SQL starters, one query per page
The twenty-two first queries that work: SELECT and WHERE, joins, grouping, sorting, NULL, and inserting and updating safely, one move per page.
You have the data. These are the questions.
SQL starters, one query per page
The twenty-two first queries that work: SELECT and WHERE, joins, grouping, sorting, NULL, and inserting and updating safely, one move per page.
Set in Space Grotesk, Inter and JetBrains Mono (SIL Open Font License).
SQL behaviour verified against the PostgreSQL documentation (postgresql.org/docs, SQL language reference), the SQLite documentation (sqlite.org/lang.html), and the MySQL 8.0 Reference Manual (dev.mysql.com/doc/refman/8.0). PostgreSQL is a trademark of the PostgreSQL Community Association of Canada; MySQL is a trademark of Oracle; SQLite is in the public domain. This book is an independent guide and is not affiliated with or endorsed by any of them.
Your purchase is for personal use only. You do not have redistribution rights: please do not share, resell, or republish this book or its pages.
© 2026 Steve Hodgkiss. All rights reserved. Personal use only; no redistribution rights.
Edition 1.0 · stevehodgkiss.net
Contents
First queries
What a database is, the query that reads it, the clause that asks the real question, and the two traps in every first WHERE.
- 01Your first query
- 02WHERE: the real question
SELECT lists the output columns and FROM names the table; SELECT * FROM products returns every row and column. From the PostgreSQL documentation, Tutorial, Querying a Table. postgresql.org/docs.
Your first query
Let's say you want to see what's in products. In SQL you don't open the table. You ask for it.
SELECT name, price FROM products; returns the two columns you name, for every row. The star, *, means every column: fine for a look, sloppy in code. Column names are the table's real columns; guessing one that isn't there is the first error you'll meet.
SELECT says what you want back. FROM says where it lives.
Run SELECT name, price FROM products; then SELECT * FROM products; and count the columns in each.
WHERE eliminates rows for which the condition is not true; each row is tested against the search condition. From the PostgreSQL documentation, Tutorial, Where, and the SQL language reference. postgresql.org/docs.
WHERE: the real question
Let's say products has four hundred rows and you need the ones under £10. Scrolling isn't a question.
WHERE price < 10 keeps only the rows for which the test is true. The test runs row by row: each row on its own, keep or drop. One wrong character in the condition matches nothing, and SQL doesn't warn you; it returns nothing, quietly.
WHERE is a question the table answers row by row.
Run SELECT name FROM products WHERE price < 10; then change < to <= and find which extra row joins.
NULL means unknown; comparisons with NULL are not true, so WHERE col = NULL matches nothing and IS NULL is the test that works; COUNT(*) counts all rows while COUNT(col) skips NULLs. From the PostgreSQL documentation, Comparison Operators and Aggregate Functions. postgresql.org/docs.
NULL is not a value
Let's say some products have no supplier yet. You put a dash, a zero and a blank in the column, and now three different mistakes look like data.
NULL means unknown: not zero, not an empty string. Comparing against unknown isn't true, so = NULL never matches; IS NULL is the test that works. COUNT(*) counts every row, COUNT(supplier) skips the NULLs: one table, two numbers, both right.
Zero is a value. NULL is the question mark.
Run SELECT COUNT(*), COUNT(supplier) FROM products; and let the gap between the numbers count the missing suppliers.
JOIN with ON combines rows from two tables into one result row for each pair of rows that satisfies the condition. From the PostgreSQL documentation, SELECT, Table Expression, Joined Tables. postgresql.org/docs.
The inner join
Let's say orders has product_id and you want names, not numbers. The answer is half in each table.
FROM orders JOIN products ON orders.product_id = products.id pairs each order with its product row. Every pair of rows for which the ON condition is true becomes one row of the result. A join condition that's always true builds every combination, a cross join: it runs, and returns a wall of nonsense.
A join is a pairing rule. The ON says which rows belong together.
Run SELECT orders.id, products.name FROM orders JOIN products ON orders.product_id = products.id;
UPDATE table SET column = expression WHERE condition changes the named columns of matching rows; without WHERE it updates every row in the table. From the PostgreSQL documentation, UPDATE reference. postgresql.org/docs.
UPDATE, safely
Let's say the travel mug's price goes up a pound. You don't edit a cell. You change the rows that match.
UPDATE products SET price = 13.5 WHERE name = 'Travel mug'; changes only the matching rows. Test the WHERE as a SELECT first: the rows the SELECT shows are the rows the UPDATE hits. An UPDATE with no WHERE changes every row in the table, and reports success.
Select the WHERE first. Then update through it.
Write the SELECT for your UPDATE's WHERE, check the rows it shows, then run the UPDATE and select the row back.