The Clerk's Handbook
Plain-English pages on the SQL that The Ritz Affair practises, for a first SQL course. SQL is the language used to ask a database questions; one question written in SQL is a query. Each page explains one piece of the language and defines its words as they come up. Every example runs on a small bakery database, never the game's archives, so nothing here gives a chapter away. The game's terminal, the box where you type and run a query, follows PostgreSQL's rules; The Grammar of SQL sums them up.
Where to start
- Warm upWrite a first query in two minutes.
- ReadMeet the bakery database, then one piece of SQL per page.
- PractiseTwenty-three graded questions, checked when you run them.
- PlayPut it all to work in The Ritz Affair.
Learning chapters
Read: one piece of SQL per page, with examples on the bakery database.
- The bakery databasethe example data, with a script to recreate it.
- Tables, keys and the schemakeys, relationships, exploring a database.
- SELECT and WHEREchoosing columns and filtering rows.
- ORDER BY and LIMITsorting and keeping the top rows.
- LIKE and text patternssearching text with wildcards.
- Dates and times stored as text or numberscomparing, ranges, substr and CAST, and why ISO dates sort.
- GROUP BY, aggregates and HAVINGcounting and summing per group.
- JOIN, LEFT JOIN and self-joinscombining tables and finding what is missing.
- NULL and missing valuesthree-valued logic, COUNT, COALESCE and the NOT IN trap.
- CASE WHENlabels, buckets and conditional counts.
- Subqueriesscalar, IN, EXISTS, correlated and derived tables.
- UNION, INTERSECT and EXCEPTstacking two results, the rows they share, the rows one lacks.
- Window functions: RANK and LAGranking rows and comparing with the previous one.
- CTEs and WITH RECURSIVEnamed steps and following chains.
Practice and reference
SQL exercises with solutionsTwenty-three graded questions in three levels, from a first SELECT 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.
SQL glossaryEvery keyword and function in a line or two, with a short example and a link to its lesson. Start here if SQL is new to you.
PostgreSQL vs SQLiteA server against a single file, when to use which, and the differences you meet in a query, from LIKE and dates to GROUP BY, with a summary table.