🧮 SQL, in 10 cards

No chapters to read. Each card gives you the one-line answer, a picture, a quick try-it and the trap interviewers hope you fall into. About 15 minutes for all ten.

0 of 10 done

1 of 10 · Basics

In what order does SQL actually run a query?

Say this first

Not the order you write it. The database filters first, groups next, and only then picks columns.

Picture it

1FROM / JOIN Pick the tables and combine them.

Some databases let GROUP BY or HAVING use aliases, but WHERE never can.

Try it

Why does WHERE total > 100 fail when total is an alias defined in SELECT?

Common trap

Assuming SQL runs in the order you type it. That is why aliases fail in WHERE and aggregates fail in WHERE.

Say it like this

SQL runs FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, so WHERE can't see aliases or aggregates.

2 of 10 · Basics

DDL, DML, DCL and TCL: what are they?

Say this first

SQL commands fall into four groups by what they do: define, change, permit, or control transactions.

Picture it

Group	Does what	Examples
DDL	Defines structure	CREATE, ALTER, DROP, TRUNCATE
DML	Changes data	INSERT, UPDATE, DELETE
DCL	Controls access	GRANT, REVOKE
TCL	Controls transactions	COMMIT, ROLLBACK, SAVEPOINT

SELECT is often listed as DQL (query), or inside DML. Say which one you mean.

Try it

Which group does GRANT belong to?

Common trap

Putting TRUNCATE under DML. Most sources class it as DDL, though a few say DML, so know which you'd defend.

Say it like this

DDL defines structure, DML changes data, DCL controls access, and TCL controls transactions.

3 of 10 · Filtering & grouping

How do you filter with IN, BETWEEN, LIKE and IS NULL?

Say this first

IN checks a list, BETWEEN checks a range, LIKE matches a pattern, and NULL needs IS NULL.

Picture it

Tap each one to see what it means.

SELECT order_id
FROM orders
WHERE amount BETWEEN 100 AND 500
  AND customer_id IN (1, 2, 3);

Try it

Amounts are 100, 250, 500 and 501. How many rows match amount BETWEEN 100 AND 500?

Common trap

Thinking BETWEEN leaves out the end values. It includes both.

Say it like this

IN checks a list, BETWEEN includes both ends, LIKE uses % and _, and NULL needs IS NULL.

4 of 10 · Filtering & grouping

How does GROUP BY work?

Say this first

It puts rows with the same value together and gives you one result row per group.

Picture it

1Rows Start with every row left after WHERE.

SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id;

Try it

SELECT dept_id, name, COUNT(*) FROM employees GROUP BY dept_id. What is wrong?

Common trap

Selecting a column that isn't grouped. Some MySQL settings let it through and return an arbitrary value, which hides the bug.

Say it like this

GROUP BY makes one row per group, so every selected column must be grouped or aggregated.

5 of 10 · Filtering & grouping

COUNT, SUM, AVG, MIN, MAX: what do they return?

Say this first

Aggregates squeeze many rows into one value, and they skip NULLs.

Picture it

Function	What it returns
COUNT(*)	The number of rows
COUNT(col)	The number of non-NULL values
COUNT(DISTINCT col)	The number of different non-NULL values
SUM / AVG	Total / average, ignoring NULL
MIN / MAX	Smallest / largest

Try it

Salary values are 10, 20, 20 and NULL. What does COUNT(DISTINCT salary) return?

Common trap

Expecting AVG to treat NULL as 0. It skips NULL rows, so the average comes out higher than you'd guess.

Say it like this

Aggregates skip NULLs, COUNT(*) counts rows, and COUNT(DISTINCT col) counts different values.

6 of 10 · Filtering & grouping

How do you write if-else in SQL?

Say this first

CASE WHEN checks conditions from top to bottom and returns the first one that matches.

Picture it

salary	band
95000	High
60000	Mid
30000	Low

SELECT name,
  CASE WHEN salary >= 80000 THEN 'High'
       WHEN salary >= 50000 THEN 'Mid'
       ELSE 'Low' END AS band
FROM employees;

Try it

What band does a salary of 60000 get?

Common trap

Putting a broad condition first. If >= 50000 came before >= 80000, everyone above 50000 would be Mid, because CASE stops at the first match.

Say it like this

CASE returns the first matching branch, so I order conditions from most specific to least.

7 of 10 · Joins & subqueries

How do you find rows that have no match, like customers with no orders?

Say this first

Three ways: LEFT JOIN then IS NULL, NOT EXISTS, or NOT IN. NOT IN has a NULL trap.

Picture it

Tap each one to see what it means.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Try it

customers NOT IN (SELECT customer_id FROM orders) returns zero rows, even though some customers have no orders. Likely cause?

Common trap

Using NOT IN on a column that can contain NULL. It silently returns nothing.

Say it like this

To find rows with no match I use LEFT JOIN with IS NULL or NOT EXISTS, and I avoid NOT IN when NULLs are possible.

8 of 10 · Window functions

What is a window function?

Say this first

It calculates across related rows without collapsing them, and PARTITION BY says which rows are related.

Picture it

GROUP BY

• One row per group
• The original rows disappear
• Good for totals and counts

Window function (OVER)

• Keeps every row
• Adds a calculated column
• Good for comparing a row to its group

SELECT name, dept_id, salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

Try it

You want each employee's salary shown next to their department average. Which fits?

Common trap

Thinking OVER() removes rows like GROUP BY does. It never does.

Say it like this

A window function calculates across related rows without collapsing them.

9 of 10 · Window functions

ROW_NUMBER vs RANK vs DENSE_RANK?

Say this first

All three number rows. They differ in what they do when two rows tie.

Picture it

salary	ROW_NUMBER	RANK	DENSE_RANK
90	1	1	1
90	2	1	1
80	3	3	2

After the tie on 90, RANK skips to 3. DENSE_RANK carries on with 2.

Try it

Salaries are 90, 90 and 80. What is DENSE_RANK for 80?

Common trap

Using RANK or ROW_NUMBER for 'Nth highest salary'. Ties and gaps pick the wrong row. Use DENSE_RANK.

Say it like this

ROW_NUMBER never ties, RANK skips after a tie, and DENSE_RANK doesn't skip, so it's the one for Nth highest.

10 of 10 · Writing real queries

What is a CTE, and why use one?

Say this first

A named, temporary result you define with WITH and use in the query that follows.

Picture it

1Name it WITH dept_avg AS (...) gives a subquery a name.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
)
SELECT e.name
FROM employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;

Try it

How long does a CTE exist?

Common trap

Thinking a CTE is stored like a view, or always faster. It is mainly for readability, and speed depends on the database.

Say it like this

A CTE names a subquery so the query reads top to bottom, and it only lives for that one statement.

Full SQL Notes

Want the rest?

The free cards cover the core. The full notes add 14 more cards in the same style, plus practice queries and rapid-fire answers:

• ON vs WHERE in joins, EXISTS vs IN, INTERSECT and EXCEPT
• LAG and LEAD, running totals, top N per group, Nth highest
• Recursive CTEs, COALESCE, date ranges, duplicates, safe updates
• 15 SQL interview queries with answers
• 17 rapid-fire one-line answers

See guides and what's coming →

A prep aid, not a guarantee of a job.