SQL Fundamentals: Joins, Query Execution Order, Normal Forms & Schema Design

Every join type on one tiny dataset, NULL traps, the logical order a SELECT is evaluated in, how Postgres actually executes it (nested loop / hash / merge), window functions, CTEs, 1NF→5NF on one evolving order sheet, deliberate denormalization, an interview-grade e-commerce schema and the classic SQL round problems.

About 120 minutes · 17 exam questions · 11 interview questions · 2 code labs

What you will learn

  1. Joins by example: INNER, LEFT, RIGHT, FULL, CROSS and SELF on one tiny dataset
  2. Semi-joins and anti-joins: EXISTS, IN, NOT EXISTS and the NOT IN null trap
  3. NULL in joins and the LEFT JOIN + WHERE trap
  4. Logical query processing order: why an alias fails in WHERE
  5. Physical execution: parser, rewriter, planner, executor and the three join algorithms
  6. Aggregation and window functions: GROUP BY vs OVER, ranking, running totals, top-N per group
  7. Subqueries vs CTEs vs joins: same answer, different plans
  8. Normalization 1NF → 3NF on one evolving order sheet, with the anomaly each step removes
  9. BCNF, 4NF and 5NF: overlapping keys, independent multi-valued facts and join dependencies
  10. When to denormalize: read models, reporting and what it costs
  11. Schema-design walkthrough: an e-commerce orders schema end to end
  12. Interview SQL classics: Nth highest, duplicates, gaps and islands

Next: SQL vs NoSQL, Data Models, ACID & Isolation