SQL Cheat Sheet

query skeleton

SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column1
HAVING group_condition
ORDER BY column1 [ASC|DESC]
LIMIT n OFFSET m;

Clauses execute in a different order than they're written: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why you can't reference a SELECT alias in WHERE, but you usually can in ORDER BY.

common commands

Command	Purpose
`CREATE TABLE t (id SERIAL PRIMARY KEY, name TEXT NOT NULL);`	define a new table
`ALTER TABLE t ADD COLUMN col TYPE;`	add a column
`DROP TABLE t;`	delete a table and its data
`INSERT INTO t (name) VALUES ('x');`	add a row
`UPDATE t SET name = 'y' WHERE id = 1;`	modify rows
`DELETE FROM t WHERE id = 1;`	remove rows
`TRUNCATE t;`	remove all rows, fast, resets identity columns

joins at a glance

Join	Keeps
`INNER JOIN`	rows with a match in both tables
`LEFT JOIN`	all left rows, plus matches from the right (NULLs where no match)
`RIGHT JOIN`	all right rows, plus matches from the left
`FULL JOIN`	all rows from both sides
`CROSS JOIN`	every combination of rows (Cartesian product)

Full treatment with examples: Joins Explained.

common PostgreSQL data types

Type	Use for
`integer` / `bigint`	whole numbers
`numeric(p, s)`	exact decimals — money, anything where float rounding is unacceptable
`text` / `varchar(n)`	strings — `text` has no length limit and no performance penalty vs. `varchar` in Postgres
`boolean`	true/false
`timestamp` / `timestamptz`	date+time — prefer `timestamptz` unless you have a specific reason not to
`uuid`	universally unique identifiers
`jsonb`	semi-structured data, indexable — see JSONB
`array` (e.g. `text[]`)	a column that holds a list

operators & NULL handling

Expression	Meaning
`=, <>, <, >, <=, >=`	comparison
`AND, OR, NOT`	logical
`BETWEEN a AND b`	inclusive range
`IN (a, b, c)`	membership
`LIKE 'a%'` / `ILIKE`	pattern match (case-sensitive / case-insensitive)
`IS NULL` / `IS NOT NULL`	NULL check — `= NULL` is always unknown, never true
`COALESCE(a, b, c)`	first non-NULL value

aggregate functions

COUNT(*), COUNT(col), SUM(col), AVG(col), MIN(col), MAX(col), ARRAY_AGG(col), STRING_AGG(col, ', ')

Full treatment: Aggregation & GROUP BY.

where to go from here

SQL Fundamentals — the same material, with explanations.
PostgreSQL Fundamentals — getting a real database running.