Intermediate

Relational Databases in Depth

SQL, then what the engine does with it.

SQL that is correct under NULLs and joins, then the engine: indexes and how the planner uses them, transactions and isolation levels, locking, schema design, and replication — on PostgreSQL and MySQL, with the differences named.

15 lessons written4 modules~3h reading

After this course you can

Write joins, aggregations, subqueries and window functions that return the right rows
Read an EXPLAIN plan and add the index it is asking for
Explain the four isolation levels and the anomalies each one allows
Design a schema that survives growth: keys, constraints, normalisation, and when to denormalise

Curriculum

15 lessons · outlined lessons show their plan

SQL

5/5 written · ~56 min

SELECT, WHERE and ORDER BYThe clause order the database actually uses, NULL as unknown, and the two honest averages that disagree.11 min JoinsInner, left, right, full, cross and self joins; the WHERE clause that turns a LEFT JOIN into an inner one; and duplicate rows from a one-to-many.12 min Aggregation and groupingGROUP BY, HAVING, the sum a join inflated, COUNT(*) versus COUNT(col), and grouping sets.10 min Subqueries and CTEsCorrelated subqueries, EXISTS, NOT IN with a NULL, common table expressions, and recursive CTEs for trees.12 min Window functionsROW_NUMBER, RANK, LAG and LEAD, running totals, and the top-N-per-group query that replaces a correlated subquery.11 min

The engine

5/5 written · ~73 min

IndexesB-trees, composite indexes and column order, covering indexes, selectivity, and the query that cannot use the index you built.15 min Query optimisationEXPLAIN and EXPLAIN ANALYZE, seq scan versus index scan, join strategies, statistics, and the ten-line query that took eleven million rows.14 min Transactions and isolationACID, the four isolation levels, dirty and non-repeatable reads, phantoms, write skew, and what PostgreSQL and MySQL actually implement.15 min Locking and deadlocksRow locks, gap locks, SELECT FOR UPDATE, lock ordering, optimistic locking with a version column, and reading a deadlock graph.15 min Connection poolingHikariCP sizing, why more connections make it slower, timeouts, leak detection, and the pool exhausted by a slow query.14 min

JDBC

3/3 written · ~32 min

JDBC: the four objectsDriver, Connection, Statement and the ResultSet that is a cursor — plus the getInt that turns NULL into zero.11 min Prepared statementsValues sent separately from the SQL: parameter binding, the plan cache, generated keys, and the return value people ignore.11 min Batch operationsRound trips are the cost. addBatch, chunk size, what a batch failure tells you, and the driver settings that are off by default.10 min

Design and operations

2/2 written · ~31 min

Schema designKeys, constraints, normal forms as a checklist, JSON columns, soft deletes, audit columns, and the denormalisation you do on purpose.14 min Replication and scalingPrimary-replica replication, replication lag and the read-your-writes problem, read replicas, partitioning, and sharding as the last resort.17 min