Advanced SQL Course

Welcome to the Advanced SQL Course! This course is designed to help you master advanced SQL concepts through practical exercises and real-world examples. The course is structured over 15 days, with daily lessons and assignments to reinforce the material.

Download MySQL for Win here

Course Outline

Day 1: Introduction to Advanced SQL

• Topics:
  • Recap of basic SQL concepts
  • Introduction to subqueries and nested queries.
• Assignment: Day 1 Assignment
• Solutions: Day 1 Solutions

Day 2: Complex Joins (INNER, LEFT, RIGHT, FULL)

• Topics: Detailed explanation and usage of complex joins.
• Assignment: Day 2 Assignment
• Solutions: Day 2 Solutions

Day 3: Subqueries and Nested Queries

• Topics: Using subqueries in SELECT, INSERT, UPDATE, DELETE.
• Assignment: Day 3 Assignment
• Solutions: Day 3 Solutions

Day 4: Window Functions

• Topics: Introduction to window functions, ROW_NUMBER(), RANK(), DENSE_RANK(), and others.
• Assignment: Day 4 Assignment
• Solutions: Day 4 Solutions

Day 5: CTE (Common Table Expressions)

• Topics: How to use CTEs for better readability and reusability of queries.
• Assignment: Day 5 Assignment
• Solutions: Day 5 Solutions

Day 6: Advanced Grouping (HAVING, GROUP BY, GROUPING SETS)

• Topics: Using HAVING and GROUP BY effectively, introduction to GROUPING SETS.
• Assignment: Day 6 Assignment
• Solutions: Day 6 Solutions

Day 7: SQL Optimization Techniques

• Topics: How to optimize SQL queries, Indexes, Query Execution Plans.
• Assignment: Day 7 Assignment
• Solutions: Day 7 Solutions

Day 8: Advanced Indexing

• Topics: Understanding composite indexes, covering indexes, and indexing strategies.
• Assignment: Day 8 Assignment
• Solutions: Day 8 Solutions

Day 9: Transactions and Isolation Levels

• Topics: ACID properties, isolation levels, and handling transactions in SQL.
• Assignment: Day 9 Assignment
• Solutions: Day 9 Solutions

Day 10: Advanced Data Manipulation (MERGE, UPSERT)

• Topics: Using MERGE for conditional inserts and updates, working with UPSERT in SQL.
• Assignment: Day 10 Assignment
• Solutions: Day 10 Solutions

Day 11: Recursive Queries

• Topics: Using recursive CTEs to solve hierarchical problems.
• Assignment: Day 11 Assignment
• Solutions: Day 11 Solutions

Day 12: Pivoting Data

• Topics: Pivoting rows to columns and vice versa, unpivoting data.
• Assignment: Day 12 Assignment
• Solutions: Day 12 Solutions

Day 13: Data Integrity and Constraints

• Topics: Defining constraints for data integrity, unique constraints, and foreign keys.
• Assignment: Day 13 Assignment
• Solutions: Day 13 Solutions

Day 14: SQL for Big Data

• Topics: Working with large datasets, partitioning, sharding, and distributed SQL.
• Assignment: Day 14 Assignment
• Solutions: Day 14 Solutions

Day 15: Advanced SQL in Real-World Projects

• Topics: Integrating SQL with real-world applications, working with SQL in cloud databases.
• Assignment: Day 15 Assignment
• Solutions: Day 15 Solutions

How to Use This Repository

1. Go through each day's lessons and complete the assignment provided.
2. Check the solution link for hints or guidance if needed.
3. Feel free to fork this repository and make your own changes or improvements to the exercises.
4. Share your feedback or improvements as pull requests.

Prerequisites

• Basic understanding of SQL.
• Familiarity with databases (e.g., MySQL, PostgreSQL, SQLite).

License

This course and its materials are created and shared by Ekaterina Podkorytova. Feel free