SQL
Almost every backend, data and AI job asks SQL. All examples use one small online-store dataset, so you learn the tables once and focus on the queries.
Goes from your first SELECT to window functions, indexes and transactions in PostgreSQL. Ends with when to use NoSQL (MongoDB) instead.
How to use this roadmap
Section titled “How to use this roadmap”- Topics are in teaching order. “Builds on” links show what to know first, even from other tracks.
- New? Start at the top. Know some already? Jump to a level, or skip any topic you can already explain.
- Short on time? Tick “Asked often only” to see what interviews ask most.
- Your progress is saved in this browser.
Where it fits: Start any time. SQL needs no Python, so many people do it alongside Python foundations.
33 topics in 4 levels, about 11 h of reading. With practice, plan 2 to 3 times that. 15 of 33 topics have lessons so far.
46 practice problems from this track’s lessons, in one list.
Foundations
Know this level already? Check what you know: 15 questions, about 8 minutes. Topics you get right are marked Known.
- 1. Tables, rows and keysNot started
How data sits in tables, and how keys link one table to another.
Watch it run: Rows checked against keys
Start15 min read, about 1 h with practice
- 2. SELECT, WHERE, ORDER BY and LIMITAsked oftenNot started
Pick columns, filter rows, sort them and take the top few.
Watch it run: The warehouse list
Start20 min read, about 1 h with practiceBuilds on: Tables, rows and keys
- 3. DISTINCTNot started
Remove duplicate rows from a result.
Watch it run: The second-highest price
Start10 min read, about 45 min with practiceBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 4. AggregatesNot started
Count, sum and average across many rows.
Watch it run: The sales tile
Start15 min read, about 50 min with practiceBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 5. GROUP BY and HAVINGAsked oftenNot started
Totals per group, like revenue per month, and filtering those groups.
Watch it run: Your own query · The loyalty coupon · The payments report
Start20 min read, about 50 min with practiceBuilds on: Aggregates
- 6. NULL and three-valued logicAsked oftenNot started
Why NULL means "unknown", and the surprises it causes in filters and counts.
Watch it run: The reminder list
Start15 min read, about 50 min with practiceBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 7. Joins: inner, left, right, full, self and crossAsked oftenNot started
Combine rows from two tables, and keep or drop the ones without a match.
- Dana never paid, and finance never noticed
- Grace cancelled, and we still asked if she’d ordered twice
Watch it run: Your own query · The payments report · The double-order check
Start35 min read, about 1 h 40 min with practiceBuilds on: Tables, rows and keys
- 8. Simple subqueriesNot started
Use one query's result inside another query.
Watch it run: The reminder list · The bottle offer
Start15 min read, about 50 min with practiceBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 9. INSERT, UPDATE and DELETENot started
Add, change and remove rows safely.
Watch it run: The duplicate cleanup
Start15 min read, about 50 min with practiceBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 10. Data types and constraintsNot started
Pick the right column types and let the database reject bad data.
Start15 min read, about 50 min with practiceBuilds on: Tables, rows and keys
Core
- 1. Logical query processing orderNot started
The order the database really runs a query in, which explains many errors.
Watch it run: Your own query · The warehouse list · The second-highest price · The sales tile · The loyalty coupon · The welcome list
Start15 min read, about 45 min with practiceBuilds on: GROUP BY and HAVING · Joins: inner, left, right, full, self and cross
- 2. CTEsAsked oftenNot started
Name a part of a query with WITH, so long queries read top to bottom.
Watch it run: The pay list
Start15 min read, about 50 min with practiceBuilds on: Simple subqueries
- 4. EXISTS vs IN vs JOIN, and the NOT IN + NULL trapAsked oftenNot started
Three ways to ask "is there a match?", and the one that quietly returns nothing.
Watch it run: The window shoppers
Start20 min read, about 50 min with practiceBuilds on: NULL and three-valued logic · Correlated subqueries
- 5. UNION vs UNION ALLComing soon
Stack two results, with or without removing duplicates.
10 min readBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 6. CASE expressionsComing soon
If-then-else inside a query.
10 min readBuilds on: SELECT, WHERE, ORDER BY and LIMIT
- 7. Date and string functionsComing soon
Group by month, tidy text and other everyday conversions.
20 min readBuilds on: GROUP BY and HAVING
- 8. Window functions: ranking, LAG, LEAD, running totals, framesAsked oftenNot started
Rank rows, compare with the previous row and keep running totals, without losing rows.
Watch it run: Window functions
Start40 min read, about 1 h 30 min with practiceBuilds on: Logical query processing order
- 9. Conditional aggregation and pivotsComing soon
Count several things in one pass, and turn rows into columns.
20 min readBuilds on: CASE expressions · GROUP BY and HAVING
Advanced
- 1. Indexes: B-tree, composite, covering, partial, and when they hurtAsked oftenComing soon
Like a book's index: find rows fast, at the cost of slower writes.
25 min readBuilds on: Joins: inner, left, right, full, self and cross
- 2. Reading EXPLAIN plansComing soon
Ask the database how it will run a query, and spot the slow step.
20 min readBuilds on: Indexes: B-tree, composite, covering, partial, and when they hurt
- 3. Query tuningComing soon
Make a slow query fast, step by step, with proof.
25 min readBuilds on: Reading EXPLAIN plans
- 4. Normalization and when to denormalizeComing soon
Store each fact once, and when breaking that rule is worth it.
20 min readBuilds on: Data types and constraints
- 5. Transactions, ACID and isolation levelsAsked oftenComing soon
Group changes so all happen or none do, and what other users see meanwhile.
25 min readBuilds on: INSERT, UPDATE and DELETE
- 6. Locks, MVCC and deadlocksComing soon
How many users change the same data at once without clashing.
20 min readBuilds on: Transactions, ACID and isolation levels
- 7. Recursive CTEsComing soon
Walk a tree, like an org chart, in plain SQL.
15 min readBuilds on: CTEs
- 8. NoSQL in practice: MongoDB documents, queries and indexesComing soon
Store JSON-like documents, query and index them, and know when that beats tables.
25 min readBuilds on: Indexes: B-tree, composite, covering, partial, and when they hurt · SQL vs NoSQL (System Design)
- 9. Gaps and islands, sessionization and deduplicationComing soon
Find streaks, sessions and duplicates with window functions.
25 min readBuilds on: Window functions: ranking, LAG, LEAD, running totals, frames
Interview-ready
- 1. Schema design questionsAsked oftenComing soon
Design the tables for a real app out loud, with keys and trade-offs.
30 min readBuilds on: Normalization and when to denormalize
- 2. OLTP vs OLAP, star and snowflake schemas, slowly changing dimensionsComing soon
App databases versus reporting warehouses, and how warehouse tables are shaped.
20 min readBuilds on: Normalization and when to denormalize
- 3. Partitioning and replication basicsComing soon
Split a big table into pieces, and keep copies on other servers.
15 min readBuilds on: Indexes: B-tree, composite, covering, partial, and when they hurt
- 4. ORM vs raw SQL and the N+1 problemComing soon
When to let the ORM write SQL, and the hidden one-query-per-row bug.
15 min readBuilds on: ORMs and migrations (Frameworks)
- 5. Problem bank: nth-highest, top-N per group, streaks, cohorts, funnels, median and moreAsked oftenComing soon
Classic SQL interview questions, each solved Okay, Better, Best.
45 min readBuilds on: Window functions: ranking, LAG, LEAD, running totals, frames