SQL cheat sheet
The SQL you need for interviews and daily work, with runnable examples and their real results. SQLite syntax, with notes where PostgreSQL, MySQL and SQL Server differ.
The sample database
All examples use three small tables with made-up data. Note the traps built into them: one employee has no department, two employees have the same salary, one department has no employees, and one sale has no amount.
departments(id, name, budget)
employees(id, full_name, department_id, manager_id, salary, hired_on)
sales(id, sold_on, region, amount)departments
| id | name | budget |
|---|---|---|
| 1 | Sales | 30000 |
| 2 | Engineering | 80000 |
| 3 | Support | 20000 |
| 4 | Finance | 15000 |
employees
| id | full_name | department_id | manager_id | salary | hired_on |
|---|---|---|---|---|---|
| 1 | Mara Lind | 2 | NULL | 9200 | 2019-03-04 |
| 2 | Joel Park | 2 | 1 | 7600 | 2020-06-15 |
| 3 | Ines Cole | 2 | 1 | 7600 | 2021-01-11 |
| 4 | Tomas Reyes | 1 | 5 | 6900 | 2018-09-01 |
| 5 | Nadia Voss | 1 | NULL | 6400 | 2017-05-22 |
| 6 | Owen Hart | 1 | 5 | 4300 | 2022-02-14 |
| 7 | Pia Novak | 3 | 8 | 3900 | 2023-04-03 |
| 8 | Sven Ito | 3 | NULL | 5200 | 2020-11-30 |
| 9 | Lea Brandt | NULL | NULL | 4800 | 2024-01-08 |
| 10 | Ravi Shah | 2 | 1 | 6100 | 2022-08-19 |
sales
| id | sold_on | region | amount |
|---|---|---|---|
| 1 | 2026-01-01 | North | 120 |
| 2 | 2026-01-02 | South | 80 |
| 3 | 2026-01-03 | North | 150 |
| 4 | 2026-01-04 | South | NULL |
| 5 | 2026-01-05 | North | 90 |
| 6 | 2026-01-06 | South | 200 |
| 7 | 2026-01-07 | North | 110 |
Clause order
You write a query in one order, but the database thinks about it in another. Knowing both explains most "why does this not work" questions.
| You write | The database evaluates |
|---|---|
| SELECT | 1. FROM and JOIN: build the rows |
| FROM / JOIN ... ON | 2. WHERE: filter rows |
| WHERE | 3. GROUP BY: make groups |
| GROUP BY | 4. HAVING: filter groups |
| HAVING | 5. Window functions |
| ORDER BY | 6. SELECT: compute columns, then DISTINCT |
| LIMIT / OFFSET | 7. ORDER BY, then LIMIT |
So WHERE cannot see an aggregate (groups do not exist yet) and cannot filter on a window function. Use HAVING for the first and a subquery or CTE for the second.
SELECT, WHERE, ORDER BY, LIMIT
Pick columns, filter rows, sort, cut the list. Add a second sort key to make ties predictable.
SELECT full_name, salary
FROM employees
WHERE salary >= 6000
ORDER BY salary DESC, full_name
LIMIT 3| full_name | salary |
|---|---|
| Mara Lind | 9200 |
| Ines Cole | 7600 |
| Joel Park | 7600 |
DISTINCT removes duplicate rows from the result. A NULL counts as one value, and here it sorts first.
SELECT DISTINCT department_id
FROM employees
ORDER BY department_id| department_id |
|---|
| NULL |
| 1 |
| 2 |
| 3 |
Without an ORDER BY, the order of rows is not guaranteed, even if it looks stable today.
Filtering: IN, BETWEEN, LIKE, dates
ANDbinds tighter thanOR. Use parentheses when you mix them.BETWEEN a AND bincludes both ends.LIKEuses%(any text) and_(one character). In SQLite it ignores case for ASCII letters by default.- Dates stored as ISO text (
YYYY-MM-DD) compare and sort correctly as text.
SELECT full_name, salary
FROM employees
WHERE salary BETWEEN 6100 AND 7600
ORDER BY salary, full_name| full_name | salary |
|---|---|
| Ravi Shah | 6100 |
| Nadia Voss | 6400 |
| Tomas Reyes | 6900 |
| Ines Cole | 7600 |
| Joel Park | 7600 |
SELECT full_name, hired_on
FROM employees
WHERE hired_on >= '2022-01-01' AND department_id IN (1, 2, 3)
ORDER BY hired_on| full_name | hired_on |
|---|---|
| Owen Hart | 2022-02-14 |
| Ravi Shah | 2022-08-19 |
| Pia Novak | 2023-04-03 |
NULL rules
NULL means unknown, not zero and not an empty string. Any comparison with NULL is unknown, and WHERE keeps only rows where the condition is true.
SELECT NULL = NULL AS equal, NULL IS NULL AS is_null, 1 + NULL AS plus_null, COALESCE(NULL, 5) AS fallback| equal | is_null | plus_null | fallback |
|---|---|---|---|
| NULL | 1 | NULL | 5 |
SELECT full_name
FROM employees
WHERE department_id IS NULL| full_name |
|---|
| Lea Brandt |
- Use
IS NULLandIS NOT NULL, never= NULL. - Aggregates such as
SUM,AVGandCOUNT(column)skip NULLs.COUNT(*)counts rows. NOT INreturns nothing if the list contains a NULL. See the NOT IN trap.
Aggregates, GROUP BY, HAVING
GROUP BY collapses rows into one row per group. WHERE filters rows before grouping; HAVING filters groups after.
SELECT COUNT(*) AS all_rows,
COUNT(department_id) AS with_department,
COUNT(DISTINCT department_id) AS departments,
MIN(salary) AS lowest, MAX(salary) AS highest,
ROUND(AVG(salary), 1) AS average
FROM employees| all_rows | with_department | departments | lowest | highest | average |
|---|---|---|---|---|---|
| 10 | 9 | 3 | 3900 | 9200 | 6200 |
SELECT department_id, COUNT(*) AS headcount, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 2
ORDER BY department_id| department_id | headcount | avg_salary |
|---|---|---|
| 1 | 3 | 5867 |
| 2 | 4 | 7625 |
| 3 | 2 | 4550 |
Every selected column must be in GROUP BY or inside an aggregate. SQLite is lenient about this; PostgreSQL and SQL Server are not. Write portable queries.
CASE, COALESCE, NULLIF, division
SELECT full_name,
CASE WHEN salary >= 7000 THEN 'high'
WHEN salary >= 5000 THEN 'mid'
ELSE 'low' END AS band
FROM employees
ORDER BY id
LIMIT 5| full_name | band |
|---|---|
| Mara Lind | high |
| Joel Park | high |
| Ines Cole | high |
| Tomas Reyes | mid |
| Nadia Voss | mid |
COALESCE(a, b, ...) returns the first non-NULL value. NULLIF(a, b) returns NULL when the two are equal, which is the standard way to avoid dividing by zero.
SELECT 7 / 2 AS int_div, 7 / 2.0 AS real_div, 7 % 2 AS remainder, 10 / NULLIF(0, 0) AS safe_div| int_div | real_div | remainder | safe_div |
|---|---|---|---|
| 3 | 3.5 | 1 | NULL |
Integer division is a classic trap: 7 / 2 is 3 when both sides are integers in SQLite, PostgreSQL and SQL Server, but 3.5 in MySQL.
JOINs
| Type | Keeps |
|---|---|
| INNER JOIN | Only rows with a match on both sides |
| LEFT JOIN | All rows from the left table; NULLs where the right has no match |
| RIGHT / FULL JOIN | Mirror image / both sides. In SQLite only from version 3.39; rewrite with LEFT JOIN on older versions |
| CROSS JOIN | Every combination of rows (rows of A times rows of B) |
Inner join: Lea has no department, so she disappears (9 rows).
SELECT e.full_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY e.id| full_name | department |
|---|---|
| Mara Lind | Engineering |
| Joel Park | Engineering |
| Ines Cole | Engineering |
| Tomas Reyes | Sales |
| Nadia Voss | Sales |
| Owen Hart | Sales |
| Pia Novak | Support |
| Sven Ito | Support |
| Ravi Shah | Engineering |
Left join: the same query keeps Lea with a NULL department (10 rows).
SELECT e.full_name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
ORDER BY e.id| full_name | department |
|---|---|
| Mara Lind | Engineering |
| Joel Park | Engineering |
| Ines Cole | Engineering |
| Tomas Reyes | Sales |
| Nadia Voss | Sales |
| Owen Hart | Sales |
| Pia Novak | Support |
| Sven Ito | Support |
| Lea Brandt | NULL |
| Ravi Shah | Engineering |
Anti-join: rows with no match. Which departments have no employees?
SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL| name |
|---|
| Finance |
Self join: join a table to itself with two aliases.
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
ORDER BY e.id| employee | manager |
|---|---|
| Joel Park | Mara Lind |
| Ines Cole | Mara Lind |
| Tomas Reyes | Nadia Voss |
| Owen Hart | Nadia Voss |
| Pia Novak | Sven Ito |
| Ravi Shah | Mara Lind |
More on JOINs: JOIN interview questions.
UNION, INTERSECT, EXCEPT
Both queries must return the same number of columns with compatible types. UNION removes duplicates; UNION ALL keeps them and is cheaper.
SELECT region FROM sales WHERE amount > 100
UNION
SELECT region FROM sales WHERE amount < 100
ORDER BY region| region |
|---|
| North |
| South |
EXCEPT returns rows of the first query that are not in the second. Unlike NOT IN, it treats NULLs as equal, so it is safe here even though employees.department_id contains a NULL.
SELECT id FROM departments
EXCEPT
SELECT department_id FROM employees| id |
|---|
| 4 |
Subqueries, EXISTS, CTEs
SELECT full_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC, full_name| full_name | salary |
|---|---|
| Mara Lind | 9200 |
| Ines Cole | 7600 |
| Joel Park | 7600 |
| Tomas Reyes | 6900 |
| Nadia Voss | 6400 |
EXISTS asks whether a related row exists and stops at the first one.
SELECT d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e
WHERE e.department_id = d.id AND e.salary > 9000)| name |
|---|
| Engineering |
A CTE (WITH) names a subquery so the main query reads top to bottom.
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.full_name, e.salary, ROUND(a.avg_salary) AS dept_average
FROM employees e
JOIN dept_avg a ON a.department_id = e.department_id
WHERE e.salary > a.avg_salary
ORDER BY e.full_name| full_name | salary | dept_average |
|---|---|---|
| Mara Lind | 9200 | 7625 |
| Nadia Voss | 6400 | 5867 |
| Sven Ito | 5200 | 4550 |
| Tomas Reyes | 6900 | 5867 |
Window functions
A window function computes a value across related rows without collapsing them. The pattern is function() OVER (PARTITION BY ... ORDER BY ... frame).
ROW_NUMBER()numbers rows 1, 2, 3 with no ties.RANK()gives ties the same number and skips the next ones.DENSE_RANK()does not skip.LAG(x)/LEAD(x)read the previous or next row.SUM(x) OVER (...)builds running totals.- You cannot filter on a window function in
WHERE; wrap the query in a CTE or subquery.
SELECT full_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM employees
WHERE department_id = 2
ORDER BY rank_in_dept, full_name| full_name | salary | rank_in_dept |
|---|---|---|
| Mara Lind | 9200 | 1 |
| Ines Cole | 7600 | 2 |
| Joel Park | 7600 | 2 |
| Ravi Shah | 6100 | 4 |
A running total. The explicit ROWS frame says exactly which rows are summed. Note that the NULL amount does not change the total.
SELECT sold_on, amount,
SUM(amount) OVER (ORDER BY sold_on
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales
ORDER BY sold_on| sold_on | amount | running_total |
|---|---|---|
| 2026-01-01 | 120 | 120 |
| 2026-01-02 | 80 | 200 |
| 2026-01-03 | 150 | 350 |
| 2026-01-04 | NULL | 350 |
| 2026-01-05 | 90 | 440 |
| 2026-01-06 | 200 | 640 |
| 2026-01-07 | 110 | 750 |
More: window function interview questions.
Strings and dates in SQLite
SQLite has no date type. Store dates as ISO text and use date(), strftime() and julianday().
SELECT 'Query' || ' ' || 'Belt' AS joined, LENGTH('SQL') AS len, UPPER('sql') AS up,
SUBSTR('interview', 1, 5) AS part, REPLACE('a-b', '-', '+') AS swapped, INSTR('interview', 'view') AS pos| joined | len | up | part | swapped | pos |
|---|---|---|---|---|---|
| Query Belt | 3 | SQL | inter | a+b | 6 |
SELECT date('2026-03-15', '+10 days') AS plus_ten,
strftime('%Y-%m', '2026-03-15') AS year_month,
julianday('2026-03-15') - julianday('2026-03-01') AS days_between| plus_ten | year_month | days_between |
|---|---|---|
| 2026-03-25 | 2026-03 | 14 |
INSERT, UPDATE, DELETE, indexes
INSERT INTO departments (id, name, budget) VALUES (5, 'Legal', 10000);
UPDATE employees SET salary = salary + 100 WHERE department_id = 3;
DELETE FROM employees WHERE id = 9;
-- insert or update if the key already exists (SQLite 3.24+)
INSERT INTO tags (name) VALUES ('sql')
ON CONFLICT(name) DO UPDATE SET uses = uses + 1;Always check an UPDATE or DELETE with the same WHERE in a SELECT first. Group changes in a transaction (BEGIN ... COMMIT).
An index lets the database find rows without reading the whole table. EXPLAIN QUERY PLAN shows which way it will go: SCAN reads everything, SEARCH ... USING INDEX jumps to matching rows.
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department_id = 2| id | parent | notused | detail |
|---|---|---|---|
| 2 | 0 | 0 | SCAN employees |
CREATE INDEX idx_employees_department ON employees(department_id);
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department_id = 2| id | parent | notused | detail |
|---|---|---|---|
| 3 | 0 | 0 | SEARCH employees USING INDEX idx_employees_department (department_id=?) |
An index costs space and slows writes, and it is skipped when you wrap the column in a function or start a LIKE pattern with %.
Dialect differences
Interview questions use core SQL that works everywhere. These are the places where the dialects part ways. Only the SQLite column is run on this site; check the others against your database's documentation.
| Task | SQLite | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|---|
| First N rows | LIMIT n | LIMIT n or FETCH FIRST n ROWS ONLY | LIMIT n | TOP n or OFFSET ... FETCH |
| Join strings | a || b | a || b or CONCAT | CONCAT(a, b) | a + b or CONCAT |
| Replace NULL | COALESCE, IFNULL | COALESCE | COALESCE, IFNULL | COALESCE, ISNULL |
| Case-insensitive match | LIKE (ASCII) | ILIKE | LIKE (depends on collation) | LIKE (depends on collation) |
| Today's date | date('now') | CURRENT_DATE | CURDATE() | CAST(GETDATE() AS date) |
| Add 7 days | date(d, '+7 days') | d + INTERVAL '7 days' | DATE_ADD(d, INTERVAL 7 DAY) | DATEADD(day, 7, d) |
| Auto id | INTEGER PRIMARY KEY | GENERATED ... AS IDENTITY | AUTO_INCREMENT | IDENTITY(1,1) |
| Boolean | 0 and 1 | BOOLEAN | BOOLEAN (alias for TINYINT(1)) | BIT |
7 / 2 | 3 | 3 | 3.5000 (use DIV for 3) | 3 |
| NULLs in ORDER BY ASC | First | Last | First | First |
| Alias in WHERE | Allowed | Error | Error | Error |
Common mistakes
= NULLinstead ofIS NULL. Zero rows, no error.NOT IN (subquery)where the subquery can return NULL. Zero rows. UseNOT EXISTS.- Filtering the right table of a LEFT JOIN in
WHERE, which turns it into an inner join. Put the condition inON. - Joining to the "many" side and then summing a value from the "one" side. Every value is counted once per match.
COUNT(*)when you meantCOUNT(column), or the other way round.- Relying on row order without
ORDER BY. - Selecting columns that are neither grouped nor aggregated and trusting SQLite's lenient answer.
- Integer division where you wanted a fraction.
Keep going
- SQL interview questions: worked answers by topic
- JOIN questions: anti-joins, self joins, fan-out
- Window function questions: ranking, running totals, LAG
- What Query Belt does
Practice it, don't just read it. Query Belt turns this page into short tasks with answer checks that show which rows are missing or extra. It works offline and has no ads. See what is free.
Coming to Google Play