Most SQL tutorials teach the same thing: SELECT, WHERE, JOIN, GROUP BY. Maybe window functions. Then they stop.
The real questions come after that. Why is this query slow? What does the execution plan mean? How do transactions actually work? What is MVCC?
I found a curriculum that answers those. It's on KandZ Tuts, and it's 131 chapters, organized as a single progression from relational theory to distributed database internals.
Here's the breakdown.
Foundations (1–13)
What relational databases are, how an RDBMS enforces the model, tables and keys, the DDL/DML/DCL/TCL/DQL categories, installing a database, data types, CREATE TABLE, ALTER TABLE, DROP/TRUNCATE, and the full constraint toolkit (primary keys, foreign keys, NOT NULL, UNIQUE, DEFAULT, CHECK).
Querying (14–28)
SELECT and FROM, WHERE, comparison and logical operators, BETWEEN, IN, LIKE/ILIKE, NULL handling, ORDER BY, LIMIT/OFFSET/TOP, DISTINCT, INSERT, UPDATE, DELETE, and aliases.
Aggregation and Joins (29–42)
Aggregate functions, GROUP BY, HAVING, the order of execution (most tutorials skip this), relational join concepts, INNER/LEFT/RIGHT/FULL OUTER/CROSS/SELF joins, UNION/UNION ALL, INTERSECT, and EXCEPT/MINUS.
Subqueries and Functions (43–53)
Subqueries in WHERE, SELECT, and FROM, correlated subqueries, EXISTS, ANY/SOME/ALL, CASE WHEN, COALESCE/NULLIF, string manipulation, date/time arithmetic, numeric functions, and CAST/CONVERT.
Window Functions and CTEs (54–65)
The OVER clause, PARTITION BY, ranking functions, NTILE, LAG/LEAD, FIRST_VALUE/LAST_VALUE/NTH_VALUE, aggregate functions over window frames, ROWS vs RANGE frames, ROLLUP/CUBE/GROUPING SETS, CTEs with WITH, chained CTEs, and recursive CTEs for hierarchical data.
Transactions, Indexes, and Optimization (66–81)
Views and materialized views, ACID properties, BEGIN/COMMIT/ROLLBACK, savepoints, isolation levels and concurrency anomalies, index concepts, B-tree/hash/bitmap indexes, composite and partial indexes, index maintenance, execution plans, table scans vs index scans, normalization through 5NF, denormalization, and ER modeling.
Procedural SQL and Security (82–93)
User-defined functions, stored procedures, parameters, conditional flow and loops, error handling, triggers, cursors, sequences, GRANT/REVOKE, roles and permissions, SQL injection prevention, row-level security, and data masking.
Advanced Features (94–100)
JSON in relational databases, full-text search, table partitioning, locks and deadlocks, upsert patterns (MERGE, ON CONFLICT, ON DUPLICATE KEY), sharding and read replicas, and backup/restore strategies.
Database Internals (101–113)
Storage engine architecture, write-ahead logging, buffer pool management, MVCC internals, lock escalation and two-phase locking, cost-based optimizer statistics, join algorithms (nested loop, hash join, merge join), query execution engines (Volcano iterator vs vectorized execution), specialized indexes (GiST, GIN, BRIN, R-trees), spatial data with PostGIS, temporal tables, graph queries, and columnar vs row storage.
Analytics and Distributed Systems (114–125)
Data warehousing (star and snowflake schemas), Data Vault 2.0, slowly changing dimensions, MPP architecture, distributed SQL engines (CockroachDB, YugabyteDB, Spanner), two-phase commit, consensus protocols (Raft and Paxos), XML and XPath, vector search with pgvector and HNSW indexes, change data capture with Debezium, foreign data wrappers, dynamic SQL hazards, and database auditing.
Production Concerns (126–131)
Compliance logging, cross-database migration, schema evolution, database unit testing (pgTAP, tSQLt), benchmarking (sysbench, TPC-C, TPC-H), database driver mechanics and wire protocols, and a cheatsheet.
Why the Internals Section Matters
Chapters 101–113 are the reason to read this curriculum.
Most resources stop at "here's how to write a query." This one goes into how the database executes it—the cost-based optimizer, the join algorithms, the buffer pool, the MVCC system.
That knowledge is what lets you diagnose a slow query instead of guessing at indexes. It's the difference between using a database and understanding one.
Who This Is For
- Beginners: Start at Chapter 1 and read in order.
- Developers who write SQL daily: Jump to Chapter 32 (order of execution) and Chapters 54–65 (window functions and CTEs).
- DBAs and data engineers: Start at Chapter 72 (indexes) and read through Chapter 113.
The Link
Free, no signup, 131 chapters.
If you've been writing SQL for years but never understood the optimizer, or you're starting from scratch and want a path that doesn't stop at JOIN, this is the most complete free curriculum I've found.
What SQL topics do you wish had better free resources? Let me know in the comments.
