Query Belt Coming to Google Play

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.

By Andrii Boiko · Updated 2026-11-18 · Every result on this page was produced by running the query on the sample database below in SQLite 3.37.2.

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

idnamebudget
1Sales30000
2Engineering80000
3Support20000
4Finance15000

employees

idfull_namedepartment_idmanager_idsalaryhired_on
1Mara Lind2NULL92002019-03-04
2Joel Park2176002020-06-15
3Ines Cole2176002021-01-11
4Tomas Reyes1569002018-09-01
5Nadia Voss1NULL64002017-05-22
6Owen Hart1543002022-02-14
7Pia Novak3839002023-04-03
8Sven Ito3NULL52002020-11-30
9Lea BrandtNULLNULL48002024-01-08
10Ravi Shah2161002022-08-19

sales

idsold_onregionamount
12026-01-01North120
22026-01-02South80
32026-01-03North150
42026-01-04SouthNULL
52026-01-05North90
62026-01-06South200
72026-01-07North110

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 writeThe database evaluates
SELECT1. FROM and JOIN: build the rows
FROM / JOIN ... ON2. WHERE: filter rows
WHERE3. GROUP BY: make groups
GROUP BY4. HAVING: filter groups
HAVING5. Window functions
ORDER BY6. SELECT: compute columns, then DISTINCT
LIMIT / OFFSET7. 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_namesalary
Mara Lind9200
Ines Cole7600
Joel Park7600
Result: 3 rows

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
Result: 4 rows

Without an ORDER BY, the order of rows is not guaranteed, even if it looks stable today.

Filtering: IN, BETWEEN, LIKE, dates

SELECT full_name, salary
FROM employees
WHERE salary BETWEEN 6100 AND 7600
ORDER BY salary, full_name
full_namesalary
Ravi Shah6100
Nadia Voss6400
Tomas Reyes6900
Ines Cole7600
Joel Park7600
Result: 5 rows · both 6100 and 7600 are included
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_namehired_on
Owen Hart2022-02-14
Ravi Shah2022-08-19
Pia Novak2023-04-03
Result: 3 rows

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
equalis_nullplus_nullfallback
NULL1NULL5
Result: 1 row
SELECT full_name
FROM employees
WHERE department_id IS NULL
full_name
Lea Brandt
Result: 1 row

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_rowswith_departmentdepartmentslowesthighestaverage
1093390092006200
Result: 1 row
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_idheadcountavg_salary
135867
247625
324550
Result: 3 rows · the NULL-department group has one row, so HAVING drops it

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_nameband
Mara Lindhigh
Joel Parkhigh
Ines Colehigh
Tomas Reyesmid
Nadia Vossmid
Result: 5 rows

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_divreal_divremaindersafe_div
33.51NULL
Result: 1 row · two integers divide as integers in SQLite

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

TypeKeeps
INNER JOINOnly rows with a match on both sides
LEFT JOINAll rows from the left table; NULLs where the right has no match
RIGHT / FULL JOINMirror image / both sides. In SQLite only from version 3.39; rewrite with LEFT JOIN on older versions
CROSS JOINEvery 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_namedepartment
Mara LindEngineering
Joel ParkEngineering
Ines ColeEngineering
Tomas ReyesSales
Nadia VossSales
Owen HartSales
Pia NovakSupport
Sven ItoSupport
Ravi ShahEngineering
Result: 9 rows

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_namedepartment
Mara LindEngineering
Joel ParkEngineering
Ines ColeEngineering
Tomas ReyesSales
Nadia VossSales
Owen HartSales
Pia NovakSupport
Sven ItoSupport
Lea BrandtNULL
Ravi ShahEngineering
Result: 10 rows

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
Result: 1 row

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
employeemanager
Joel ParkMara Lind
Ines ColeMara Lind
Tomas ReyesNadia Voss
Owen HartNadia Voss
Pia NovakSven Ito
Ravi ShahMara Lind
Result: 6 rows · people without a manager are not listed; use LEFT JOIN to keep them

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
Result: 2 rows · UNION ALL would return 6 rows here

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
Result: 1 row

Subqueries, EXISTS, CTEs

SELECT full_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC, full_name
full_namesalary
Mara Lind9200
Ines Cole7600
Joel Park7600
Tomas Reyes6900
Nadia Voss6400
Result: 5 rows · average salary is 6200

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
Result: 1 row

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_namesalarydept_average
Mara Lind92007625
Nadia Voss64005867
Sven Ito52004550
Tomas Reyes69005867
Result: 4 rows · employees above their own department's average

Window functions

A window function computes a value across related rows without collapsing them. The pattern is function() OVER (PARTITION BY ... ORDER BY ... frame).

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_namesalaryrank_in_dept
Mara Lind92001
Ines Cole76002
Joel Park76002
Ravi Shah61004
Result: 4 rows · a tie at 2 means the next rank is 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_onamountrunning_total
2026-01-01120120
2026-01-0280200
2026-01-03150350
2026-01-04NULL350
2026-01-0590440
2026-01-06200640
2026-01-07110750
Result: 7 rows

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
joinedlenuppartswappedpos
Query Belt3SQLintera+b6
Result: 1 row
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_tenyear_monthdays_between
2026-03-252026-0314
Result: 1 row

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
idparentnotuseddetail
200SCAN employees
Result: 1 row · no index yet: the whole table is scanned
CREATE INDEX idx_employees_department ON employees(department_id);
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE department_id = 2
idparentnotuseddetail
300SEARCH employees USING INDEX idx_employees_department (department_id=?)
Result: 1 row · after CREATE INDEX: an index search. The exact wording varies between SQLite versions

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.

TaskSQLitePostgreSQLMySQLSQL Server
First N rowsLIMIT nLIMIT n or FETCH FIRST n ROWS ONLYLIMIT nTOP n or OFFSET ... FETCH
Join stringsa || ba || b or CONCATCONCAT(a, b)a + b or CONCAT
Replace NULLCOALESCE, IFNULLCOALESCECOALESCE, IFNULLCOALESCE, ISNULL
Case-insensitive matchLIKE (ASCII)ILIKELIKE (depends on collation)LIKE (depends on collation)
Today's datedate('now')CURRENT_DATECURDATE()CAST(GETDATE() AS date)
Add 7 daysdate(d, '+7 days')d + INTERVAL '7 days'DATE_ADD(d, INTERVAL 7 DAY)DATEADD(day, 7, d)
Auto idINTEGER PRIMARY KEYGENERATED ... AS IDENTITYAUTO_INCREMENTIDENTITY(1,1)
Boolean0 and 1BOOLEANBOOLEAN (alias for TINYINT(1))BIT
7 / 2333.5000 (use DIV for 3)3
NULLs in ORDER BY ASCFirstLastFirstFirst
Alias in WHEREAllowedErrorErrorError

Common mistakes

  1. = NULL instead of IS NULL. Zero rows, no error.
  2. NOT IN (subquery) where the subquery can return NULL. Zero rows. Use NOT EXISTS.
  3. Filtering the right table of a LEFT JOIN in WHERE, which turns it into an inner join. Put the condition in ON.
  4. Joining to the "many" side and then summing a value from the "one" side. Every value is counted once per match.
  5. COUNT(*) when you meant COUNT(column), or the other way round.
  6. Relying on row order without ORDER BY.
  7. Selecting columns that are neither grouped nor aggregated and trusting SQLite's lenient answer.
  8. Integer division where you wanted a fraction.

Keep going

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