Skip to main content

Book Exercise Lanes

This module's exercise system is book-driven and tool-driven. Use these lanes for targeted volume after you have already learned the concept from the guide.

How To Use This Page​

  1. Finish the relevant concept page first.
  2. Solve at least one query / schema design from memory against a seeded PostgreSQL database.
  3. Only then open the matching exercise lane.
  4. Keep a mistake log with tags such as three-valued-logic bug, join direction flipped, missing GROUP BY column, non-sargable predicate, wrong isolation level, cascade direction wrong, N+1, non-repeatable read, violated constraint at runtime.

Lane 1: The Relational Model and Relational Algebra​

Use this lane when the issue is translating English to algebra or algebra to SQL.

Target outcomes:

  • 10 English-to-algebra translations across selection, projection, join, union, difference
  • 10 algebra-to-SQL translations on a schema you designed
  • 5 written "which is the primary key and why" arguments
  • 2 "this relation is not a set because..." corrections of a starter schema

Lane 2: SQL Query Writing​

Use this lane when the issue is translating a requirement into correct, efficient SQL.

Target outcomes:

  • 25 PGExercises problems solved
  • 10 self-written queries on your own schema across the 5 question shapes: filter, aggregate, anti-join, top-N-per-group, recursive CTE
  • 5 written "the naive query was wrong because NULL / duplicates / grouping..." corrections

Lane 3: Schema Design and Normalization​

Use this lane when you can write SQL but cannot defend your schema.

Target outcomes:

  • 6 normalize-this-table exercises (to 3NF and, when different, to BCNF) with lossless-decomposition checks
  • 3 full ER diagrams translated to DDL for domains you chose
  • 2 written "this schema has the following anomaly risk; here is the denormalization tradeoff" memos

Lane 4: Constraints, DDL, and Migrations​

Use this lane when the issue is enforcing invariants in the database or evolving the schema without downtime.

Target outcomes:

  • DDL for 3 domains with every row-level invariant declared as NOT NULL, UNIQUE, CHECK, or FOREIGN KEY
  • 3 expand-backfill-cut-over-contract plans for real migration shapes (rename, split, retype)
  • 2 CREATE VIEW + 1 CREATE MATERIALIZED VIEW + 1 CREATE FUNCTION on your schema, each with a one-sentence rationale

Lane 5: Query Performance and Transactions​

Use this lane when queries are correct but slow, or when concurrency bites.

Target outcomes:

  • 6 before/after EXPLAIN ANALYZE comparisons on your schema with the index or rewrite that caused the change
  • 3 N+1 rewrites in pseudocode with the corresponding SQL single-query version
  • 3 isolation-level experiments in two psql sessions (lost update, non-repeatable read, write skew if feasible) with written conclusions
  • 2 written "this query looked cheap but was dominated by X" post-mortems

How To Handle A Gap​

When you get stuck and none of the lanes click:

  1. Restate the task in one sentence.
  2. State which cluster(s) it sits in.
  3. Open only the referenced chunks for those clusters.
  4. If still stuck, open the authoritative external resource (PostgreSQL docs or Use The Index, Luke).
  5. Record the gap in your mistake log.