SQL Query Interview Questions: 5 to Ask and Answer
The interview questions SQL queries revolve around come down to five patterns: finding the second highest salary, spotting duplicates, using joins to find unmatched rows, picking the top earner per department with a subquery, and joining a table to itself. Beginners should start with the first two. Candidates aiming at data engineer or backend roles need all five. Interviewers can use the same five to tell memorized answers from real understanding.
Most lists run to 30 or 80 questions. They bury the few that matter and rarely say what a good answer looks like. Candidates end up memorizing snippets, and recruiters end up with fuzzy scorecards.
This article gives you five questions with a working query, the traps, and the follow-ups for each. At Olibr, we help recruiters hire SQL developers, so each answer also notes what a hiring manager should listen for. Every query uses two tables: employees (id, name, email, salary, department_id, manager_id) and departments (id, name). They run on PostgreSQL and MySQL 8 unless noted.
1. Second highest salary
The question and the query
This is the most asked SQL query interview question because it has several valid answers. It tests subqueries, ranking, and how you think about ties. The simplest answer removes the top value, then takes the maximum of what is left:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
The MAX-of-the-rest approach returns NULL, not an error, when no second salary exists. The ranking version generalizes better:
SELECT DISTINCT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2;
Traps and follow-ups
The classic trap is LIMIT 1 OFFSET 1 without DISTINCT. It returns the second row, not the second distinct salary, so two people tied at the top give a wrong answer. Strong candidates also check what happens when the table has only one salary.
- Ask for the Nth highest. In the DENSE_RANK version, swap 2 for N.
- Ask why DENSE_RANK and not RANK or ROW_NUMBER. Only DENSE_RANK keeps ranks consecutive when values tie.
Ties are what separate a memorized answer from an understood one.
2. Finding duplicate rows
The question and the query
Duplicates show up in every real database, and this question checks whether you know how GROUP BY and HAVING work together. Group on the column that should be unique, then keep only groups larger than one:
SELECT email, COUNT(*) AS occurrences
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
WHERE filters rows before grouping, while HAVING filters groups after it. That difference is what the interviewer is really testing, and it is why COUNT(*) can never appear in WHERE.
Traps and follow-ups
Many candidates reach for DISTINCT, which hides duplicates instead of finding them. Another subtle point is NULL. GROUP BY treats all NULL emails as one group, so a table with two missing emails will report them as duplicates.
- Duplicates across several columns: list every column in GROUP BY.
- Delete duplicates but keep the lowest id: number rows with
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id)and delete those numbered above 1. The exact DELETE syntax differs by database.
DISTINCT hides duplicates. GROUP BY with HAVING finds them.
3. Finding unmatched rows with a join
The question and the query
This question tests the difference between INNER JOIN and LEFT JOIN, and it appears in nearly every set of SQL query interview questions. To list departments with no employees, keep every department, then keep only the rows where the join found no match:
SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;
The IS NULL test on the right table works because a LEFT JOIN fills unmatched columns with NULL. Check a column that can never be NULL in real data, such as the primary key.
Traps and follow-ups
The usual mistake is filtering the right table in WHERE, for example WHERE e.salary > 50000. That silently turns the LEFT JOIN into an INNER JOIN. Conditions on the right table belong in the ON clause.
- Rewrite the query with NOT EXISTS and compare readability.
- Explain FULL OUTER JOIN. MySQL does not support it, so you emulate it with a UNION of two LEFT JOINs.
A WHERE filter on the right table quietly cancels a LEFT JOIN.
4. Top earner per department
The question and the query
This question combines grouping with subqueries. A correlated subquery solves it by recomputing the department maximum for each employee row:
SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.salary = (
SELECT MAX(salary)
FROM employees
WHERE department_id = e.department_id
);
A correlated subquery references the outer row, so it runs once per row. A nested subquery that does not reference the outer query runs once. Ties return every top earner, which is usually what you want.
Traps and follow-ups
Many candidates write GROUP BY department_id and select the name too, which fails or returns arbitrary names. Window functions are the cleaner answer at scale, and interviewers often ask for them next.
- Top three per department: use
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)in a subquery and filterrnk <= 3. - Departments whose average salary beats the company average: GROUP BY with HAVING and a scalar subquery.
Correlated subqueries read well, but window functions scale better.
5. Comparing rows with a self join
The question and the query
Self joins confuse people because one table plays two roles. To find employees who earn more than their manager, join employees to itself under two aliases:
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
Think of e as the employee and m as the manager. The manager_id column points to the id of the same table, and the aliases make the two roles explicit.
Traps and follow-ups
The INNER JOIN drops employees whose manager_id is NULL, which is correct here because a top-level employee has no manager to out-earn. If the task is to list every employee with their manager's name, you need a LEFT JOIN instead.
- Find pairs of employees in the same department. Add
e1.id < e2.idso each pair appears once, not twice. - Find managers with at least three direct reports: GROUP BY manager_id with HAVING COUNT(*) >= 3.
Self joins are ordinary joins. You just have to name the same table twice.
Putting the five questions to work
If you are preparing, write each query from memory on a blank editor, then explain the trap out loud. Ties, NULLs, and the WHERE versus HAVING split are where most answers fail. Real fluency shows when you can adapt a query to a variation you have not seen.
If you are interviewing, use these five as a screening round. Ask for the query, then ask the follow-up. A candidate who raises ties unprompted is worth more than one who recites a perfect snippet.
If you are hiring rather than preparing, browse verified SQL developer profiles on Olibr and bring these five questions to your first call.
For engineers
Find work worth your time.
Live engineering roles across India and the US, matched to your stack. Build a profile recruiters actually discover.