Cheat sheets · Module 1
SQL Basics cheat sheet
Pull exactly the rows and columns you need from a table.
SELECT * · FROM
A SQL query is a question you ask the database. FROM names the table to look in, and SELECT says which columns you want back. The star * is shorthand for every column, so SELECT * FROM some_table returns the whole table: every column, every row.
SELECT * FROM pets;Practice: Your first query →
SELECT · ORDER BY
SELECT picks which columns come back and FROM names the table. ORDER BY sorts the rows: ascending (A→Z, oldest→newest) by default, or add DESC for the reverse.
SELECT title, price FROM products ORDER BY price DESC;Practice: Customer directory →
WHERE · AND
WHERE keeps only the rows where a condition is true. Combine conditions with AND (both must hold) or OR (either can). Text values go in single quotes: 'Canada'.
SELECT title FROM products WHERE category = 'books' AND in_stock = 1;Practice: Active Canadian customers →
OR · NOT · parentheses
OR keeps a row when either condition is true, and NOT flips a condition: NOT status = 'x' keeps everything except 'x'. When you mix them, SQL does AND before OR (like × before + in math), so a OR b AND c means a OR (b AND c). Put parentheses around the OR part to say what you mean: (a OR b) AND c.
SELECT title, genre FROM books WHERE (genre = 'sci-fi' OR genre = 'fantasy') AND NOT format = 'audiobook';Practice: North American customers who stayed →
LIKE · wildcards
LIKE matches text against a pattern. % stands for any run of characters (including none) and _ for exactly one. 'A%' starts with A, '%son' ends with son, '%data%' contains data.
SELECT name FROM employees WHERE email LIKE '%@acme.com';Practice: Customers with a .ca email →
IN · BETWEEN
IN checks a value against a list: status IN ('a', 'b') is shorthand for status = 'a' OR status = 'b'. BETWEEN x AND y checks a range and includes both ends, which makes it handy for date ranges.
SELECT id, priority, opened_on
FROM tickets
WHERE priority IN ('high', 'urgent')
AND opened_on BETWEEN '2024-03-01' AND '2024-03-31';Practice: First-week shipments →ORDER BY · LIMIT
LIMIT n keeps only the first n rows of the result. It runs after ORDER BY, so 'sort, then cut' gives you a top-N list.
SELECT title, views FROM videos ORDER BY views DESC LIMIT 5;Practice: Three biggest orders →
SELECT DISTINCT
SELECT DISTINCT removes duplicate rows from the result, so each value comes back once. It applies to the whole row: SELECT DISTINCT a, b keeps one row per unique (a, b) pair, not per a.
SELECT DISTINCT city FROM stores WHERE is_open = true ORDER BY city;Practice: Countries with active customers →
AS aliases · calculated columns
SELECT can do math: salary / 12 or price * 1.2 makes a new column, worked out for every row. AS gives any column a name of your choice (salary AS annual_salary), and that name becomes the column header. One catch: whole number divided by whole number drops the decimals in Postgres (7 / 2 is 3), so write 12.0 to keep them.
SELECT title AS product, price AS price_before_tax, ROUND(price * 1.2, 2) AS price_with_tax FROM products;Practice: Monthly pay →
CASE WHEN
CASE WHEN turns values into labels: CASE WHEN cond1 THEN 'a' WHEN cond2 THEN 'b' ELSE 'c' END. Postgres checks the WHENs top to bottom and uses the first one that's true, so put the strictest condition first. Without ELSE, unmatched rows get NULL.
SELECT title, price,
CASE WHEN price >= 100 THEN 'premium'
WHEN price >= 20 THEN 'standard'
ELSE 'budget' END AS tier
FROM products;Practice: Salary bands →NULL · COALESCE
NULL means 'no value'. COALESCE(a, b) returns the first argument that isn't NULL, so COALESCE(col, fallback) swaps NULLs for a default. All its arguments must share one type: to fall back to text from an integer column, cast first with col::text.
SELECT name, COALESCE(phone, 'no phone') AS contact, COALESCE(discount_pct::text, 'none') AS discount FROM suppliers;Practice: Filling in missing managers →
EXTRACT · day of week
EXTRACT(field FROM date) pulls one part out of a date: YEAR, MONTH, DAY, or the weekday. Watch the weekday numbering: DOW runs Sunday 0 to Saturday 6, while ISODOW runs Monday 1 to Sunday 7, so weekends are ISODOW IN (6, 7).
SELECT id, shipped_on FROM shipments WHERE EXTRACT(MONTH FROM shipped_on) = 12 AND EXTRACT(ISODOW FROM shipped_on) = 1;Practice: Weekend logins →