LE PETIT JOURNAL
THE CLERK'S HANDBOOK

The Clerk's Handbook

By AlephB

Plain-English pages on the SQL that The Ritz Affair practises, for a first SQL course. Every example runs on a small bakery database, never the game's archives, so nothing here gives a chapter away. The game's terminal follows PostgreSQL's rules; the last page, The Grammar of SQL, sums them up.

Where to start

  1. Warm upWrite a first query in two minutes.
  2. ReadMeet the bakery database, then one construct per page.
  3. PractiseTwenty graded questions, checked when you run them.
  4. PlayPut it all to work in The Ritz Affair.

Learning chapters

Read: one construct per page, with examples on the bakery database.

  1. The bakery databasethe example data, with a script to recreate it.
  2. Tables, keys and the schemakeys, relationships, exploring a database.
  3. SELECT and WHEREchoosing columns and filtering rows.
  4. ORDER BY and LIMITsorting and keeping the top rows.
  5. LIKE and text patternssearching text with wildcards.
  6. Dates and times stored as text or numberscomparing, ranges, substr and CAST, and why ISO dates sort.
  7. GROUP BY, aggregates and HAVINGcounting and summing per group.
  8. JOIN, LEFT JOIN and self-joinscombining tables and finding what is missing.
  9. NULL and missing valuesthree-valued logic, COUNT, COALESCE and the NOT IN trap.
  10. CASE WHENlabels, buckets and conditional counts.
  11. Subqueriesscalar, IN, EXISTS, correlated and derived tables.
  12. UNION, INTERSECT and EXCEPTstacking two results, the rows they share, the rows one lacks.
  13. Window functions: RANK and LAGranking rows and comparing with the previous one.
  14. CTEs and WITH RECURSIVEnamed steps and following chains.

Practice and reference

SQL exercises with solutionsTwenty graded questions in three levels, from a first filter to a recursive chain: run your query in the page's terminal and it is checked against the question. Solutions stay hidden until you open them. The Grammar of SQLThe rules the game's terminal follows: grouping, quotes, types and conditions, done right, each linked to the chapter that explains it.