Language basics

23 steps in 5 sets of SQL.

SELECT, and everything immediately around it. Choosing rows, sorting and paging, filtering with the operators SQL gives you, then creating tables and getting rows into them.

Twenty-three steps. Written and verified against SQLite, so every query here actually ran against a real schema before it reached you.

Start this tour

Selecting rows

SELECT columnsThe shape of every query: pick columns, name a table, alias the output.
SELECT starThe star is fine at a prompt and a liability in code that has to keep working.
Computed columnsA SELECT list holds expressions, not just column names.
Filter with WHEREWHERE keeps only the rows whose condition holds.
DISTINCT valuesDISTINCT collapses duplicate rows down to one each.

Sorting & paging

Sort and limitORDER BY sorts the result; LIMIT trims it to the top rows.
Sorting on several keysEach sort key breaks ties in the one before it.
Pages with LIMIT and OFFSETPaging without an ORDER BY returns rows in whatever order the engine likes.

Filtering

AND, OR and parenthesesCombining conditions: parentheses make the precedence explicit.
IN and BETWEENSet membership and inclusive ranges, without a pile of OR clauses.
Pattern matching with LIKELIKE compares text against a pattern of wildcards.
NULL is not a valueNULL means unknown, so it needs IS rather than an equals sign.
Negating a conditionNOT reads well until NULLs arrive, and then it quietly drops rows.

Tables & rows

CREATE TABLEA table definition: columns, types and the constraints that guard them.
Foreign keysA foreign key ties a child row to the parent it belongs to.
Types and CASTA column's declared type is a promise about storage, not always a wall.
INSERT rowsINSERT adds rows; one VALUES list per row.
INSERT from a queryThe rows to insert can come from a query instead of a VALUES list.
UPDATE with a filterUPDATE rewrites columns in the rows WHERE matches — all of them if you forget.
DELETE rowsDELETE removes whole rows; there is no undo outside a transaction.
RETURNING the rows you changedRETURNING hands back the rows a write touched, including generated ids.

Encore

schema_setup.sqlA complete migration: drop, create, index and seed a small store schema.
library_schema.sqlA small schema from nothing: three tables, the rules between them, and two reports.