Field Guide — Learn SQL in a Garden

Colors the bed knows

Every chapter that draws flowers uses the same palette. The color column is plain text — any string will store, but only these twelve names render distinctly in the bed and in the gardener’s log. Anything else still stores fine, but draws as pink.

pink red white yellow purple blue orange magenta cream peach coral lavender

Comparison operators

All six work inside WHERE and HAVING. They’re identical in shape across all chapters.

=equals — same value !=not equal (preferred spelling) <>also not equal (SQL-traditional spelling) <less than <=at most >greater than >=at least

Combining conditions

Conditions can be combined with the logical words below. Use parentheses to group when in doubt.

ANDboth sides must be true ORat least one side must be true NOTinverts the condition

Special predicates

LIKE 'M%'text matches a pattern. % matches any run of characters, _ matches one character. IS NULLvalue is absent. = NULL never matches — use IS NULL. IS NOT NULLvalue is present. IN (subquery)value appears in the rows returned by a subquery (Chapter VI).

Clause order

Every SELECT follows the same fixed clause order. You can leave parts out, but you cannot shuffle them.

SELECTwhich columns or aggregates to return FROMwhich table(s) to read JOIN ... ONhow to pair rows across tables (Chapter IV) WHEREwhich rows to keep before grouping GROUP BYwhich column(s) to bucket rows by (Chapter V) HAVINGwhich groups to keep after aggregating ORDER BYhow to arrange the result LIMIThow many rows to keep

Mutation shapes

The three writing verbs from Chapter II, side by side.

INSERT

INSERT INTO table (cols) VALUES (vals);add a row with explicit column list (preferred — survives the schema gaining a column) INSERT INTO table VALUES (vals);shortcut: every column, in defined order INSERT INTO table ... VALUES (...), (...);plant multiple rows in one statement

UPDATE

UPDATE table SET col = val WHERE cond;change one column on matching rows UPDATE table SET a = 1, b = 2 WHERE cond;change several columns at once SET col = col + 1the right side can be an expression that reads the current row

DELETE

DELETE FROM table WHERE cond;remove matching rows DELETE FROM table;no WHERE — every row is removed. Pause before pressing Run.

Building a bed

Statements that shape the beds themselves, not the flowers within them (Chapter III).

CREATE and DROP

CREATE TABLE name (col type, ...);draft a new bed — name each column and its type DROP TABLE name;remove the bed and everything in it DROP TABLE IF EXISTS name;safer — no error if the bed isn’t there

Data types

INTEGERwhole numbers REALdecimal numbers (heights, diameters, prices) TEXTstrings of any length, always quoted with '...' BLOBbinary data — rarely needed in this course

Column constraints

PRIMARY KEYthe row’s identifier — unique and never NULL UNIQUEno duplicates allowed; NULL is still allowed NOT NULLa value must be provided when a row is inserted DEFAULT valfills in a value when INSERT omits the column REFERENCES other_table(col)foreign key — this column must match a row over there

ALTER TABLE

ALTER TABLE t ADD COLUMN col type;grow the bed with a new column ALTER TABLE t RENAME COLUMN a TO b;rename an existing column ALTER TABLE t RENAME TO new_name;rename the whole table

Join shapes

Four ways to pair rows across two tables (Chapter IV). Every join uses an ON clause to name the shared column; the types differ in which unmatched rows survive.

INNER JOINonly rows that match on both sides. Rows without a partner drop. LEFT JOINevery row from the left table, plus matches from the right. Unmatched right sides come back as NULL. RIGHT JOINmirror of LEFT — every row from the right, matched or not. FULL OUTER JOINevery row from both tables. Any row without a partner shows NULL on the other side.

The shape

SELECT ... FROM a JOIN b ON a.col = b.col;INNER is the default; the word can be omitted SELECT ... FROM a LEFT JOIN b ON ...;substitute the join type as needed

Aggregate functions

Functions that take many rows in and produce one number out (Chapter V). Used alone for a single answer, or paired with GROUP BY for a per-basket answer.

COUNT(*)how many rows — counts every row, including those with NULLs COUNT(col)how many rows have a non-NULL value in that column SUM(col)add up a numeric column AVG(col)average of a numeric column — NULLs are skipped MIN(col)smallest value — works on numbers, text, and dates MAX(col)largest value

With GROUP BY

Every column in SELECT must either be the thing you grouped by, or an aggregate over the group.

SELECT color, COUNT(*) FROM t GROUP BY color;one row per color, each with its count ... HAVING COUNT(*) > 1;filter groups after aggregating — WHERE filters before ORDER BY COUNT(*) DESCany aggregate can be used in ORDER BY, not just plain columns

Subquery patterns

A query nested inside another query — the gardener gathers a small basket first, then uses it inside the larger question (Chapter VI).

WHERE col IN (SELECT ...)value must appear in the results of the inner query WHERE col NOT IN (SELECT ...)value must not appear in the inner results WHERE EXISTS (SELECT ...)true if the inner query returns at least one row WHERE NOT EXISTS (SELECT ...)true if the inner query returns no rows

Correlated subquery

The inner query references a column from the outer query, so it re-runs once for each row of the outer.

... WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);the inner sees the outer’s row via a.id