Databases & SQL

1. Tables, rows and keys

A relational database is a collection of tables, and the whole design rests on one idea: each table stores exactly one kind of entity, each row is one instance of it, each column is one fact — and keys are what stop the rows from lying about each other. This lesson builds the relational model from scratch: domains and types, keys in all their forms, foreign keys and referential integrity, constraints as enforced truth, and how to model a small real system (a library) so that illegal states are unrepresentable. The schema is where data correctness lives; everything else is downstream.

2. SELECT, WHERE, ORDER BY

Almost every question you will ever ask a database comes down to three clauses: which columns (SELECT), which rows (WHERE), in what order (ORDER BY) — plus the filters' fine print, three-valued logic with NULL, and the operators that make predicates correct. This lesson works through the read side of SQL precisely: the logical order in which a SELECT is actually evaluated, the filter vocabulary, sorting and its collation surprises, paging, functions in predicates and when they break indexes, and the discipline of selecting only what you need. Query text is a description of a result; the engine decides how.

3. Joins: asking across tables

Data is split into tables so each fact is stored once — and every interesting question spans tables, which is what joins are for. This lesson covers the join types with their exact row-by-row semantics (INNER, LEFT, RIGHT, FULL, CROSS), outer joins and the "find what's missing" pattern, self joins and anti-joins, join correctness traps (fan-out, null keys, non-equi joins), and how to read a join in a plan. Normalisation stores the story in pieces; joins tell it whole again.

1. Insert, update and delete safely

Reads may be frequent, but writes are where mistakes become permanent: a forgotten WHERE rewrites a table, a missing transaction splits one fact into two, a silent default invents data. This lesson covers DML precisely — inserting with defaults and conflict handling, updating with returning verification, deleting with intention, batch operations, the SQL injection boundary in parameterised statements, and the discipline of writing every write to touch exactly the rows you mean and to prove what changed. Safe writes are the difference between a database you trust and one you are afraid of.

2. Indexes and EXPLAIN

An index is a sorted side-structure the database maintains so it does not read a whole table to answer one question — and it is the single most effective tool for making a slow query fast. This lesson covers how B-tree indexes actually work, what they cost in writes and storage, the many kinds of index and when each applies, the predicate forms that can and cannot use one, composite index column order, and EXPLAIN ANALYZE as the instrument that turns tuning from folklore into measurement. Add indexes from queries, prove them with plans, and never index 'just in case'.

3. Transactions and constraints

Money, seats, inventory and grades are all *shared mutable state*, and two people acting at once will eventually collide. This lesson covers the ACID guarantees that make multi-statement writes safe, isolation levels and the anomalies each one permits, locking (`for update`, deadlock avoidance), constraints as the final backstop, and the application patterns — optimistic versions, idempotency, retry on serialization failure — that turn concurrency from a source of corruption into a solved problem. Correctness under concurrency is not an advanced topic; it is Tuesday.