The SQL guide

0Game terminology

The names below are the game's vocabulary. They describe the learning experience; SQL itself follows SQLite syntax.

IHow the Keep reads SQL

• Write one statement that starts with SELECT or WITH. A trailing ; is fine.
• Keywords are not case-sensitive: select and SELECT are the same. Table and column names are not either.
• Text values go in single quotes: 'Saffron'. Double quotes are for names: "orders".
• The Steward compares your result, not your query. Any correct SQL that returns the right rows wins.

IIThe ledger

These are the tables in catalogue-2-5.sqlite, read from the file itself. Each quest names the tables it needs.

Dates are stored as text in the form YYYY-MM-DD, which sorts and compares correctly as text.

IIISELECT and columns

Pick the columns you want, in the order you want them. AS renames a column in the result.

SELECT name, region_id AS region FROM customers LIMIT 4

SELECT * returns every column — handy for a first look with RUN, rarely what an answer wants.

DISTINCT removes duplicate rows from the result:

SELECT DISTINCT product FROM order_items ORDER BY product

IVFiltering rows

WHERE keeps only the rows where the condition is true.

SELECT id, product, qty FROM order_items WHERE product IN ('Amber', 'Saffron') AND qty >= 2

VSorting and limiting

ORDER BY sorts; add DESC for largest first. Sort by several columns to break ties. LIMIT keeps the first rows after sorting.

SELECT product, unit_price FROM order_items ORDER BY unit_price DESC, product LIMIT 3

VICounting and totals

Aggregates fold many rows into one value: COUNT, SUM, AVG, MIN, MAX. GROUP BY makes one result row per group; HAVING filters groups after they are totalled (WHERE filters rows before).

SELECT region_id, COUNT(*) AS customers FROM customers GROUP BY region_id ORDER BY region_id

SELECT product, SUM(qty * unit_price) AS takings FROM order_items GROUP BY product HAVING SUM(qty * unit_price) > 1000 ORDER BY takings DESC

VIIJoining tables

JOIN … ON pairs rows from two tables where the condition holds. Give tables short aliases and prefix columns with them.

SELECT c.name, r.name AS region FROM customers c JOIN regions r ON r.id = c.region_id ORDER BY c.name LIMIT 4

LEFT JOIN keeps every row from the left table even with no match — the right side is then NULL. That makes it the tool for “which ones have none?”:

SELECT r.name, COUNT(c.id) AS customers FROM regions r LEFT JOIN customers c ON c.region_id = r.id GROUP BY r.id, r.name ORDER BY r.name

A query may use at most 5 tables in total (a CTE counts as all the tables inside it). No quest needs more.

VIIISubqueries

A query in brackets can stand in for a value, a list, or a table.

SELECT product, unit_price FROM order_items WHERE unit_price > (SELECT AVG(unit_price) FROM order_items) ORDER BY unit_price DESC

SELECT name FROM regions WHERE id IN (SELECT region_id FROM customers WHERE joined_on >= '2026-01-01') ORDER BY name

IXWITH (CTEs)

WITH name AS (…) names a query so the main query can use it like a table. It makes long answers readable. Several can be chained with commas.

WITH takings AS (
  SELECT product, SUM(qty * unit_price) AS total FROM order_items GROUP BY product
)
SELECT product, total FROM takings WHERE total > (SELECT AVG(total) FROM takings) ORDER BY total DESC

XWindow functions

A window function computes over related rows without folding them into one. OVER (PARTITION BY … ORDER BY …) says which rows, in what order.

SELECT product, qty, ROW_NUMBER() OVER (PARTITION BY product ORDER BY qty DESC) AS nth FROM order_items ORDER BY product, nth

SELECT joined_on, name, COUNT(*) OVER (ORDER BY joined_on) AS members_so_far FROM customers ORDER BY joined_on

To keep only the top row of each group, number the rows in a CTE and filter on the number in the main query.

XICASE

CASE WHEN … THEN … ELSE … END chooses a value per row.

SELECT product, qty, CASE WHEN qty >= 10 THEN 'bulk' WHEN qty >= 4 THEN 'crate' ELSE 'handful' END AS size FROM order_items ORDER BY qty DESC LIMIT 5

XIIDates

Dates are YYYY-MM-DD text, so >= and BETWEEN work directly. strftime pulls parts out: '%Y' year, '%m' month, '%Y-%m' year-month.

SELECT strftime('%Y-%m', joined_on) AS month, COUNT(*) AS joined FROM customers GROUP BY month ORDER BY month

date('2026-03-01', '+1 month') moves a date. “On or after 1 March” is >= '2026-03-01'; “before 2026” is < '2026-01-01'.

XIIIText and numbers

SELECT 7 / 2 AS whole, 7 / 2.0 AS exact, ROUND(7 / 3.0, 2) AS rounded

XIVNULL

NULL means “no value”. It is not zero and not empty text. Anything compared with NULL is unknown, so WHERE x = NULL never matches — write x IS NULL.

• COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL.
• SUM and AVG skip NULLs.
• COALESCE(x, 0) gives 0 when x is NULL.

XVHow answers are graded

When you SUBMIT, the Steward runs the quest's own answer on the same ledger and compares the two results.

XVIWhat is not allowed

A refused query never costs a life.

XVIIMessages you may see

⚔ Ride out to the Keep   or read the rules.