How to learn SQL

This stage establishes that SQL describes result sets, not loops. You work on a single table with enough rows to make incorrect WHERE clauses obvious. Joins are delayed because filtering and null handling must become automatic before row multiplication enters.

• SELECT list and aliases
• WHERE with AND, OR and NOT
• NULL and three-valued logic
• ORDER BY and LIMIT
• DISTINCT and duplicate rows
• LIKE, ILIKE and pattern matching
• date and timestamp filtering
• COUNT and basic aggregates

You can now: Given a 50,000-row table, write a query that returns the correct 127 rows for a date range within ten minutes.

Joins are where most learners stall because they memorise Venn diagrams instead of row multiplication. This stage forces you to predict row counts before running each join. Aggregation follows because GROUP BY changes the grain of the result and must be understood after joins.

• INNER JOIN
• LEFT, RIGHT and FULL OUTER JOIN
• CROSS JOIN and row multiplication
• self-join
• GROUP BY and HAVING
• SUM, AVG, MIN and MAX
• COUNT(*) versus COUNT(column)
• joining on multiple keys

You can now: Take two related tables and write a query that produces the exact expected row count and grouped totals without checking the output first.

This stage replaces nested thinking with named intermediate results. It sits after joins because subqueries often hide a join that should be explicit. You learn to choose between a CTE, a subquery and a temporary table by reading the plan.

• scalar subqueries
• correlated subqueries
• EXISTS versus IN
• WITH and common table expressions
• recursive CTEs
• UNION, INTERSECT and EXCEPT
• ROW_NUMBER and RANK
• lateral joins

You can now: Rewrite a five-level nested query as three named CTEs and confirm both return the same 2,304 rows.

This is the stage most learners never reach. You read actual execution plans, not textbook descriptions. It sits here because you now write queries complex enough to be slow. You learn that the planner's choices depend on statistics, not syntax.

• EXPLAIN and EXPLAIN ANALYZE
• sequential scans versus index scans
• B-tree indexes
• composite indexes
• covering indexes
• query planner statistics and ANALYZE
• identifying bottlenecks
• rewriting OR to UNION

You can now: Take a query that runs in 1.8 seconds on 2 million rows, add one composite index, and reduce it to under 80 milliseconds while returning the same rows.

You cannot write reliable SQL against a poorly designed schema. This stage teaches you to design tables before querying them. It sits after performance work because you now know which designs cause slow joins and missing indexes.

• primary and foreign keys
• first, second and third normal form
• denormalisation trade-offs
• many-to-many junction tables
• one-to-one and one-to-many relationships
• data types and constraints
• CHECK and NOT NULL
• designing against update anomalies

You can now: Given a spreadsheet of orders and products, produce a normalised schema with junction tables and load it without duplicate or orphan rows.

This final stage combines correctness under concurrency with analytical queries. Transactions matter because real databases have simultaneous writers, not just readers. Window functions finish the set-based model by computing across rows without collapsing them.

• BEGIN, COMMIT and ROLLBACK
• isolation levels
• locking and deadlocks
• ACID properties
• window frames and PARTITION BY
• running totals and moving averages
• LAG and LEAD
• integrity constraints in practice

You can now: Write a transaction that transfers stock between two warehouses and a window query that produces a correct running total for 100,000 rows.