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:
productholds 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) orREAL(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
SELECTorWHERE. Capitals are a habit; SQL readsselectjust as well. - clauseOne part of a query, starting with its keyword: the
FROMclause, theWHEREclause. - 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*AfterSELECT, every column of the table.SELECT * FROM staffFROMNames the table to read.AS(alias)Gives a column of the result a name:price * 2 AS two_of_them. InFROM 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 mixANDandOR.INTrue when the value is in a list.town IN ('Hillcrest', 'Old Town')BETWEENTrue inside a range, both ends included.price BETWEEN 2 AND 3LIKEMatches 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.ILIKELIKEwith capitals ignored.name ILIKE '%croissant%'IS NULL,IS NOT NULLThe only way to test for a missing value.email = NULLis 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 DESCLIMITKeeps the first rows of the result.ORDER BY price DESC LIMIT 3gives the three dearest.OFFSETSkips rows beforeLIMITstarts counting.LIMIT 3 OFFSET 3gives 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 whereemailis 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 categoryHAVINGKeeps only the groups for which a condition is true.HAVING COUNT(*) >= 5string_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
WHEREdrops 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' ENDTHEN,ELSE,ENDThe value for a true condition, the value when none is true (NULL if left out), and the end of theCASE.
Combining tables
JOINPairs the rows of two tables that match, and keeps only those pairs. Also writtenINNER JOIN.ONSays which rows match.JOIN customer c ON o.customer_id = c.idLEFT JOINLikeJOIN, 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
Window functions
- window functionComputes a value for each row from a set of related rows. Every row stays, where
GROUP BYwould 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 BYInsideOVER, 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,RANKgoes on at 3 andDENSE_RANKat 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 cheapWITH 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.