SQL interview cheat sheet

SELECT name, salary * 2 AS doubled
FROM emp
WHERE doubled > 250

LessonsQuery Execution Order·SELECT Basics·ORDER BY and LIMITArticleSQL Order of Execution: Why WHERE Can't See Your AliasArticleSQL SELECT Basics: Columns, Aliases, DISTINCT and SELECT *ArticleSQL ORDER BY and LIMIT: Top-N, OFFSET and Keyset Pagination

• INNER JOIN

  3 rows

  Only the pairs that match. Pepper matches twice, so he appears twice; Rusty and Ines vanish.

  SELECT d.name, a.adopter
  FROM dog d
  JOIN adoption a ON a.dog_id = d.id

  3 rows

  Result

  name	adopter
  Pepper	Omar
  Pepper	Yusuf
  Nell	Marta

• LEFT JOIN

  4 rows

  Every dog. A dog with no match comes back once, with NULL on the right.

  SELECT d.name, a.adopter
  FROM dog d
  LEFT JOIN adoption a ON a.dog_id = d.id

  4 rows

  Result

  name	adopter
  Pepper	Omar
  Pepper	Yusuf
  Rusty	NULL
  Nell	Marta

• RIGHT JOIN

  4 rows

  Every adoption — a LEFT JOIN read from the other side. Ines's dog 4 is not in the dog table.

  SELECT d.name, a.adopter
  FROM dog d
  RIGHT JOIN adoption a ON a.dog_id = d.id

  4 rows

  Result

  name	adopter
  Pepper	Yusuf
  Pepper	Omar
  Nell	Marta
  NULL	Ines

  SQLite has the keyword from 3.39; these rows came from the equivalent LEFT JOIN form on 3.37, and match the keyword on newer versions.

• FULL OUTER JOIN

  5 rows

  Every row from both sides, matched where possible. MySQL has no FULL JOIN: write LEFT JOIN UNION ALL the unmatched right rows.

  SELECT d.name, a.adopter
  FROM dog d
  FULL JOIN adoption a ON a.dog_id = d.id

  5 rows

  Result

  name	adopter
  Pepper	Omar
  Pepper	Yusuf
  Rusty	NULL
  Nell	Marta
  NULL	Ines

  SQLite has the keyword from 3.39; these rows came from the equivalent LEFT JOIN form on 3.37, and match the keyword on newer versions.

• CROSS JOIN

  12 rows

  Every dog paired with every adoption, no condition: 3 × 4 rows.

  SELECT COUNT(*) AS n
  FROM dog CROSS JOIN adoption

• Semi-join (EXISTS)

  2 rows

  Dogs that have at least one match — each once, however many matches.

  SELECT d.name FROM dog d
  WHERE EXISTS (
    SELECT 1 FROM adoption a
    WHERE a.dog_id = d.id)

  2 rows

  Result

  name
  Pepper
  Nell

• Anti-join

  1 row

  Dogs with no match: LEFT JOIN, then keep the rows whose right key came back NULL.

  SELECT d.name
  FROM dog d
  LEFT JOIN adoption a ON a.dog_id = d.id
  WHERE a.dog_id IS NULL

  1 row

  Result

  name
  Rusty

• LEFT JOIN + WHERE on the right table

  2 rows

  Rusty's NULL adopter fails the test, so the LEFT JOIN quietly became an INNER one. Move the condition into ON (AND a.adopter <> 'Omar') and Rusty is back: 3 rows.

  SELECT d.name, a.adopter
  FROM dog d
  LEFT JOIN adoption a ON a.dog_id = d.id
  WHERE a.adopter <> 'Omar'

  2 rows

  Result

  name	adopter
  Pepper	Yusuf
  Nell	Marta

LessonINNER and OUTER JOINsArticleSQL Joins Explained Visually: INNER, LEFT and FULL OUTER

• Row conditions in WHERE, group conditions in HAVING. A row condition in HAVING works but groups rows it could have dropped.
• COUNT(*) counts rows; COUNT(col) skips NULLs; COUNT(DISTINCT col) counts values.
• SUM of no rows is NULL, not 0 — COALESCE(SUM(x), 0) when a report needs a number.
• Every selected column is grouped or aggregated. SQLite accepts a bare column and picks a row; PostgreSQL rejects the query.

SELECT dept, COUNT(*) AS n,
  AVG(salary) AS avg_salary
FROM emp
WHERE salary >= 90
GROUP BY dept
HAVING COUNT(*) >= 3

SELECT COUNT(*) AS all_rows,
  COUNT(manager_id) AS has_manager,
  COUNT(DISTINCT salary) AS salaries
FROM emp

SELECT SUM(amount) AS total,
  COALESCE(SUM(amount), 0) AS safe
FROM orders
WHERE customer_id = 2

LessonAggregations & GROUP BYArticleSQL GROUP BY and HAVING Explained: WHERE vs HAVING

SELECT name, salary,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC, name) AS rn,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS drnk
FROM emp
WINDOW w AS (ORDER BY salary DESC)
ORDER BY rn

SELECT day, amount,
  LAG(amount) OVER w AS prev,
  LEAD(amount) OVER w AS next,
  SUM(amount) OVER w AS so_far
FROM sale
WINDOW w AS (ORDER BY day)
ORDER BY day

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept) AS dept_total
FROM emp
ORDER BY dept, name

SELECT amount,
  SUM(amount) OVER (
    ORDER BY amount) AS t
FROM sale
ORDER BY amount

LessonWindow FunctionsArticleSQL Window Functions Explained: OVER, PARTITION BY, RANK

Same question, three shapes: who earns more than their department's average?

SELECT e.name
FROM emp e
WHERE e.salary > (
  SELECT AVG(salary) FROM emp
  WHERE dept = e.dept)
ORDER BY e.name

SELECT e.name
FROM emp e
JOIN (SELECT dept, AVG(salary) AS avg_s
      FROM emp GROUP BY dept) AS d
  ON d.dept = e.dept
WHERE e.salary > d.avg_s
ORDER BY e.name

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_s
  FROM emp GROUP BY dept
)
SELECT e.name
FROM emp e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_s
ORDER BY e.name

LessonsSubqueries·CTEs and RecursionArticleSQL Subqueries Explained: Scalar, IN, EXISTS and CorrelatedArticleSQL Recursive CTE Explained: Anchor, Recursive Step and Cycles

SELECT name FROM customer
WHERE id NOT IN (
  SELECT customer_id FROM orders)

SELECT c.name FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id)

SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS spent
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id

LessonWHERE and FilteringArticleSQL NULL in WHERE: Why = NULL Matches Nothing

• Cost: a scan is O(n); an index search is O(log n) plus the k matches, plus one row lookup each unless the index covers the query.
• Every write pays: each insert, delete and update of an indexed column also updates every index that holds it — O(log n) more per index, and more storage.
• The planner may ignore a good index when the query matches a large share of the table: one sequential scan can beat many row lookups.
• Composite order matters: put the column you always filter by with = first; the index serves any leftmost prefix of its columns.

LessonsIndexes·Composite Indexes·Reading a Query PlanArticleSQL Indexes Explained: Full Scan vs Index Search, With Query PlansArticleComposite and Covering Indexes: Why Column Order MattersArticleHow to Read a Query Plan: EXPLAIN in SQL, Line by Line

Counted with SQLite's trace callback, which records every statement it runs. Bind the ids of the IN (…) list as parameters, never paste them in; index the foreign key, or each of the N queries is a scan as well as a trip.

LessonThe N+1 Query ProblemArticleThe N+1 Query Problem: How to Spot It and Fix It

AAtomic

All the statements land, or none do; a failure rolls back the earlier ones too.

CConsistent

Constraints (CHECK, foreign keys, UNIQUE) hold before and after.

IIsolated

Others do not see your half-finished work — how strictly is the isolation level.

DDurable

Once committed, it survives a crash.

Dirty: you read uncommitted data. Non-repeatable: a row you read twice changed. Phantom: the set of rows matching a condition changed. Engines may give more than the minimum (PostgreSQL's repeatable read also stops phantoms), and defaults differ: read committed in PostgreSQL, repeatable read in MySQL's InnoDB, and SQLite runs every transaction serializable.

LessonsTransactions & ACID·Isolation LevelsArticleACID Transactions Explained: Commit, Rollback and ConstraintsArticleSQL Isolation Levels Explained: Dirty, Non-Repeatable, Phantom

• Second-highest salary

  The second-highest distinct salary, or NULL if there is none.

  SELECT MAX(salary) AS second
  FROM emp
  WHERE salary < (SELECT MAX(salary) FROM emp)

  1 row

  Result

  second
  120

  An aggregate over zero rows is one NULL row, so a one-salary table answers NULL, not nothing.

  Lesson

  Subqueries · SQL

  Practice

  • Second Highest SalaryEasy
  • Department With the Highest Average PayMedium

• N-th highest salary

  The third-highest distinct salary.

  SELECT DISTINCT salary
  FROM (
    SELECT salary, DENSE_RANK() OVER (
      ORDER BY salary DESC) AS r
    FROM emp) AS ranked
  WHERE r = 3

  1 row

  Result

  salary
  100

  DENSE_RANK, not RANK: the three 95s would push RANK past 5 and leave gaps.

  Lesson

  Window Functions · SQL

  Practice

  • Running Balance per AccountMedium
  • Collapse a Status Log Into RunsHard

• Find duplicates

  Emails that signed up more than once, and how often.

  SELECT email, COUNT(*) AS n
  FROM signup
  GROUP BY email
  HAVING COUNT(*) > 1
  ORDER BY email

  2 rows

  Result

  email	n
  a@x.io	3
  b@x.io	2

  To keep the first of each: DELETE FROM signup WHERE id NOT IN (SELECT MIN(id) FROM signup GROUP BY email) leaves ids 1, 2, 4.

  Lesson

  Aggregations & GROUP BY · SQL

  Practice

  • Emails Used More Than OnceEasy
  • Department With the Highest Average PayMedium

• Rows with no match

  Customers who never ordered.

  SELECT c.name
  FROM customer c
  WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id)

  1 row

  Result

  name
  Jon

  NOT IN (SELECT customer_id FROM orders) returns no rows here: order 4 has a NULL customer_id.

  Lesson

  INNER and OUTER JOINs · SQL

  Practice

  • Second Highest SalaryEasy
  • Same Reading Three Times in a RowMedium

• Earns more than their manager

  Employees paid more than the person they report to.

  SELECT e.name, e.salary,
    m.salary AS manager_salary
  FROM emp e
  JOIN emp m ON m.id = e.manager_id
  WHERE e.salary > m.salary
  ORDER BY e.name

  2 rows

  Result

  name	salary	manager_salary
  Cleo	130	120
  Finn	100	95

  A self-join: the same table twice under two aliases. The inner join drops Ana, who has no manager.

  Lesson

  INNER and OUTER JOINs · SQL

  Practice

  • Customers Who Never OrderedEasy
  • Same Reading Three Times in a RowMedium

• Top N per group

  The two best-paid people in each department.

  SELECT dept, name, salary
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC) AS rn
    FROM emp) AS ranked
  WHERE rn <= 2
  ORDER BY dept, rn

  4 rows

  Result

  dept	name	salary
  eng	Cleo	130
  eng	Ana	120
  ops	Finn	100
  ops	Dev	95

  A window cannot sit in WHERE, so rank in a subquery and filter outside. ROW_NUMBER gives exactly N; DENSE_RANK keeps ties (top 3 in eng is then 4 people).

  Lesson

  Window Functions · SQL

  Practice

  • Top Two Earners per DepartmentMedium
  • Collapse a Status Log Into RunsHard

• Running total

  Sales so far, day by day.

  SELECT day, amount,
    SUM(amount) OVER (
      ORDER BY day) AS total
  FROM sale
  ORDER BY day

  4 rows

  Result

  day	amount	total
  2026-09-01	40	40
  2026-09-02	55	95
  2026-09-03	35	130
  2026-09-04	55	185

  The default frame is RANGE: rows tied on the ORDER BY key share one total. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a strict row-by-row sum.

  Lesson

  Window Functions · SQL

  Practice

  • Running Balance per AccountMedium
  • Collapse a Status Log Into RunsHard

• Three days in a row

  People who logged in on at least three consecutive days.

  SELECT DISTINCT person
  FROM (
    SELECT person, day,
      LAG(day, 2) OVER (
        PARTITION BY person
        ORDER BY day) AS two_back
    FROM login) AS t
  WHERE julianday(day)
    - julianday(two_back) = 2

  1 row

  Result

  person
  kim

  Two rows back is exactly two days back only if every row is a different day: deduplicate the logins first.

  Dialectjulianday() is SQLite. On PostgreSQL DATE columns, day - two_back = 2; on MySQL, DATEDIFF(day, two_back) = 2.

  Lesson

  Window Functions · SQL

  Practice

  • Running Balance per AccountMedium
  • Collapse a Status Log Into RunsHard

• Gaps and islands

  Collapse the taken seat numbers into runs.

  SELECT MIN(n) AS run_start,
    MAX(n) AS run_end
  FROM (
    SELECT n, n - ROW_NUMBER() OVER (
      ORDER BY n) AS grp
    FROM seat) AS t
  GROUP BY grp
  ORDER BY run_start

  3 rows

  Result

  run_start	run_end
  1	3
  5	6
  9	9

  Inside a run, value and row number climb together, so their difference is constant: that difference is the group. The gaps are between one run_end and the next run_start.

  Lesson

  Window Functions · SQL

  Practice

  • Running Balance per AccountMedium
  • Collapse a Status Log Into RunsHard

• Everyone under a manager

  The whole org chart from the top, with each person's depth.

  WITH RECURSIVE chain AS (
    SELECT id, name, 0 AS depth
    FROM emp WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, c.depth + 1
    FROM emp e
    JOIN chain c ON e.manager_id = c.id
  )
  SELECT name, depth FROM chain
  ORDER BY depth, name

  7 rows

  Result

  name	depth
  Ana	0
  Ben	1
  Cleo	1
  Dev	1
  Eve	2
  Finn	2
  Gus	2

  Anchor, then a step that joins back onto the CTE until it finds nothing. Data with a cycle never runs out: cap the depth or track visited ids.

  Lesson

  CTEs and Recursion · SQL

  Practice

  • Users Retained Month Over MonthHard