backendgym backendgym — reps Sign in with GitHubSign in
← Reps
PostgreSQL Intermediate

SQL · Designing and writing

Turn requirements into tables, constraints and keys, then insert, load, update and delete without leaving the data in a state you did not mean.

sql-designing-and-writing / README.md

bgym start sql-designing-and-writing brings up a pinned Postgres 16 container with a small MBTA CharlieCard schema. Nothing is installed on your machine. The tables and seed data live in seed/; write SQL in reps/NN/answer.sql, and bgym run NN sends it to psql. bgym reset recreates the volume and runs the seed again.

You should have finished sql-querying and be comfortable reading joins and aggregates. This set asks you to turn requirements into schema objects and to change data without leaving referential or audit damage behind.

The data has riders, cards, stations and swipes. It is deliberately imperfect: a station name has trailing whitespace, a card has no swipes, a station has no swipes, and a blocked card remains in the data. Balances use numeric, timestamps carry time zones, and card status is an enum. Several reps wrap their changes in a transaction and roll them back after inspecting the result.

Do sql-querying before this set. Do this set before sql-viewing.

Reps

In order — each one assumes the ones before it. Open a rep to read it.

  1. 01 01 · Shape a staging table
  2. 02 02 · Inspect the card schema
  3. 03 03 · Include cards with no swipes
  4. 04 04 · Import through staging
  5. 05 05 · Delete through foreign keys
  6. 06 06 · Block only the empty card
  7. 07 07 · Clean station names
  8. 08 08 · Audit a status change
  9. 09 09 · Soft-delete one card