English · Українська
From "I can write a SELECT" to "I can design a schema, index it, and query it under transactions."
Time commitment: ~3–4 hours a day, ~5 study days a week. The 20 blocks build progressively; each week ends with a review/rest day to consolidate. Do the blocks in order — later ones assume earlier ones.
How to measure progress: a block is done when its test suite passes (npx vitest blocks/NN-...)
and you can explain, out loud, why each query works. Commit after every block.
The dataset: every block queries the same small store schema — customers, products,
orders, order_items (see db/schema.sql / db/seed.sql). Learning one schema deeply beats
skimming ten.
From "spreadsheet thinking" to "set thinking".
| Day | Block | You'll be able to… |
|---|---|---|
| 1 | 01 Getting Started | Explain what a relational DB is, connect, and run your first SELECT. |
| 2 | 02 SELECT & Filtering | Project columns, filter rows with WHERE, and alias with AS. |
| 3 | 03 Operators & Expressions | Use AND/OR/NOT, IN, BETWEEN, LIKE, and handle NULL correctly. |
| 4 | 04 Sorting & Pagination | Sort with ORDER BY, page with LIMIT/OFFSET, and know why keyset paging is better. |
| 5 | 05 Data Types & NULL | Reason about text/number/date/boolean types, casts, and three-valued logic. |
| 6–7 | Review | Re-solve week 1 from memory. Write 5 queries against the store with no hints. |
The week most self-taught devs skip — and the one interviews live in.
| Day | Block | You'll be able to… |
|---|---|---|
| 8 | 06 Aggregation | Summarize with COUNT, SUM, AVG, MIN, MAX. |
| 9 | 07 GROUP BY & HAVING | Group rows, filter groups with HAVING, and avoid the classic WHERE vs HAVING mistake. |
| 10 | 08 Joins — INNER | Combine tables with INNER JOIN and understand join keys. |
| 11 | 09 Joins — OUTER & self | Use LEFT/RIGHT/FULL joins, self-joins, and know when rows disappear. |
| 12 | 10 Subqueries & CTEs | Write scalar/correlated subqueries and readable WITH (CTE) queries. |
| 13–14 | Review | Rebuild a multi-table report (e.g. revenue per customer) end to end. |
Stop being only a "reader" of databases and start owning them.
| Day | Block | You'll be able to… |
|---|---|---|
| 15 | 11 Creating Tables (DDL) | Design tables with the right types using CREATE TABLE. |
| 16 | 12 Constraints & Keys | Enforce integrity with PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL. |
| 17 | 13 INSERT / UPDATE / DELETE | Mutate data safely with RETURNING and INSERT ... ON CONFLICT (upsert). |
| 18 | 14 Indexes & EXPLAIN | Read EXPLAIN ANALYZE, add B-tree indexes, and prove a query got faster. |
| 19 | 15 Transactions & Isolation | Use BEGIN/COMMIT/ROLLBACK, understand ACID, isolation levels, and locks. |
| 20–21 | Review | Design a small schema from a written spec, seed it, and query it. |
The tools that separate "can query" from "can build on Postgres".
| Day | Block | You'll be able to… |
|---|---|---|
| 22 | 16 Window Functions | Use OVER, PARTITION BY, ROW_NUMBER/RANK, running totals. |
| 23 | 17 Advanced Postgres | Work with JSONB, arrays, enums, and generated columns. |
| 24 | 18 Views & Normalization | Create views/materialized views and normalize to 3NF; spot the N+1 problem. |
| 25 | 19 SQL from TypeScript | Run parameterized queries safely (no SQL injection), pool connections, and know where an ORM fits. |
| 26–30 | 20 Capstone Project | Design, seed, index, and query a complete schema — and defend every decision. |
You'll be ready to: model data for a real feature, write and review non-trivial queries, diagnose a
slow query with EXPLAIN, reason about transactions and race conditions, and answer the SQL half of a
full-stack interview without freezing. Next steps: pair this with node-learn/express-learn to wire
a typed API to a real database, then learn a query builder / ORM (Drizzle or Prisma) on top of the SQL
you now actually understand.