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