English · Українська
Learn core SQL & PostgreSQL in ~4 weeks the way you actually get good at it: by writing queries. 20 hands-on, test-driven blocks driven from strict TypeScript, each with runnable examples and verified reference solutions.
A complete, free course for developers who ship code but never learned SQL properly. You don't read about joins — you write real queries, make failing tests pass, and see them run against a real Postgres. Perfect for self-study, interview prep, or a study group.
- 🧪 Test-driven — every block ships failing tests; your query makes them green. No passive reading.
- 🐘 Real Postgres, zero setup — tests run against an embedded PostgreSQL (PGlite) so there's no Docker or install to fight. Switch to a real server with one env var.
- ✅ Verified solutions — every reference answer is itself tested (
npm run test:solutions), so what you compare against is guaranteed correct. - 🧠 Explain what you write — dedicated blocks on
EXPLAIN ANALYZE, indexes, and transactions so you understand why a query is fast or correct, not just that it passed. - 📦 Real-world finish — window functions, JSONB, normalization, calling SQL safely from TypeScript, and a full schema-design capstone.
# 1. Clone (or "Use this template" on GitHub)
git clone https://github.com/Rossoline/sql-learn.git
cd sql-learn
# 2. Install dependencies (Node.js 18+)
npm install
# 3. Run all tests — most fail until you write the queries
npm test
# 4. Work one block at a time, re-running on save
npx vitest blocks/01-getting-started- Read the block's
README.md. - Open
sql/examples.sql, read it, and run it to see the output:npm run play blocks/01-getting-started/sql/examples.sql
- Open
sql/exercises.sqland complete each-- TODOquery. - Run that block's tests until they all pass:
npx vitest blocks/01-getting-started
- Compare with
solutions/only after you've tried.
Every block runs against the same seed schema (see
db/schema.sqlanddb/seed.sql) — a small store withcustomers,products,orders, andorder_items. You'll know it by heart by week 2.
blocks/NN-topic/
├── README.md # the topic explained, with examples and the exercise list
├── sql/examples.sql # runnable, worked queries
├── sql/exercises.sql # practice queries with -- TODO stubs — your job
├── tests/ # automated tests (Vitest) that run your SQL and assert results
└── solutions/ # reference solutions (try the exercises first!)
The full day-by-day plan is in PLAN.md. Twenty blocks across four weeks:
| Week | Theme | Blocks |
|---|---|---|
| 1 | Querying | 01 Getting Started · 02 SELECT & Filtering · 03 Operators & Expressions · 04 Sorting & Pagination · 05 Data Types & NULL |
| 2 | Combining Data | 06 Aggregation · 07 GROUP BY & HAVING · 08 Joins — INNER · 09 Joins — OUTER & self · 10 Subqueries & CTEs |
| 3 | Schema & Speed | 11 Creating Tables (DDL) · 12 Constraints & Keys · 13 INSERT / UPDATE / DELETE · 14 Indexes & EXPLAIN · 15 Transactions & Isolation |
| 4 | Practical SQL | 16 Window Functions · 17 Advanced Postgres (JSONB/arrays) · 18 Views & Normalization · 19 SQL from TypeScript · 20 Capstone Project |
| Command | What it does |
|---|---|
npm test |
Run every block's tests (your progress). |
npm run test:solutions |
Verify the reference solutions all pass. |
npm run play <file.sql> |
Run any .sql file against a fresh seeded database. |
npx vitest blocks/NN-... |
Run/watch a single block. |
Each test spins up a fresh, seeded PostgreSQL database, runs the SQL you wrote in exercises.sql,
and asserts on the returned rows. Write the query correctly and its test goes green. The goal isn't
only passing tests — it's writing SQL you understand and could defend in a code review or interview.
- Use this template (or fork) so you can commit your solutions and build a streak.
- Commit after each block (
git commit -m "block 08 done"). - Don't skip the READMEs — the exercises assume you read them.
- For every query that passes, ask: "What index would this use? What happens on 10M rows?"
- Stuck for ~15 min? Read the solution, understand it, then re-write it from memory.
If this helped you finally get SQL, please ⭐ the repo — it helps others find it. Contributions and issue reports are welcome. Happy querying! 🐘