This course assumes you already write SQL. If SELECT … JOIN … GROUP BY … HAVING is familiar and you have shipped features backed by a relational database, you are in the right place. If you have never written a join, start somewhere else and come back.
What it is actually about is the layer underneath the queries you already write: why the planner ignored your index, what your isolation level really guarantees, how to express in one query something you currently solve with a loop in application code, and which schema decisions you will still be happy with in three years.
The shape of it
| Section | What you come away able to do |
|---|---|
| 1 · Design | Normalise deliberately, choose keys that will not betray you, and use constraints as correctness rather than decoration. |
| 2 · Joins & NULL | Predict what a join does to row counts, and stop being ambushed by three-valued logic. |
| 3 · Windows | Ranking, running totals, moving averages, LAG/LEAD, and frames you actually understand. |
| 4 · Patterns | CTEs, LATERAL, recursion — then sessionization and gaps-and-islands, which defeat plain GROUP BY. |
| 5 · Indexes & the planner | Read EXPLAIN ANALYZE, get composite column order right, and know why your index went unused. |
| 6 · Transactions | Pick an isolation level on purpose and defend against what it still permits. |
| 7 · Modelling for change | History, soft deletes, JSONB, and migrations that do not need downtime. |
| 8 · Final exam | A timed assessment across the lot. |
You will be running queries, not reading them
Two PostgreSQL practice databases come with the course, in Section 1. They are not toys — between them they carry about 168,000 rows, and they were built specifically so that every technique here has something real to bite on:
bookshop— a normalised retail schema. Customers, books, authors, orders, line items, reviews and a stock ledger. 20,000 orders and 50,000 order lines.telemetry— the opposite shape. Append-only sensor readings and a raw event stream: 118,800 readings and 90,000 events, deliberately messy enough that sessionization and gaps-and-islands are genuinely non-trivial.
Each one is missing an index that it obviously needs. That is not an oversight — in Section 5 you run EXPLAIN ANALYZE, watch a sequential scan discard 117,390 rows, and add the index yourself. Discovering it is worth considerably more than being handed it.
PostgreSQL, and what transfers
Examples are PostgreSQL 14+. That is a deliberate choice: it has the most complete implementation of the standard features this course is about, and it is free.
Roughly what carries over:
- Fully portable — normalisation, join semantics, NULL behaviour, window functions, CTEs. These are standard SQL and work on MySQL 8+, SQL Server, Oracle and SQLite 3.25+.
- Portable in concept — indexing strategy and the planner. Every engine has an optimiser and B-tree indexes; the
EXPLAINoutput looks different but you are reading for the same things. - Engine-specific —
EXPLAINsyntax, JSONB, partial indexes, and the exact concurrency behaviour of each isolation level. Differences are flagged where they matter.
Each practice database creates its own schema (shop, tel) and drops it first. Loading them cannot collide with anything you already have, and reloading to start fresh is safe at any point. Every example assumes the matching SET search_path, shown in the download lessons.