Quick-fire SQL theory questions

Step 1 Understand it

Why you need it. These questions take thirty seconds each but set the tone for the interview. A crisp answer shows you really understand the tool; a vague one makes the interviewer dig deeper. They are collected here so you can revise them in one place.

Picture it

flowchart LR
  Q["a difference-between<br/>question"] --> D["1 define both"] --> K["2 the key difference"] --> W["3 when to use each<br/>plus a tiny example"]

The idea

Use the same three-part shape every time. One: define both things in a sentence each. Two: state the difference that matters. Three: say when you would choose each, ideally with an example. Keep it under thirty seconds, then stop — let them ask for more. Open the follow-up questions below and answer each one out loud before showing the answer.

How it works, step by step

1. Define both sides: "WHERE filters rows; HAVING filters groups."
2. Give the difference that matters: "WHERE runs before grouping, so it can't use aggregates."
3. Say when to use each: "Row conditions in WHERE, aggregate conditions in HAVING."
4. Offer a tiny example if it helps, then stop talking.

Analogy

Penalty practice. You don't learn penalties by thinking about them; you take the same kick again and again until it is automatic, so it holds up under pressure.

Where the analogy breaks

A penalty is the same every time. Interviewers rephrase questions, so learn the idea behind each answer, not just the words.

Worked example

SELECT (SELECT COUNT(*) FROM employees) AS count_star,
       (SELECT COUNT(manager_id) FROM employees) AS count_column,
       (SELECT COUNT(*) FROM (SELECT location FROM departments
                              UNION SELECT city FROM customers) AS u) AS union_rows,
       (SELECT COUNT(*) FROM (SELECT location FROM departments
                              UNION ALL SELECT city FROM customers) AS ua) AS union_all_rows;

Must-know points

• Shape: define both, the key difference, when to use each.
• Keep it under thirty seconds, then stop.
• Use a tiny example from tables you know, like employees and orders.
• Revise the follow-up questions below out loud until each answer is automatic.

Common mistakes

• Rambling for two minutes on a thirty-second question.
• Giving only one side ("HAVING is for groups") without the contrast.
• Memorising words without the reason, then freezing when the question is rephrased.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  Top SQL interview questions (video)

  YouTube search, free

• Read

  Mode SQL Tutorial — The SQL Tutorial for Data Analysis

  Mode, free

• Practice

  LeetCode — Top SQL 50 study plan

  free account

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Answer five follow-up questions below out loud, without opening the answers, each in under thirty seconds.

   Show answer

   Five, each in one breath:

   • WHERE vs HAVING: WHERE filters rows before grouping and can't use aggregates; HAVING filters groups after GROUP BY, so it can use COUNT and SUM.
   • INNER JOIN vs LEFT JOIN: INNER keeps only rows that match on both sides; LEFT keeps every row of the left table and fills NULLs where the right has no match. Use LEFT for "including the ones with none".
   • UNION vs UNION ALL: both stack two results; UNION removes duplicate rows (extra work), UNION ALL keeps them all and is faster. Default to UNION ALL unless you need distinct rows.
   • DELETE vs TRUNCATE vs DROP: DELETE removes chosen rows and can be rolled back; TRUNCATE empties the whole table fast; DROP removes the table itself.
   • RANK vs DENSE_RANK vs ROW_NUMBER: ROW_NUMBER gives 1, 2, 3, 4 with no ties; RANK lets ties share and then skips (1, 2, 2, 4); DENSE_RANK lets ties share without skipping (1, 2, 2, 3).

2. Writefrom a blank page, then run it

   Write one query that shows the difference between INNER JOIN and LEFT JOIN on departments and employees, as two counts in one row.

   Show answer

   Each count is a scalar subquery, so both fit in one row.

   The INNER JOIN gives 7: every employee who has a department (Gus has none, so he isn't paired). The LEFT JOIN from departments gives 8: the same 7, plus one row for HR, which has nobody, with NULL employee columns. The difference is exactly the unmatched rows on the left side.

   sqlCopy

   SELECT (SELECT COUNT(*) FROM departments d
             JOIN employees e ON e.dept_id = d.dept_id) AS inner_join_rows,
          (SELECT COUNT(*) FROM departments d
             LEFT JOIN employees e ON e.dept_id = d.dept_id) AS left_join_rows;

   Try it

   1 row

   inner_join_rows	left_join_rows
   7	8

3. Predictsay it before you run it

   What does SELECT COUNT(DISTINCT city), COUNT(city), COUNT(*) FROM customers; return?

   Show answer

   3, 5 and 6.

   • COUNT(DISTINCT city): London, Leeds and Bristol. The NULL is not counted.
   • COUNT(city): every city that isn't NULL, duplicates included: Zoe is the only one missing, so 5.
   • COUNT(*): every row, 6.

   sqlCopy

   SELECT COUNT(DISTINCT city), COUNT(city), COUNT(*) FROM customers;

   Try it

   1 row

   COUNT(DISTINCT city)	COUNT(city)	COUNT(*)
   3	5	6

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Explain why SELECT DISTINCT and GROUP BY can give the same rows, and when they don't.

   Show answer

   When you select only the grouped columns, they mean the same thing: SELECT DISTINCT dept_id FROM employees and SELECT dept_id FROM employees GROUP BY dept_id both give one row per department (with one row for NULL). Many databases even run them the same way.

   They differ when:
   • You want something per group. GROUP BY can count, sum or average each group (below); DISTINCT can only remove duplicates.
   • You filter groups. HAVING works with GROUP BY; DISTINCT has nothing like it.
   • DISTINCT applies to every selected column, including calculated ones, and runs after SELECT. GROUP BY groups by what you list, and everything else in SELECT must be aggregated.
   • The output order: neither guarantees one without ORDER BY.

   sqlCopy

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

   Try it

   4 rows

   dept_id	people
   NULL	1
   1	4
   2	2
   3	1

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

For any "what's the difference" question I give a one-line definition of each, the difference that matters in practice, and when I would choose each. For example: WHERE filters rows before grouping and can't use aggregates; HAVING filters groups after GROUP BY and can — so row conditions go in WHERE and aggregate conditions in HAVING.

Follow-up questions

Tick each check when you can do it without notes