LE PETIT JOURNAL
THE CLERK'S HANDBOOK

SQL glossary

Game: the terminal of every chapter

Every SQL word the handbook uses, each in a line or two, most with a short example. Each word links to the lesson that explains it.

The examples use the bakery database. When a word is new to you, read its line here, then follow the link for the full lesson.

The words of a database

  • databaseA collection of tables kept together, such as the bakery's six tables.
  • tableA grid of data about one kind of thing: product holds the products.
  • rowOne line of a table: one product, one order.
  • columnOne named field that every row of a table has, such as price.
  • typeWhat a column holds: TEXT, INTEGER (whole numbers) or REAL (numbers with decimals).
  • primary keyThe column that tells the rows apart, usually id: no two rows share a value.
  • foreign keyA column that holds another table's primary key, such as orders.customer_id. It links the two tables.
  • schemaThe list of tables, their columns and the keys between them. The diagram on the exercises page is one.
  • NULLThe mark of a missing value. It is neither 0 nor empty text, and it equals nothing, not even another NULL.

Writing a query

  • queryA question written in SQL. It reads the tables and answers with a result, which is itself a small table.
  • keywordA word SQL reserves for itself, such as SELECT or WHERE. Capitals are a habit; SQL reads select just as well.
  • clauseOne part of a query, starting with its keyword: the FROM clause, the WHERE clause.
  • order of executionThe order in which the clauses are worked out: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
  • 'text' and "name"Single quotes hold a value: 'cake'. Double quotes hold a name, and you will rarely need them.
  • ;Ends a query. A terminal that runs one query at a time does not need it.

Reading rows

  • SELECTNames the columns to show. SELECT name, price FROM product
  • *After SELECT, every column of the table. SELECT * FROM staff
  • FROMNames the table to read.
  • AS (alias)Gives a column of the result a name: price * 2 AS two_of_them. In FROM orders o, the table gets a short name, o.
  • DISTINCTRemoves the rows of the result that repeat. SELECT DISTINCT category FROM product
  • + - * /Arithmetic on numbers. quantity * unit_price

Filtering rows

  • WHEREKeeps only the rows for which a condition is true. WHERE price < 2
  • conditionSomething that is true or false for each row, such as category = 'cake'.
  • = <> < <= > >=Equal, not equal, less than, at most, more than, at least. SQL compares with one equals sign.
  • AND, OR, NOTCombine conditions: both true, at least one true, the opposite. Add brackets when you mix AND and OR.
  • INTrue when the value is in a list. town IN ('Hillcrest', 'Old Town')
  • BETWEENTrue inside a range, both ends included. price BETWEEN 2 AND 3
  • LIKEMatches text against a pattern, capitals counting. name LIKE '%tart'
  • % and _The wildcards of a pattern: % stands for any run of characters, even none, _ for exactly one.
  • ILIKELIKE with capitals ignored. name ILIKE '%croissant%'
  • IS NULL, IS NOT NULLThe only way to test for a missing value. email = NULL is never true.

Sorting and keeping some rows

  • ORDER BYSorts the result. Without it, the rows come in no promised order.
  • ASC, DESCSmallest first (the default) or largest first. ORDER BY price DESC
  • LIMITKeeps the first rows of the result. ORDER BY price DESC LIMIT 3 gives the three dearest.
  • OFFSETSkips rows before LIMIT starts counting. LIMIT 3 OFFSET 3 gives rows 4 to 6.

Counting and summing

  • aggregate functionA function that turns many rows into one value: COUNT, SUM, AVG, MIN, MAX.
  • COUNTCOUNT(*) counts rows; COUNT(email) counts the rows where email is not NULL; COUNT(DISTINCT town) counts different values.
  • SUMAdds up a column. SUM(quantity)
  • AVGThe average of a column. AVG(price)
  • MIN, MAXThe smallest and the largest value of a column.
  • ROUNDRounds a number. ROUND(AVG(price), 2) keeps two decimals.
  • GROUP BYMakes one group per value, and the aggregates then work per group. SELECT category, COUNT(*) FROM product GROUP BY category
  • HAVINGKeeps only the groups for which a condition is true. HAVING COUNT(*) >= 5
  • string_aggJoins the texts of a group into one. string_agg(name, ', ')

Text, numbers and dates

  • ||Joins two texts into one. name || ' (' || town || ')'
  • lowerWrites a text in small letters. lower(email)
  • trimRemoves the spaces at both ends of a text. trim(email)
  • substrTakes part of a text: substr(day, 6, 2) starts at character 6 and takes 2, the month of '2026-03-14'.
  • LEFT, RIGHTThe first or the last characters of a text. LEFT(time, 2) is the hour.
  • CASTTurns a value into another type. CAST('07' AS INTEGER) is the number 7.
  • ISO dateA date written year, month, day: '2026-03-14'. As text, it sorts in calendar order.

Missing values

  • COALESCEGives the first value that is not NULL. COALESCE(email, 'none')
  • NULLIFGives NULL when two values are equal, often to avoid dividing by zero. NULLIF(n, 0)
  • three-valued logicA comparison with NULL is neither true nor false: it is unknown, and WHERE drops the rows where it is unknown.

Choosing a value

  • CASE WHENPicks a value by condition, like an if. CASE WHEN price < 2 THEN 'cheap' ELSE 'dear' END
  • THEN, ELSE, ENDThe value for a true condition, the value when none is true (NULL if left out), and the end of the CASE.

Combining tables

  • JOINPairs the rows of two tables that match, and keeps only those pairs. Also written INNER JOIN.
  • ONSays which rows match. JOIN customer c ON o.customer_id = c.id
  • LEFT JOINLike JOIN, but keeps every row of the first table, with NULLs where nothing matched.
  • self-joinA table joined to itself under two short names, for example each member of staff beside their manager.

Queries inside queries

  • subqueryA query in brackets inside another query.
  • scalar subqueryA subquery that returns one value. WHERE price > (SELECT AVG(price) FROM product)
  • IN (subquery)True when the value is among the subquery's results. id IN (SELECT customer_id FROM orders)
  • EXISTS, NOT EXISTSTrue when the subquery finds at least one row, or none.
  • correlated subqueryA subquery that uses a column of the outer query, so it runs again for each outer row.
  • derived tableA subquery in FROM, read like a table. It needs a name: FROM (SELECT ...) AS t.

Stacking results

  • UNIONPuts the rows of two queries in one list and removes repeats. Both queries need the same number of columns.
  • UNION ALLLike UNION, but keeps every row, repeats included.
  • INTERSECTThe rows both queries return.
  • EXCEPTThe rows of the first query that the second does not return.

Window functions

  • window functionComputes a value for each row from a set of related rows. Every row stays, where GROUP BY would fold them into one.
  • OVERFollows a window function and says which rows it looks at, in which order. RANK() OVER (ORDER BY price DESC)
  • PARTITION BYInside OVER, starts the window again for each value. OVER (PARTITION BY category ORDER BY price)
  • ROW_NUMBERNumbers the rows 1, 2, 3 and so on, ties or not.
  • RANK, DENSE_RANKRanks the rows and gives ties the same rank. After two firsts, RANK goes on at 3 and DENSE_RANK at 2.
  • LAG, LEADThe value of a column in the previous row, or in the next one. LAG(day) OVER (ORDER BY day)

Named steps

  • WITH (CTE)Names a query so the next step can read it like a table. WITH cheap AS (SELECT * FROM product WHERE price < 2) SELECT name FROM cheap
  • WITH RECURSIVEA CTE that reads its own rows to add more: it follows a chain, such as who referred whom, or builds a list of days.