Learning: SQL
Become the engineer a team trusts with its database: able to express a question as a query that answers exactly it, read a query plan to find out why that query is slow, choose an index from evidence instead of instinct, reason about what concurrent transactions may observe, and design and migrate a schema that keeps bad data out and stays fast as the table grows.
Start here: 0001. Tables, Rows and Types
Latest lesson: 0053. Triggers, Stored Procedures, and Where Logic Belongs
Success looks like
- Write a query with joins, aggregation and window functions that answers the question asked, and explain why the result has exactly those rows,
NULLs included. - Read an execution plan with real timings and name which step is the problem.
- Choose an index, or reject one, from the plan and the selectivity, then prove the difference with a measurement.
- State what a transaction at a given isolation level may observe, and name the anomaly a concurrency bug depends on.
- Design a schema where bad data is impossible rather than discouraged, using keys, types and constraints.
- Change a schema on a live system without downtime and without a lock queue behind the migration.
- Take a slow query generated by an ORM, explain why it is slow, and rewrite it.
- Review someone's query or schema and name concretely what it will cost at ten times the rows.
Constraints
- Assumes no prior SQL, and no database administration experience.
- PostgreSQL is the reference engine, because its documentation is the most complete freely available description of how a real planner and a real concurrency model behave. SQLite is used where a rep should need no server at all. Where engines genuinely differ, the lesson says so and names the standard behaviour if there is one.
- Needs only a local engine and a terminal on any supported OS. Nothing in the arc requires paid tooling, a cloud account, or a managed database.
- Reps need data, and a table with ten rows teaches the wrong lesson about indexes. The arc uses one dataset that grows large enough for the planner's choices to be real.
- Planner behaviour changes between major versions. Version-sensitive claims are checked against the current documentation, and any lesson that depends on a version says which one.
Out of scope
- Database administration as a subject: backups and recovery, replication, failover, connection pooling, version upgrades.
- ORMs and query builders as subjects in their own right. Their output is read and criticised in stage 7; how to configure one is not taught.
- Analytical warehouses and their dialects: BigQuery, Snowflake, Redshift, Spark SQL. Columnar execution changes the performance advice, and the arc does not chase it.
- Non-relational stores as alternatives to compare against.
- Engine internals past the point where they stop explaining query and transaction behaviour: on-disk formats, write-ahead log internals, the planner's source code.
The arc
Seven stages, zero to senior. Not a lesson list: a stage takes several lessons, and the boundaries are soft.
| Stage | Lessons | Covers | Done when |
|---|---|---|---|
| 1. The relational model | 0001 to 0006 | Tables, rows and types, NULL and three-valued logic, SELECT, filtering, sorting, sets versus bags, what a key is | Can say why a query returned exactly those rows, including the ones NULL removed |
| 2. Querying | 0007 to 0013 | Every kind of join, aggregation and GROUP BY, HAVING, subqueries, common table expressions, set operations | Expresses a question as one query without trial and error |
| 3. Beyond the basics | 0014 to 0020 | Window functions and frames, lateral joins, recursive queries, JSON columns where they earn their place | Solves ranking and running-total problems in SQL rather than in application code |
| 4. Schema design | 0021 to 0027 | Normalisation and when to stop, primary and foreign keys, constraints, choosing types deliberately, surrogate versus natural keys | Bad data is impossible rather than discouraged |
| 5. Transactions | 0028 to 0034 | ACID as four separate promises, isolation levels and the anomalies each permits, multiversion concurrency control, locking, deadlocks, explicit row locks, idempotency | Can name the anomaly a concurrency bug depends on, before reproducing it |
| 6. Performance | 0035 to 0041 | B-tree indexes first and the others after, selectivity and cardinality, statistics, reading a plan with timings, join strategies, pagination, when the query is not the problem | Optimises from a plan and proves the win with a measurement |
| 7. Operating and judgment | 0042 to 0048 | Migrations without downtime, reviewing queries and schemas, reading ORM output, portability across engines, when SQL is the wrong tool | Trusted to make the call and to explain it to someone else; stage 8 completes it with the authorization and procedural-logic review questions this stage's own checklist assumed |
| 8. Security and specialised tools | 0049 to 0053 | SQL injection and parameterised statements, roles and least privilege, row-level security, full-text search, triggers and stored procedures | Reviews an authorization boundary and a piece of procedural logic as concretely as a query or a schema change |
Lessons
Work through these in order.
| # | Lesson | Teaches |
|---|---|---|
| 0001 | Tables, Rows and Types | A column type is a constraint you get for free, and the wrong one is hard to undo |
| 0002 | SELECT and Evaluation Order | The clauses run in a different order than they are written, which explains most beginner errors |
| 0003 | NULL and Three-Valued Logic | WHERE keeps only true, so unknown behaves as false and NOT IN can return nothing |
| 0004 | Sets and Bags | A table is a bag, so duplicates are real and UNION quietly pays to remove them |
| 0005 | Sorting and Collation | Text ordering depends on a collation, and a tie without a unique key is not stable |
| 0006 | Keys and Constraints | A key is a claim the database enforces, and every claim you leave out becomes a bug |
| 0007 | Joins, and What a Join Actually Does | A join is a filtered cross product, so the ON condition decides which rows exist before anything else runs |
| 0008 | Outer Joins and the Rows That Are Not There | An outer join keeps the rows that matched nothing, and one WHERE clause silently throws them away again |
| 0009 | Aggregation and GROUP BY | Aggregates ignore NULL and a join that multiplies rows multiplies the total |
| 0010 | HAVING, FILTER and Several Groupings at Once | Where a condition belongs decides which rows it can still see, and one pass can answer several groupings |
| 0011 | Subqueries, and Which Kind Answers the Question | A scalar, an IN, an EXISTS and a derived table answer four different questions, and one of them lies when NULL appears |
| 0012 | Common Table Expressions | WITH names a step so a long query reads top to bottom, and on this release it is not an optimisation fence |
| 0013 | Set Operations, and One Question as One Query | UNION, INTERSECT and EXCEPT combine result sets by position and remove duplicates unless told not to |
| 0014 | Window Functions | A window function computes across other rows without collapsing the one it is on |
| 0015 | Window Frames | The default frame includes every row that ties with the current one, which is why a running total can jump |
| 0016 | Navigating Within a Window | lag, lead and the value functions reach other rows directly, and two of them obey the frame |
| 0017 | Lateral Joins | LATERAL lets a subquery in FROM see the row beside it, which is how you take the top few per group |
| 0018 | Recursive Queries | A recursive CTE feeds its own output back in until nothing new comes out, which is how a hierarchy gets walked |
| 0019 | JSON Columns | Querying a document column is ordinary SQL once you know which operator returns text and which returns JSON |
| 0020 | Choosing the Tool | Top-N, running totals and deduplication each have two or three correct spellings, and the choice is about what the question asks |
| 0021 | Normalisation | Every normal form removes a way for two rows to disagree about the same fact |
| 0022 | Choosing a Key | A surrogate key buys stability and gives up meaning, and the generator you pick decides what else it costs |
| 0023 | Foreign Keys and What Happens on Delete | A foreign key is a promise about rows that exist, and the action you choose decides who pays when one goes |
| 0024 | Constraints That Actually Hold | A CHECK fails only when its condition is false, so an absent value satisfies almost every rule you wrote |
| 0025 | Choosing Types Deliberately | A type decides what the database will accept, what it will silently change, and what it can never take back |
| 0026 | Denormalising on Purpose | A second copy of a fact is a second chance to be wrong, unless the database is the one keeping it |
| 0027 | Making Bad Data Impossible | Take a schema that permits a wrong row and close every gap, then say what the design still cannot promise |
| 0028 | What a Transaction Promises | Four promises travel under one acronym, and only one of them is yours to negotiate |
| 0029 | Multiversion Concurrency Control | An update writes a new row version rather than changing one, which is why a reader never waits for a writer |
| 0030 | Isolation Levels | PostgreSQL gives you three distinct levels under four names, and each one permits a different set of surprises |
| 0031 | The Anomalies, and How to Recognise Yours | Every concurrency bug has a name, and naming it tells you which level or lock would have prevented it |
| 0032 | Locks You Take on Purpose | A row lock is how you hold a decision still, and every one of them has a failure mode worth choosing |
| 0033 | Deadlocks | Two transactions each holding what the other needs, and the only cure is agreeing on an order in advance |
| 0034 | Idempotency and the Retry That Is Correct | A retry that replays the same statements reproduces the bug it was meant to fix, so it has to read again and decide again |
| 0035 | Reading a Plan | The plan tells you what the database did and what it expected, and the gap between them is where the work is |
| 0036 | What an Index Actually Does | A B-tree turns a scan of every row into a walk down a few pages, and the plan names which of four ways it read them |
| 0037 | Selectivity and Statistics | Every plan is built from a sample of your data, so a bad plan is usually a bad estimate rather than a bad planner |
| 0038 | Choosing an Index | Column order decides which queries an index can answer, and the best index is often the one you decide not to build |
| 0039 | Join Strategies | Three ways to join and the planner picks by estimated cost, so a wrong estimate shows up as the wrong strategy |
| 0040 | Pagination and Counting | OFFSET reads and discards every row it skips, and counting exactly costs a pass over the table |
| 0041 | When the Query Is Not the Problem | Proving a win needs a measurement you can repeat, and the cause is often the table, the traffic or the client |
| 0042 | Migrations Without Downtime | A migration that takes a millisecond can still stop every read on the table, because of what queues behind it |
| 0043 | Changing What Already Has Data | A constraint on a full table is two statements, and a backfill is a loop rather than one UPDATE |
| 0044 | Reviewing a Query | Read the query for the rows it returns before reading it for speed, because a fast wrong answer is worse |
| 0045 | Reviewing a Schema Change | Ask what the migration locks, what it rewrites, and what runs while both versions of the code are live |
| 0046 | Portability, and What It Costs | Most of this arc travels between engines, and the parts that do not are the parts worth depending on deliberately |
| 0047 | Reading What an ORM Emits | The ORM writes the SQL you did not write, and the only way to know what it sent is to look |
| 0048 | Trusted With the Database | Seven stages end in one habit, which is naming the row, the lock or the plan that makes a call defensible |
| 0049 | SQL Injection and Parameterised Statements | String concatenation lets a value change what a statement means, not only what it matches, and a parameterised statement removes the possibility rather than trying to sanitise every case |
| 0050 | Roles and Least Privilege | Roles subsume the old ideas of users and groups, ownership already grants every privilege with no GRANT needed, and least privilege is entirely about what everyone else is allowed to do |
| 0051 | Row-Level Security | GRANT decides access to a table as a whole, a row security policy decides it per row, and the engine enforces it on every access path, not only the one a developer remembered to filter |
| 0052 | Full-Text Search | tsvector reduces a document to normalized lexemes a GIN index can search, tsquery asks whether they are present, and the boundary with a dedicated search engine is where ranking and scale outgrow one column |
| 0053 | Triggers, Stored Procedures, and Where Logic Belongs | This is stage 8's capstone, a trigger runs on every path that reaches a table and a procedure can commit or roll back its own transaction, and the judgment call is deciding when that invisibility is actually worth its cost |
Reference
- Glossary: canonical terms for this topic
- Resources: trusted sources, each annotated with what it covers
- The dataset: the schema and data every lesson from stage 2 onward queries, and how to load it
- NULL and three-valued logic: truth tables, where NULL counts as equal, and the traps with their fixes
- Evaluation order of a SELECT: what runs when, what each clause can see, and which errors that explains
- Querying: the stage 2 sheet, with every join type, what each aggregate does to NULL, and where a condition belongs
- Beyond the basics: the stage 3 sheet, with the window functions, the frame modes, and which tool answers which question
- Schema design: the stage 4 sheet, with the normal forms, every constraint kind, and which type refuses what
- Transactions: the stage 5 sheet, with each isolation level, the anomalies it permits, the lock strengths, and the retryable errors
- Performance: the stage 6 sheet, with the plan nodes, which numbers to trust, the scan and join strategies, and the index decisions
- Operating: the stage 7 sheet, with which migrations are safe, the review questions, and what travels between engines
How this works
Each lesson is short and self-contained. Answer keys are collapsed: recall first, then open them. The real-world reps matter more than the reading, and spacing them out is the point. Anything still unclear at the end of a lesson is worth chasing to its primary source before moving on.