SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Database Creation and Management

So far you have read data; now you build and change it. You'll design tables with the right data types, keys and constraints so bad data cannot get in; insert, update and delete rows with the care production data deserves (preview first, filter precisely, return what changed); use upserts, views and transactions; and change a live schema the way experienced teams do, with additive steps and backfills. Every exercise runs on a disposable copy of the database, so you can experiment freely, and the playground's Reset data button restores everything.

Start the track

Lessons

  1. Creating a tableDefine a new table with CREATE TABLE: its name, its columns, their types and the rules every row must follow.18 min · 4 exercisesFree
  2. Choosing data typesPick the right type for every column: exact numeric for money, text or varchar for strings, boolean for flags, date and timestamptz for time.20 min · 4 exercises Members
  3. Primary keys and identity columnsGive every row a reliable identity: natural versus surrogate keys, composite keys, and identity columns that number rows for you.20 min · 4 exercises Members
  4. Foreign keysLink tables with REFERENCES so every child row points at a real parent, and decide what happens when a parent is deleted.22 min · 4 exercises Members
  5. CHECK, UNIQUE, NOT NULL and DEFAULTWrite the business rules into the table: required values, defaults, uniqueness and CHECK conditions, with names you can recognise in error messages.22 min · 4 exercises Members
  6. Inserting rowsAdd rows with INSERT: name the columns, insert many rows at once, use defaults and NULLs deliberately, and copy rows from a query with INSERT … SELECT.18 min · 4 exercises Members
  7. Changing rows with UPDATEChange existing values with UPDATE … SET … WHERE, preview the affected rows first, and update from another table with UPDATE … FROM.20 min · 4 exercises Members
  8. Removing rows with DELETEDelete exactly the rows you mean: preview with SELECT, filter precisely, respect foreign keys, and delete based on other tables with DELETE … USING or NOT EXISTS.18 min · 4 exercises Members
  9. Upserts and RETURNINGInsert-or-update in one statement with INSERT … ON CONFLICT, and see exactly which rows changed with RETURNING.22 min · 4 exercises Members
  10. Changing a table with ALTER TABLEAdd, drop and rename columns, change types and defaults, and add constraints to tables that already contain data.22 min · 4 exercises Members
  11. Dropping tables, views and columns safelyRemove tables, views and columns with DROP, use IF EXISTS in scripts, read dependency errors, and understand exactly what CASCADE takes with it.18 min · 4 exercises Members
  12. ViewsSave a query under a name with CREATE VIEW, query it like a table, and change it safely.20 min · 4 exercises Members
  13. TransactionsGroup several changes with BEGIN, COMMIT and ROLLBACK so they succeed or fail together, and undo part of the work with SAVEPOINT.24 min · 4 exercises Members
  14. Referential integrityKeep every reference pointing at a real row: find orphans, choose what happens on delete, and add foreign keys to tables that already hold data.22 min · 4 exercises Members
  15. Safe schema changesChange a live schema the way experienced teams do: small additive migrations, backfills, then tightening, with a way back.25 min · 4 exercises Members