SQL

An inner join keeps only rows that match on both sides; a left join keeps every row from the left table, filling the right side with NULLs when nothing matches.

Here, Legal has no employees. The left join below still lists it, with a count of zero. Swap LEFT JOIN for JOIN and Legal vanishes from the report entirely - no error, just a department that silently stopped existing. That is the bug this question is about.

SELECT d.name AS department, COUNT(e.id) AS people
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY d.name

What it is really testing. Whether you have been caught by it. Everyone can recite the definition; the candidate who says "and that is how you lose the empty department" has debugged a report.

COUNT(*) counts the row the join produced, so an unmatched department would count as one instead of zero.

A left join with no match still emits a row, with every right-hand column NULL. COUNT(*) counts rows and would report Legal as having one employee. COUNT(e.id) counts non-NULL values of that column, so it correctly reports zero. This is the single most common off-by-one in reporting SQL.

SELECT COUNT(*) AS all_rows, COUNT(dept_id) AS rows_with_dept
FROM employees

What it is really testing. Whether you know COUNT ignores NULLs. It is the mechanism behind the previous answer and the next one, and it is worth saying out loud.

WHERE filters rows before they are grouped; HAVING filters the groups afterwards.

That ordering is why you cannot put an aggregate in WHERE - at the point WHERE runs, the groups do not exist yet, so there is no average to compare against. Use WHERE to cut rows you never wanted, and HAVING only for conditions on the aggregate. Filtering early is also faster.

SELECT d.name AS department, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.name
HAVING AVG(e.salary) > 70000
ORDER BY d.name

What it is really testing. Whether you can say why, not just which. "HAVING is for aggregates" is correct and incomplete; the reason is the execution order.

FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT.

Which is nearly the opposite of how you write it, and it explains two everyday puzzles: you cannot use a column alias defined in SELECT inside WHERE, because SELECT has not run yet - but you can use it in ORDER BY, because that runs after.

What it is really testing. Whether the alias rule is something you understand or something you have memorised as an arbitrary restriction. Expect the alias question as the follow-up.

Any comparison with NULL is unknown, not true - so rows with NULL are dropped by both = and !=.

Eli has no department. WHERE dept_id != 1 looks as though it should return everybody outside Engineering, which is three people, and it returns two - Eli is excluded, because NULL != 1 is not true. To include her you need OR dept_id IS NULL. This is the most expensive NULL mistake in everyday SQL.

SELECT COUNT(*) AS not_in_engineering
FROM employees
WHERE dept_id != 1

What it is really testing. Whether you reach for IS NULL rather than = NULL, and whether you know that != excludes NULLs too. The second half catches most people.

Order descending and skip one - with DISTINCT, so that a tie at the top does not cost you the answer.

DISTINCT is the part being tested. Without it, two people on the top salary would make the "second row" the same figure as the first. The window function DENSE_RANK() is the more general answer and the one to give if asked for the Nth.

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1

What it is really testing. Whether you handle ties. The naive LIMIT 1 OFFSET 1 is right only when the top salary is unique, and the interviewer will say "what if two people earn it?"

It puts them all in one group of their own, even though NULL does not equal NULL.

This is an exception worth knowing: in a comparison NULL is never equal to anything including itself, but GROUP BY, DISTINCT and UNION all treat NULLs as the same value for grouping. So the employee with no department becomes her own row in the result rather than disappearing.

SELECT dept_id, COUNT(*) AS people, SUM(salary) AS total
FROM employees
GROUP BY dept_id
ORDER BY dept_id

What it is really testing. Whether you know the inconsistency exists. It genuinely is inconsistent, and candidates who have only read the comparison rule guess wrongly.

UNION removes duplicates; UNION ALL keeps everything, and is faster because of it.

Removing duplicates means sorting or hashing the whole result, which is real work. If you know the two sets cannot overlap - or you want the duplicates - UNION ALL is both correct and cheaper. Reaching for UNION by habit is a common and invisible performance cost.

SELECT name FROM employees WHERE salary >= 75000
UNION ALL
SELECT name FROM employees WHERE dept_id = 1
ORDER BY name

What it is really testing. Whether you know UNION costs something. Most answers give the behavioural difference and stop short of the reason it matters.

Group by the column and keep the groups with more than one row.

GROUP BY collapses equal values into one row each, and COUNT(*) tells you how many went in. HAVING COUNT(*) > 1 then keeps only the ones that were duplicated. The same shape finds duplicate emails before you add a unique constraint, which is the real-world version of this question.

SELECT salary, COUNT(*) AS people
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1

What it is really testing. Fluency with GROUP BY plus HAVING together. It is the most reused pattern in everyday SQL and a reliable warm-up question.

It computes across a set of rows without collapsing them - you keep every row and gain a column.

GROUP BY turns five employees into two department rows. A window function leaves all five and adds, say, their rank inside their department. That is what OVER (PARTITION BY ... ORDER BY ...) means: partition is the grouping, and the rows survive. Running totals and "rank within group" are the everyday uses.

SELECT name,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, name) AS rank_in_dept
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, rank_in_dept

What it is really testing. Whether you grasp that rows survive. "It is like GROUP BY but..." is the start of the right answer; the end of it is "without losing the rows".

When you need a single aggregate to compare against, or you only want rows from one table.

Comparing each salary against the overall average is natural as a subquery and awkward as a join, because the average is one number rather than a table to match against. Where a join would work too, prefer it - a correlated subquery that re-runs per row is the classic accidental slow query.

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY name

What it is really testing. Whether you know correlated subqueries can be expensive. The syntax half is easy; the performance half is what distinguishes the answers.

DELETE removes rows and can be filtered and rolled back; TRUNCATE empties the whole table quickly; DROP removes the table itself.

DELETE is logged row by row, so it is slow on big tables but works inside a transaction and takes a WHERE clause. TRUNCATE deallocates the data instead and usually resets auto-increment, but takes no WHERE and in many databases cannot be rolled back. DROP leaves no table at all.

What it is really testing. Whether you know which ones you can undo. This question exists because the wrong choice on a live database is unrecoverable.