SQL Cheatsheet
Standard SQL (ANSI) with Postgres-friendly syntax. Covers SELECT, JOIN, aggregates, and a few DBA essentials.
1 credit
Reading data
5 itemsColumns + filter
SELECT id, name FROM users WHERE active = true;Order + limit
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;Distinct
SELECT DISTINCT country FROM users;IN / BETWEEN / LIKE
WHERE id IN (1,2,3) / created_at BETWEEN a AND b / email LIKE '%@x.com'Null-safe compare
WHERE email IS NULL / col IS DISTINCT FROM valJoins
5 itemsInner (intersection)
SELECT * FROM a JOIN b ON a.id = b.a_id;Left (keep all on left)
a LEFT JOIN b ON a.id = b.a_idFull outer
a FULL OUTER JOIN b ON ...Self join
u1 JOIN users u2 ON u1.manager_id = u2.idUSING (matching cols)
a JOIN b USING (user_id)Aggregates & grouping
4 itemsCount / sum / avg
COUNT(*), SUM(price), AVG(rating)Group + filter group
GROUP BY country HAVING COUNT(*) > 10Conditional count
COUNT(*) FILTER (WHERE status='paid')Distinct count
COUNT(DISTINCT user_id)Window functions
sql
SELECT id, user_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn, SUM(amount) OVER (PARTITION BY user_id) AS user_total FROM orders;
Window funcs compute across rows without collapsing them — great for ranking, running totals, lead/lag.
Writing data
5 itemsInsert
INSERT INTO users (name, email) VALUES ('Alice', 'a@x');Update
UPDATE users SET active = false WHERE id = 1;Delete
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';Upsert (Postgres)
INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;Transaction
BEGIN; ... COMMIT; (ROLLBACK on error)