The course
One application, nine levels. Inkwell is a small blogging app: people sign up, write posts, comment, like, tag and follow each other. Each level is the next thing that happens to the app (a feature, a bug, a slowdown), and the SQL concept you learn is the one that situation needs. Every level builds on the schema you wrote in the one before.
This is not a tour of everything in SQL. It covers what you reach for in day-to-day application work.
| Level | What happens to Inkwell | What you learn | Where |
|---|---|---|---|
| 01 | Day one: store users and posts | Tables, types, primary keys, migrations | SQL workbench |
| 02 | Comments ship; a deleted user leaves orphaned posts | Foreign keys, one-to-many, ON DELETE | SQL workbench |
| 03 | Likes, tags and follows | Many-to-many, junction tables, composite keys, self-references | SQL workbench |
| 04 | First real users, first bad data | INSERT, NOT NULL, UNIQUE, DEFAULT, CHECK | Postgres |
| 05 | The homepage needs a feed | SELECT, WHERE, ORDER BY, LIMIT, JOIN | Postgres |
| 06 | Profiles need counts; admin wants to find inactive users | GROUP BY, HAVING, LEFT JOIN | Postgres |
| 07 | 300,000 posts later, the feed crawls | EXPLAIN ANALYZE, indexes | Postgres |
| 08 | Edits, soft deletes, and a spam account | UPDATE, DELETE, RETURNING, transactions | Postgres |
| 09 | Build notifications end to end, on your own | Everything above | Postgres |