InterviewGrind Get notified

InterviewGrind › SQL

SQL Interview Questions and Answers

NULL handling, joins, GROUP BY and window functions: SQL interview questions with the exact rows each query returns, verified by running them.

15 free questions from the app. Try each one before you open the answer.

1. AggregationEasy

You want departments with more than 5 employees. Where does COUNT(*) > 5 go?

  1. In WHERE, before GROUP BY
  2. In HAVING, after GROUP BY
  3. In ORDER BY
  4. In the SELECT list
Show answer

Answer: B. In HAVING, after GROUP BY

WHERE filters rows before grouping; HAVING filters groups after aggregation. You can use both in one query.

2. Set operationsEasy

Combining this year's and last year's newsletter lists, which can't overlap, the query is slower than it should be. What's the difference between UNION and UNION ALL?

  1. UNION removes duplicate rows (extra work); UNION ALL keeps every row
  2. UNION ALL removes duplicates; UNION keeps them
  3. UNION works only on two tables
  4. They're identical; ALL is just more explicit
Show answer

Answer: A. UNION removes duplicate rows (extra work); UNION ALL keeps every row

Removing duplicates requires sorting or hashing the combined result. When you know the inputs can't overlap, or duplicates are fine, UNION ALL is cheaper.

3. AggregationEasy

orders(id, user_id, amount): (10, 1, 50), (11, 1, 20), (12, 2, 30). Marketing asks for orders and unique buyers in one query. What does it return?

SELECT COUNT(DISTINCT user_id) FROM orders;

What does it output?

  1. 3
  2. 2
  3. 1
  4. 100
Show answer

Answer: B. 2

COUNT(DISTINCT col) counts unique non-NULL values: users 1 and 2. A typical use is "how many customers ordered this month".

4. IndexesEasy

Myth or fact: adding an index always makes your database faster.

  1. Myth
  2. Fact
Show answer

Answer: A. Myth

Myth. Every index slows down inserts and updates and takes storage. For columns with few distinct values, or queries that read most of the table, the planner may ignore the index anyway. Index what your slow queries actually filter and sort on.

5. NULL handlingEasy

Table emp has rows: (Ann, eng), (Bo, eng), (Cy, ops), (Di, NULL). The headcount and the department count disagree. What does this return?

SELECT COUNT(*), COUNT(dept) FROM emp;

What does it output?

  1. 4, 4
  2. 4, 3
  3. 3, 3
  4. 4, 2
Show answer

Answer: B. 4, 3

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column, so Di's NULL department is skipped.

6. JoinsEasy

users LEFT JOIN orders ON orders.user_id = users.id. A user has no orders. What happens to that user?

  1. The user is left out of the result
  2. The user appears once, with NULL in every orders column
  3. The query fails
  4. The user is matched to a random order from the orders table
Show answer

Answer: B. The user appears once, with NULL in every orders column

LEFT JOIN keeps every row from the left table. Where there's no match, the right side's columns are NULL. Filtering on orders.id IS NULL finds users with no orders.

7. OrderingEasy

Myth or fact: without ORDER BY, rows come back in the order they were inserted.

  1. Myth
  2. Fact
Show answer

Answer: A. Myth

Myth. Without ORDER BY the order is unspecified. It often looks like insertion order on small tables, then changes when the planner picks an index, runs in parallel, or rows get updated. If order matters, say so.

8. NULLEasy

This should find users who never set an email, but it always returns zero rows. What's the one-token fix?

SELECT id FROM users WHERE email = NULL;
  1. = → IS
  2. NULL → ''
  3. WHERE → HAVING
  4. = → ==
Show answer

Answer: A. = → IS

Any comparison with NULL using = gives UNKNOWN, which WHERE treats as false. IS NULL is the only way to test for missing values.

9. TransactionsEasy

Moving money runs two UPDATEs: subtract from A, add to B. Why wrap them in a transaction?

  1. If anything fails midway, neither change is kept
  2. To make both UPDATEs run faster together
  3. Because every UPDATE statement requires its own transaction
  4. To lock the whole database during the move
Show answer

Answer: A. If anything fails midway, neither change is kept

Atomicity: all of the transaction's changes commit together or not at all. The other ACID properties are consistency, isolation (concurrent transactions don't see each other's partial work) and durability.

10. AggregationEasy

This should list every email address used by more than one account. It won't run.

  1. SELECT email, COUNT(*) AS accounts
  2. FROM users
  3. GROUP BY email
  4. WHERE COUNT(*) > 1;

Which line has the bug?

Show answer

Answer: Line 4: WHERE COUNT(*) > 1;

WHERE filters rows before grouping, so it can't use aggregates, and it must come before GROUP BY anyway. Conditions on groups go in HAVING COUNT(*) > 1.

11. NULL handlingMedium

Table emp has rows: (Ann, eng), (Bo, eng), (Cy, ops), (Di, NULL). A report of everyone outside engineering comes up short. What does this return?

SELECT COUNT(*) FROM emp WHERE dept <> 'eng';

What does it output?

  1. 1
  2. 2
  3. 0
  4. 4
Show answer

Answer: A. 1

Comparing NULL with anything gives UNKNOWN, not TRUE, so Di is filtered out. Only Cy matches. Add OR dept IS NULL if you want Di included.

12. JoinsMedium

users: (1, Ann), (2, Bo), (3, Cy). orders(id, user_id, amount): (10, 1, 50), (11, 1, 20), (12, 2, 30). An orders-per-user report should include Cy with zero. What does it return?

SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY u.name;

What does it output?

  1. Ann, 2
    Bo, 1
  2. Ann, 2
    Bo, 1
    Cy, 0
  3. Ann, 2
    Bo, 1
    Cy, 1
  4. Ann, 1
    Bo, 1
    Cy, 0
Show answer

Answer: B. Ann, 2 / Bo, 1 / Cy, 0

LEFT JOIN keeps Cy with NULL order columns, and COUNT(o.id) skips NULLs, giving 0. COUNT(*) would count Cy's NULL row and report 1, a classic bug.

13. GroupingMedium

Table emp has rows: (Ann, eng), (Bo, eng), (Cy, ops), (Di, NULL). A per-department headcount includes a mystery row. What does SQLite return?

SELECT dept, COUNT(*) FROM emp
GROUP BY dept
ORDER BY dept;

What does it output?

  1. eng, 2
    ops, 1
  2. NULL, 1
    eng, 2
    ops, 1
  3. eng, 2
    ops, 1
    NULL, 1
  4. eng, 3
    ops, 1
Show answer

Answer: B. NULL, 1 / eng, 2 / ops, 1

GROUP BY puts all NULLs in one group (unlike =, which never matches NULL). SQLite and Postgres disagree on where NULLs sort: SQLite puts them first in ascending order, Postgres last. Use NULLS FIRST/LAST to be explicit.

14. NULL handlingMedium

Table t has a column x with values 10, NULL, 20. The average looks too high. What does this return?

SELECT SUM(x), AVG(x), COUNT(x) FROM t;

What does it output?

  1. 30, 10.0, 3
  2. 30, 15.0, 2
  3. NULL, NULL, 3
  4. 30, 15.0, 3
Show answer

Answer: B. 30, 15.0, 2

Aggregates ignore NULLs: the average is 30 / 2, not 30 / 3. If missing values should count as zero, write AVG(COALESCE(x, 0)).

15. IndexesMedium

There's an index on (last_name, first_name). Which query can use it efficiently?

  1. WHERE first_name = 'Ann'
  2. WHERE last_name = 'Lee'
  3. WHERE first_name LIKE '%nn'
  4. None; composite indexes only work for ORDER BY
Show answer

Answer: B. WHERE last_name = 'Lee'

A composite index is sorted by its first column, then the second, like a phone book. It helps queries that filter on a leftmost prefix: last_name, or last_name + first_name. Filtering on first_name alone can't use the sort order.

Practise like this every day

InterviewGrind gives you ten questions like these each day on your iPhone, with Pro tracks for company-style loops.

Get the launch email

More interview questions

Python · JavaScript · Java · C++ · System design · Big-O · Coding patterns · CS fundamentals