SQLRepPracticeMock interviewGuidesPlaygroundLearning path
Interview questionsPostgres errorsCheat sheetsTermsPrivacyRefundssupport@sqlrep.com
© SQLRep · Practice SQL online, right in your browser.

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 →

Practice every concept interactively at sqlrep.com