Querydrill / Cheat sheet
SQL cheat sheet
A hundred and ten entries in ten groups, written in standard SQL that runs on PostgreSQL, MySQL 8, SQL Server and SQLite with the dialect differences marked. Most entries have a drill that checks your query on real tables. Print the page or get the PDF by email.
The ones worth learning first
| What | How |
|---|
| Filter rows, then sort, then limit | SELECT * FROM orders WHERE status = 'paid' ORDER BY total DESC LIMIT 10; |
| Count per group | SELECT dept, COUNT(*) FROM employees GROUP BY dept; |
| Filter on an aggregate | ... GROUP BY dept HAVING COUNT(*) > 5 |
| Join two tables | SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id; |
| Rows with no match | SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL; |
| Rank within a group | RANK() OVER (PARTITION BY dept ORDER BY salary DESC) |
| Running total | SUM(amount) OVER (ORDER BY order_date) |
| Name a subquery | WITH t AS (SELECT ...) SELECT * FROM t; |
| Second highest salary | SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); |
| Conditional value | CASE WHEN total > 100 THEN 'big' ELSE 'small' END |
Select and filter
| Pick columns | SELECT name, email FROM customers; drill |
| Rename a column | SELECT total * 1.1 AS total_with_tax FROM orders; |
| Filter rows | WHERE country = 'US' AND total > 100 drill |
| Either condition | WHERE status = 'paid' OR status = 'refunded' |
| In a list | WHERE country IN ('US', 'CA', 'MX') drill |
| RangeBoth ends included. | WHERE total BETWEEN 10 AND 50 |
| Pattern% any run of characters, _ one character. ILIKE ignores case in PostgreSQL. | WHERE email LIKE '%@gmail.com' drill |
| Missing value= NULL is never true. Use IS NULL / IS NOT NULL. | WHERE manager_id IS NULL drill |
| Not | WHERE NOT (status = 'paid') or status <> 'paid' |
| Distinct values | SELECT DISTINCT country FROM customers; drill |
| Sort | ORDER BY total DESC, created_at ASC drill |
| Nulls lastPostgreSQL and SQLite. MySQL: ORDER BY score IS NULL, score DESC. | ORDER BY score DESC NULLS LAST |
| Top nSQL Server: SELECT TOP 3 ... ; Oracle: FETCH FIRST 3 ROWS ONLY. | ORDER BY total DESC LIMIT 3 drill |
| Skip rows (paging) | LIMIT 20 OFFSET 40 |
| Calculated column | SELECT price * (1 - discount) AS net FROM items; drill |
| Conditional column | CASE WHEN total >= 100 THEN 'big' WHEN total >= 50 THEN 'mid' ELSE 'small' END AS size drill |
Aggregates
| Count rows | SELECT COUNT(*) FROM orders WHERE status = 'paid'; drill |
| Count non-null | COUNT(shipped_at) |
| Count distinct | COUNT(DISTINCT customer_id) drill |
| Sum, average, min, max | SUM(total), AVG(total), MIN(total), MAX(total) drill |
| Per group | SELECT dept, COUNT(*) FROM employees GROUP BY dept; drill |
| Two grouping columns | GROUP BY country, status drill |
| Filter groupsWHERE filters rows before grouping; HAVING filters groups after. | GROUP BY dept HAVING AVG(salary) > 60000 drill |
| Average per group | SELECT dept, AVG(salary) FROM employees GROUP BY dept; drill |
| Conditional countPostgreSQL: COUNT(*) FILTER (WHERE status = 'paid'). | SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) |
| Average order value | SUM(total) / COUNT(DISTINCT order_id) drill |
| Rows above the average | WHERE salary > (SELECT AVG(salary) FROM employees) drill |
| Pivot with CASE | SUM(CASE WHEN status = 'paid' THEN total END) AS paid, SUM(CASE WHEN status = 'refunded' THEN total END) AS refunded drill |
| Round | ROUND(AVG(total), 2) |
| Integer division trapTwo integers divide to an integer in most databases. | paid * 1.0 / total |
Joins
| Inner join | FROM orders o JOIN customers c ON c.id = o.customer_id drill |
| Left join (keep all left rows) | FROM customers c LEFT JOIN orders o ON o.customer_id = c.id drill |
| Rows with no match | LEFT JOIN ... WHERE o.id IS NULL drill |
| Three tables | FROM orders o JOIN order_items i ON i.order_id = o.id JOIN products p ON p.id = i.product_id drill |
| Self join (employee and manager) | FROM employees e JOIN employees m ON m.id = e.manager_id drill |
| Join then aggregate | SELECT c.country, SUM(o.total) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.country drill |
| Count per parent including zeroCOUNT(e.id), not COUNT(*), so empty departments show 0. | SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON e.dept_id = d.id GROUP BY d.name drill |
| Products never sold | SELECT p.* FROM products p LEFT JOIN order_items i ON i.product_id = p.id WHERE i.id IS NULL drill |
| Pairs in the same group | FROM customers a JOIN customers b ON a.country = b.country AND a.id < b.id drill |
| Cross join (every combination) | FROM sizes CROSS JOIN colors |
| Full outer joinNot in MySQL; use LEFT JOIN UNION RIGHT JOIN. | FULL OUTER JOIN |
| Join on several columns | ON a.year = b.year AND a.month = b.month |
| Filter the right side before joiningPutting it in WHERE turns a left join into an inner join. | LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid' |
Subqueries and CTEs
| Scalar subquery | WHERE salary > (SELECT AVG(salary) FROM employees) |
| IN subquery | WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US') |
| EXISTSUsually faster than IN on large tables; NOT EXISTS is null-safe where NOT IN is not. | WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) |
| Derived table | FROM (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) t |
| CTE | WITH dept_avg AS (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) SELECT ... FROM dept_avg drill |
| Several CTEs | WITH a AS (...), b AS (...) SELECT ... FROM a JOIN b ON ... |
| Compare a row to its group | SELECT e.* FROM employees e JOIN (SELECT dept, AVG(salary) a FROM employees GROUP BY dept) d ON d.dept = e.dept WHERE e.salary > d.a drill |
| Recursive CTESQL Server and Oracle omit the word RECURSIVE. | WITH RECURSIVE r AS (SELECT ... UNION ALL SELECT ... FROM r JOIN ...) SELECT * FROM r |
| Union (deduplicates) | SELECT ... UNION SELECT ... |
| Union all (keeps duplicates, faster) | SELECT ... UNION ALL SELECT ... |
| In the second set but not the firstOracle and old MySQL: MINUS or NOT EXISTS. | SELECT ... EXCEPT SELECT ... |
Window functions
| Row number | ROW_NUMBER() OVER (ORDER BY created_at) |
| Rank with gaps | RANK() OVER (PARTITION BY dept ORDER BY salary DESC) drill |
| Rank without gaps | DENSE_RANK() OVER (ORDER BY score DESC) drill |
| Top n per group | WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY cat ORDER BY revenue DESC) rn FROM t) SELECT * FROM r WHERE rn <= 2 drill |
| Running total | SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) drill |
| Previous row | LAG(amount) OVER (ORDER BY month) drill |
| Next row | LEAD(amount) OVER (ORDER BY month) |
| Month over month change | amount - LAG(amount) OVER (ORDER BY month) drill |
| Share of total | amount * 1.0 / SUM(amount) OVER () drill |
| Group total next to each row | SUM(amount) OVER (PARTITION BY dept) |
| First row per customerWindow functions are not allowed in WHERE; wrap in a CTE. | ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) = 1 drill |
| Moving average (3 rows) | AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) |
| Nth highest | SELECT salary FROM (SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employees) t WHERE r = 3 drill |
| Second highest without windows | SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) drill |
| Percentile bucket | NTILE(4) OVER (ORDER BY total) |
Dates
| TodayMySQL also CURDATE(); SQL Server GETDATE() for date and time. | CURRENT_DATE |
| Extract the yearMySQL/SQL Server: YEAR(hired_at). SQLite: strftime('%Y', hired_at). | EXTRACT(YEAR FROM hired_at) drill |
| Group by monthMySQL: DATE_FORMAT(created_at, '%Y-%m'). SQLite: strftime('%Y-%m', created_at). | DATE_TRUNC('month', created_at) drill |
| Days betweenPostgreSQL. MySQL: DATEDIFF(end, start). SQL Server: DATEDIFF(day, start, end). SQLite: julianday(end) - julianday(start). | end_date - start_date |
| Add daysMySQL: DATE_ADD(created_at, INTERVAL 30 DAY). SQL Server: DATEADD(day, 30, created_at). | created_at + INTERVAL '30 days' |
| Last 30 days | WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' |
| Rows in a monthHalf-open ranges use the index; functions on the column do not. | WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01' |
| Active months per customer | COUNT(DISTINCT DATE_TRUNC('month', created_at)) drill |
| Age in yearsPostgreSQL. | EXTRACT(YEAR FROM AGE(birth_date)) |
Strings and nulls
| Join stringsMySQL and SQL Server: CONCAT(first_name, ' ', last_name). | first_name || ' ' || last_name |
| Upper, lower, lengthSQL Server: LEN. | UPPER(name), LOWER(name), LENGTH(name) |
| Trim spaces | TRIM(name) |
| Substring | SUBSTRING(email FROM 1 FOR 3) or SUBSTR(email, 1, 3) |
| Position of a characterMySQL: LOCATE or INSTR. SQL Server: CHARINDEX. | POSITION('@' IN email) |
| Replace | REPLACE(phone, '-', '') |
| Default for null | COALESCE(nickname, first_name, 'unknown') |
| Null if equalamount / NULLIF(total, 0) avoids divide by zero. | NULLIF(total, 0) |
| Cast:: is PostgreSQL only. | CAST(total AS DECIMAL(10,2)) or total::numeric |
| Starts with | WHERE name LIKE 'A%' |
Data cleaning patterns
| Find duplicates | SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1 drill |
| Keep one of each duplicate | WITH r AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users) SELECT * FROM r WHERE rn = 1 |
| Normalize country codes | UPPER(TRIM(country)) drill |
| Revenue by month, paid only | SELECT DATE_TRUNC('month', paid_at) m, SUM(amount) FROM payments WHERE status = 'paid' GROUP BY m ORDER BY m drill |
| First paid order per customer | SELECT customer_id, MIN(paid_at) FROM payments GROUP BY customer_id drill |
| Customers on each plan | SELECT plan, COUNT(*) FROM subscriptions WHERE status = 'active' GROUP BY plan drill |
| Customers who paid at least once | SELECT COUNT(DISTINCT customer_id) FROM payments WHERE status = 'paid' drill |
| Delete duplicates (PostgreSQL) | DELETE FROM users a USING users b WHERE a.id > b.id AND a.email = b.email |
Changing data
| Insert | INSERT INTO customers (name, email) VALUES ('Ana', 'ana@x.com'); |
| Insert from a query | INSERT INTO archive SELECT * FROM orders WHERE created_at < '2025-01-01'; |
| UpdateAlways write the WHERE before the SET. | UPDATE orders SET status = 'shipped' WHERE id = 42; |
| Delete | DELETE FROM orders WHERE status = 'cancelled'; |
| Upsert (PostgreSQL, SQLite)MySQL: ON DUPLICATE KEY UPDATE. SQL Server: MERGE. | INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name |
| Transaction | BEGIN; ... COMMIT; (ROLLBACK to undo) |
| Create a table | CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); |
| Add a column | ALTER TABLE t ADD COLUMN email TEXT; |
| Index | CREATE INDEX idx_orders_customer ON orders (customer_id); |
| See the planEXPLAIN ANALYZE runs it and shows real timings in PostgreSQL. | EXPLAIN SELECT ... |
Order of execution
| The order SQL runs inWhich is why a SELECT alias cannot be used in WHERE, and window functions cannot be used in WHERE. | FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT |
| Alias in ORDER BYAllowed: ORDER BY runs after SELECT. | SELECT total * 1.1 AS t FROM orders ORDER BY t |
| Alias in WHERE | Not allowed. Repeat the expression or use a CTE. |
| GROUP BY and SELECT | Every selected column is either in GROUP BY or inside an aggregate. |
querydrill.app: practice every one of these with instant checking. 110 entries.
Questions
Which SQL should I learn for interviews?
Joins, GROUP BY with HAVING, subqueries, CTEs and the window functions ROW_NUMBER, RANK, LAG and SUM OVER. Most interview questions are one of about fifteen patterns: top n per group, second highest, rows with no match, running total, month over month, duplicates, first event per user.
Which dialect is this cheat sheet in?
Standard SQL as PostgreSQL runs it, with the MySQL, SQL Server and SQLite differences marked in the notes. The structure is the same everywhere; dates and string functions are where the dialects differ.
WHERE or HAVING?
WHERE filters rows before they are grouped; HAVING filters groups after aggregation. A condition on a raw column goes in WHERE (faster), a condition on COUNT or SUM goes in HAVING.
Why does my LEFT JOIN behave like an INNER JOIN?
Because a condition on the right-hand table is in WHERE, which throws out the rows where that table is NULL. Move the condition into the ON clause.
Why can I not use a window function in WHERE?
Window functions are computed after WHERE runs. Put the query in a CTE or a derived table, then filter the result.
A cheat sheet does not make them stick
Doing them with instant checking does. The free exercises need no account.
Start practicing
Querydrill