Course Outline

🔹 Day 1: SQL Essentials & Complex Queries

Session 1: SQL Foundations

• Database concepts: tables, rows, columns, keys
• SELECT statements: filtering, sorting, aliases
• WHERE, ORDER BY, and LIMIT clauses

Session 2: Aggregations and Grouping

• COUNT, SUM, AVG, MIN, MAX
• GROUP BY and HAVING clauses
• NULL handling in aggregations

Session 3: Subqueries and Expressions

• Scalar, correlated, and nested subqueries
• CASE statements and conditional logic
• String and date manipulation

Lab Activities:

• Write queries to extract specific data from real-world tables
• Group and summarize data using aggregates
• Use subqueries to answer complex business questions

🔹 Day 2: Joins & Relational Thinking

Session 1: Understanding Joins

• INNER, LEFT, RIGHT, FULL OUTER joins
• Cross joins and self joins
• Join conditions and aliases

Session 2: Multi-Table Queries

• Joining 3 or more tables
• Handling NULLs and unmatched records
• Using joins with aggregations

Session 3: Set Operations and CTEs

• UNION, INTERSECT, and EXCEPT
• Common Table Expressions (CTEs)
• Recursive queries (intro)

Lab Activities:

• Write complex join queries across multiple tables
• Solve business problems using LEFT and FULL joins
• Use CTEs for better query readability and logic

🔹 Day 3: Query Optimization & Performance Tuning

Session 1: Indexes and Execution Plans

• How indexes work
• Reading and interpreting query plans
• Choosing the right index for the query

Session 2: Optimizing Queries

• Identifying and fixing slow queries
• Avoiding common performance pitfalls (e.g., SELECT *)
• Using EXPLAIN/ANALYZE to troubleshoot

Session 3: Advanced Techniques and Best Practices

• Window functions (ROW_NUMBER, RANK, LEAD/LAG)
• Materialized views and temporary tables
• Writing clean, maintainable SQL code

Lab Activities:

• Analyze and optimize slow queries using indexes
• Use window functions to calculate running totals and rankings
• Refactor complex queries for performance and clarity