Course 08 · Data
SQL
Data that outlives the page.
The language every database answers to. One door factory, twenty-two lessons, and a real SQLite engine running your queries in the page.
0 of 22 lessons finished — nothing yet
The syllabus
22 lessons,
in the order they build on each other.
What a database is for
Tables, rows and columns — and what a database gives you that a JSON file cannot.
SELECT
Asking for columns, renaming them, doing arithmetic, and reading the result as a table.
WHERE
Comparisons, AND and OR and the brackets between them, BETWEEN, IN and LIKE.
NULL
The value that is not a value: three-valued logic, IS NULL, and the bug it causes in every codebase.
ORDER BY and LIMIT
Sorting on columns and expressions, ties, NULL placement, paging and why the order is otherwise undefined.
Counting and summing
COUNT, SUM, AVG, MIN, MAX, DISTINCT — and the difference between COUNT(*) and COUNT(column).
GROUP BY and HAVING
One row per group, the bare-column rule, filtering before and after grouping.
Joining tables
Foreign keys in practice: INNER JOIN, the ON clause, aliases, and joining three tables.
LEFT JOIN and the rows that are missing
Keeping rows that have no match, finding the ones that have none, and the WHERE that silently undoes it.
Self joins and cross joins
A table joined to itself, every combination on purpose, and the accidental Cartesian product.
Subqueries
A query inside a query: scalar, IN, EXISTS, correlated — and which of them the planner likes.
Common table expressions
WITH: naming the steps so a long query reads top to bottom, and recursion for trees.
Window functions
Keeping every row and still seeing the whole: OVER, PARTITION BY, ranks, running totals, LAG.
Writing data
INSERT, UPDATE, DELETE, RETURNING, upserts — and the WHERE you must not forget.
Designing a table
CREATE TABLE, types, NOT NULL, DEFAULT and CHECK: making bad data impossible to store.
Keys and relationships
Primary keys, foreign keys, one-to-many, many-to-many, and what ON DELETE decides.
Normalisation, and when to stop
One fact in one place, the anomalies it prevents, and the copies worth keeping anyway.
Indexes
What a B-tree can do, what an index costs on write, and reading the query plan.
Transactions
All or nothing: BEGIN, COMMIT, ROLLBACK, what ACID promises and what isolation does not.
Text, dates and JSON
String functions, storing time so it sorts, and the JSON column that saves you from a table.
SQL from an application
Parameters, injection, pools, migrations and the N+1 query that quietly ruins a page.
Practice: the whole floor
Eight questions a business actually asks, answered with everything the course has taught.
Keep going
The other courses.
HTML
The document, the elements and the meaning behind them. Everything else on the web is layered on top of this.
CSS
Selectors, the box model, layout with flexbox and grid, and motion that has a reason to move.
JavaScript
Values, functions, the DOM, events and asynchronous work — written so you can debug it later.