Advanced SQL

This course is designed to push your SQL skills to the advanced level, focusing on advanced techniques and optimizations which are crucial for backend developers. You will master the craft of write efficient, complex queries, optimize database performance, and integrate SQL with the backend frameworks in which you already know how to work.

Lesson 1: Complex Queries and Joins

• Topics:
  • Advanced JOINs (Self-Joins, Cross Joins, Natural Joins)
  • Subqueries (Correlated vs Non-Correlated)
  • Common Table Expressions (CTEs)
  • Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
• Hands-On:
  • Write complex queries using multiple JOINs and subqueries.
  • Implement CTEs for recursive queries.
  • Use window functions for advanced data analysis.

Lesson 2: Advanced Data Manipulation

• Topics:
  • Upserts (INSERT … ON DUPLICATE KEY UPDATE)
  • Transactions and ACID Properties
  • Savepoints and Rollbacks
  • Bulk Inserts and Updates
• Hands-On:
  • Perform upserts in a sample database.
  • Implement transactions with savepoints and rollbacks.
  • Optimize bulk data operations.

Lesson 3: Indexing and Query Optimization

• Topics:
  • Index Types (B-Tree, Hash, Full-Text)
  • Query Execution Plans (EXPLAIN, EXPLAIN ANALYZE)
  • Indexing Strategies and Best Practices
  • Query Optimization Techniques
• Hands-On:
  • Analyze query execution plans.
  • Create and optimize indexes for a sample database.
  • Rewrite queries for better performance.

Lesson 4: Stored Procedures and Functions

• Topics:
  • Creating and Using Stored Procedures
  • User-Defined Functions (UDFs)
  • Triggers and Event Schedulers
  • Error Handling in Stored Procedures
• Hands-On:
  • Write stored procedures and functions.
  • Implement triggers for automated tasks.
  • Handle errors within stored procedures.

Lesson 5: Advanced Data Types and JSON

• Topics:
  • Working with JSON Data (JSON_EXTRACT, JSON_ARRAY, JSON_OBJECT)
  • Geospatial Data and Queries
  • Full-Text Search
  • Custom Data Types and Domains
• Hands-On:
  • Query and manipulate JSON data.
  • Perform geospatial queries.
  • Implement full-text search in a database.

Lesson 6: SQL Integration with Backend Frameworks

• Topics:
  • ORM vs Raw SQL: When to Use Which
  • SQL in Node.js (Using Sequelize, Knex.js)
  • SQL in Python (Using SQLAlchemy, Django ORM)
  • SQL in PHP (Using Eloquent, Doctrine)
  • SQL in Ruby (Using Active Record)
  • SQL in Java (Using Hibernate, JPA)
• Hands-On:
  • Integrate raw SQL queries with your preferred backend framework.
  • Compare performance between ORM and raw SQL.

Lesson 7: Database Design and Normalization

• Topics:
  • Advanced Database Design Principles
  • Normalization (1NF, 2NF, 3NF, BCNF)
  • Denormalization and When to Use It
  • Schema Migration Strategies
• Hands-On:
  • Design a normalized database schema.
  • Perform schema migrations in a sample project.

Lesson 8: Advanced Security and Permissions

• Topics:
  • SQL Injection and Prevention
  • Role-Based Access Control (RBAC)
  • Encryption and Hashing in SQL
  • Auditing and Logging
• Hands-On:
  • Implement SQL injection prevention techniques.
  • Set up RBAC in a database.
  • Encrypt sensitive data in a database.

Lesson 9: Scaling and Performance Tuning

• Topics:
  • Database Sharding and Partitioning
  • Replication and High Availability
  • Connection Pooling
  • Caching Strategies (Redis, Memcached)
• Hands-On:
  • Set up database replication.
  • Implement connection pooling in a backend application.
  • Integrate caching with SQL queries.

Lesson 10: Final Project and Review

• Project:
  • Build a backend application that integrates advanced SQL techniques.
  • Optimize the database for performance and security.
  • Present your project to the class.
• Review:
  • Recap of key concepts and techniques.
  • Q&A session and feedback.

Assessment:

• Quizzes: After each topic to reinforce learning.
• Assignments: Hands-on tasks to apply concepts.
• Final Project: A comprehensive backend application that demonstrates mastery of advanced SQL.

Learning Outcomes:

With the end of this module, students will be able to:

• Write complex, optimized SQL queries.
• Design and manage efficient database schemas.
• Integrate advanced SQL techniques with backend frameworks.
• Implement security and performance best practices in SQL.

Resources:

• Books:
  • “SQL Performance Explained” by Markus Winand
  • “SQL Antipatterns” by Bill Karwin
• Online Resources:
  • SQLZoo, LeetCode SQL Problems, Mode Analytics SQL Tutorials
• Tools:
  • MySQL Workbench, pgAdmin, DBeaver, SQL Fiddle

This course will provide you with the advanced SQL skills necessary to excel in backend development, making you a more versatile and effective as a developer.