🗄️ Learn SQL — From Zero to Pro
A comprehensive, edge-case-covering, idiomatic SQL curriculum. Each document is self-contained and covers its concept deeply enough that a careful reader can go from beginner to pro SQL developer.
How to Use This Course
- Read sequentially for a structured path (01 → 30).
- Jump to a chapter as a reference when you hit a concept in the wild.
- Run the exercises in chapter 30 against a real database.
- Read your database's docs (PostgreSQL, MySQL, SQLite) alongside.
Prerequisites
- A SQL database (PostgreSQL recommended; SQLite works for most chapters).
- A client tool (
psql, DBeaver, orsqlite3CLI). - Comfort with basic programming concepts.
Curriculum
Part I — Foundations
| # | Topic | Why It Matters |
|---|---|---|
| 01 | Introduction & Setup | Relational model, PostgreSQL/SQLite install, psql/sqlite3. |
| 02 | SELECT Basics & Filtering | Projection, WHERE, comparison & logical operators. |
| 03 | Sorting, Pagination & LIMIT | ORDER BY, LIMIT/OFFSET, keyset pagination. |
| 04 | Joins | INNER/LEFT/RIGHT/FULL/CROSS, join mechanics. |
| 05 | Aggregation & GROUP BY | COUNT/SUM/AVG/MIN/MAX, HAVING, grouping quirks. |
Part II — Query Composition
| # | Topic | Why It Matters |
|---|---|---|
| 06 | Subqueries | Scalar, correlated, EXISTS/IN, semi/anti-joins. |
| 07 | Common Table Expressions | WITH, readability, chaining, materialization hints. |
| 08 | Set Operations | UNION/UNION ALL/INTERSECT/EXCEPT, column matching. |
| 09 | Window Functions | OVER, PARTITION BY, frames, ranking, running totals. |
| 10 | Data Types & NULL Handling | Three-valued logic, COALESCE, NULLIF, type coercion. |
Part III — Schema & Data Definition
| # | Topic | Why It Matters |
|---|---|---|
| 11 | Tables, Schemas & DDL | CREATE/ALTER/DROP, schemas, temporary tables. |
| 12 | Constraints & Keys | PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL. |
| 13 | Indexes & Performance | B-tree, partial, expression, composite, when indexes hurt. |
| 14 | INSERT, UPDATE, DELETE | DML, RETURNING, upsert, cascading deletes. |
| 15 | Transactions & Isolation | ACID, BEGIN/COMMIT/ROLLBACK, isolation levels, deadlocks. |
Part IV — Advanced Querying
| # | Topic | Why It Matters |
|---|---|---|
| 16 | Views & Materialized Views | Virtual tables, refresh strategies, updatable views. |
| 17 | Date & Time Handling | DATE/TIMESTAMP/INTERVAL, time zones, DST traps. |
| 18 | JSON & Array Columns | JSONB, indexing JSON, array operators, unnesting. |
| 19 | Recursive Queries | Recursive CTEs, tree traversal, graph patterns. |
| 20 | Full-Text Search | tsvector, ranking, tsquery, trigrams, LIKE vs FTS. |
Part V — Programming in the Database
| # | Topic | Why It Matters |
|---|---|---|
| 21 | Triggers & Events | BEFORE/AFTER, statement vs row, audit tables. |
| 22 | Stored Procedures & Functions | FUNCTION vs PROCEDURE, PL/pgSQL, volatility. |
| 23 | Sequences & Identifiers | SERIAL/IDENTITY/SEQUENCE, gaps, currval/nextval. |
| 24 | Security, Roles & Permissions | GRANT/REVOKE, roles, RLS, least privilege. |
Part VI — Production Engineering
| # | Topic | Why It Matters |
|---|---|---|
| 25 | Normalization & Data Modeling | 1NF–BCNF, denormalization tradeoffs, surrogate keys. |
| 26 | Query Optimization & EXPLAIN | EXPLAIN ANALYZE, seq vs index scans, join strategies. |
| 27 | Advanced SQL Patterns | Pivots, gaps-and-islands, running medians, histograms. |
| 28 | Database Administration | Backups, VACUUM, replication, connection pooling. |
| 29 | Common Pitfalls & Idiomatic Fixes | 40+ traps and their fixes. |
| 30 | Exercises & Project Ideas | From beginner to pro. |
Learning Path Suggestions
If you're new to databases
- Read 01–10 in order.
- Build a small schema (11–14) and insert real data.
- Read 15 (Transactions) before writing any app code.
- Do exercises 1–5 in chapter 30.
If you're coming from a NoSQL background
Read 04 (Joins) and 05 (Aggregation) carefully — they're the core differentiator. Read 25 (Normalization) to understand why schemas exist. Skim 10 (NULL) — three-valued logic is a common surprise.
If you're coming from a programming language
Read 06–09 (subqueries, CTEs, set ops, windows) — these are the "control flow" of SQL. Don't skip 10 (NULL) — NULL = NULL is UNKNOWN, not TRUE. Read 26 (EXPLAIN) early — query plans matter more than syntax.
If you're a senior engineer
Skim 01–14. Read 09 (Windows), 15 (Isolation), 18 (JSON), 19 (Recursive), 26 (EXPLAIN) closely. Use 27 (Patterns) and 29 (Pitfalls) as references. Read 28 (Admin) for production readiness.
Companion Resources
- PostgreSQL Docs — the reference implementation used in most examples.
- SQLite Docs — embedded SQL, great for learning.
- Use The Index, Luke — indexing explained deeply.
- SQL Performance Explained — index mechanics.
- pgexercises.com — interactive practice.
- Mode SQL Tutorial — guided examples.
Tooling to Install
bash
# PostgreSQL (macOS)
brew install postgresql@16
brew services start postgresql@16
psql postgres
# SQLite
brew install sqlite
sqlite3 practice.db
# Or use Docker
docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:16
psql -h localhost -U postgres
License
These notes are yours to use, share, and modify.
🗄️