5 SQL Queries Interview Questions That Reveal Real Skill

5 SQL Queries Interview Questions That Reveal Real Skill

The best SQL queries interview questions are short, common in real work, and open to more than one correct answer, so you can see how a candidate thinks. Five cover most of what matters: second highest salary, duplicate records, INNER JOIN vs LEFT JOIN, WHERE vs HAVING, and top earner per group. Recruiters screening analysts and junior backend hires get the most from the first four. Hiring managers for senior data roles should lean on the last one.

Most SQL screens fail for one reason. The interviewer cannot tell a memorized answer from real understanding, and a non-technical recruiter has even less to go on. Each item below gives you the question, a working query, and the points a strong answer includes. You can judge depth, not just whether the output looks right. Candidates preparing for an interview can use the same set as a practice list, then move on to a longer bank of SQL interview questions. The queries use standard SQL and simple tables such as employees, candidates, and applications.

Olibr helps recruiters find and shortlist technical talent, including SQL developers, so this screen is written for the people running those first calls.

1. Second highest salary

The second highest salary question checks whether a candidate can handle ties and empty results, not just write a subquery. It appears in almost every set of SQL queries interview questions because it has several valid solutions and one common trap. That makes it a fast filter for freshers and mid-level analysts.

A good second-highest-salary answer survives duplicate salaries and a table with only one row.

Sample query and answer

Start with the subquery version, which excludes the top salary and takes the maximum of what remains. Then ask for the Nth highest version, which uses DENSE_RANK so the candidate changes one number.

-- Second highest salary
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Nth highest salary (here N = 2)
SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

What a strong answer includes

Listen for tie handling and portability before you move on. These points separate a memorized answer from an understood one.

• The candidate explains why DENSE_RANK is safer than ROW_NUMBER when two people share the top salary.
• The candidate knows the first query returns NULL when only one distinct salary exists, while the ranked query returns no rows.
• The candidate mentions that SELECT DISTINCT ... ORDER BY salary DESC LIMIT 1 OFFSET 1 works in MySQL and PostgreSQL, but SQL Server needs TOP or OFFSET FETCH.

2. Find duplicate records

Finding duplicates is a GROUP BY and HAVING exercise, and it tests whether the candidate asks which columns define a duplicate. Recruiters know the real-world version well. The same candidate gets entered twice in a database with a slightly different phone number or spelling.

The first good sign is a candidate who asks what counts as a duplicate before typing anything.

Sample query and answer

Group by the column that should be unique, count the rows, and keep groups larger than one. Then ask how to delete the extras while keeping one row per group.

SELECT email, COUNT(*) AS occurrences
FROM candidates
GROUP BY email
HAVING COUNT(*) > 1;

DELETE FROM candidates
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM candidates
  ) numbered
  WHERE rn > 1
);

What a strong answer includes

Good candidates show caution with deletes and think about prevention, not only cleanup.

• The candidate clarifies the matching columns first, for example email alone or email plus phone.
• The candidate numbers rows with ROW_NUMBER and PARTITION BY, so one copy survives instead of all copies being deleted.
• The candidate runs the logic as a SELECT before the DELETE and suggests a unique constraint to stop repeats.

3. INNER JOIN vs LEFT JOIN

An INNER JOIN returns only rows that match on both sides, while a LEFT JOIN keeps every row from the left table and fills gaps with NULL. The best way to test it is a business question, not a definition. Ask for candidates who never applied to any job. A strong candidate reaches for a LEFT JOIN with an IS NULL filter within a minute.

A candidate who can explain joins through a real question understands them better than one who recites diagrams.

Sample query and answer

Join applications to candidates, keep unmatched candidates, and filter on the missing key. This pattern is called an anti-join. It is also the cleanest way to see whether the candidate understands what NULL means after a join.

SELECT c.candidate_id, c.name
FROM candidates c
LEFT JOIN applications a
  ON a.candidate_id = c.candidate_id
WHERE a.application_id IS NULL;

What a strong answer includes

Pay attention to filter placement and alternatives. Those two details expose how much production SQL the candidate has written.

• The candidate knows a right-table filter in WHERE can silently turn a LEFT JOIN into an INNER JOIN, while the same filter in ON keeps unmatched rows.
• The candidate offers NOT EXISTS as an alternative and says when it reads more clearly.
• The candidate expects a follow-up on self joins, such as employees who earn more than their manager.

4. WHERE vs HAVING

WHERE filters individual rows before grouping, and HAVING filters groups after aggregation. Almost every candidate can recite that. The real test is whether they can write a query that needs both and explain why each condition sits where it does. This is one of the most repeated SQL queries interview questions for freshers, so a rehearsed answer is common.

If a candidate can say what runs before GROUP BY and what runs after, they understand the question.

Sample query and answer

Ask for departments where the average salary exceeds 60,000, counting only people hired since 2020. The hire date condition is a row filter, so it belongs in WHERE. The average is an aggregate condition, so it belongs in HAVING.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department_id
HAVING AVG(salary) > 60000;

What a strong answer includes

Look for execution order and efficiency, not just the definition.

• The candidate states the logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
• The candidate explains that aggregate functions cannot appear in WHERE because the groups do not exist yet.
• The candidate moves every non-aggregate condition into WHERE, so fewer rows reach the grouping step.

5. Top earner in each department

The top earner per department question is where window functions show up, and it separates intermediate SQL from beginner SQL. A plain GROUP BY returns the maximum salary per department, but it cannot return the employee's name beside it. That gap is the point of the question.

Candidates who reach for PARTITION BY unprompted have usually written real reporting queries.

Sample query and answer

Rank employees inside each department, then keep rank 1. The ranking has to happen in a subquery or CTE, because window functions are not allowed in WHERE.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employees
) ranked
WHERE rnk = 1;

What a strong answer includes

The follow-up questions matter more than the first query. Check for tie awareness and a clear reason for the subquery.

• The candidate explains that RANK returns every tied top earner, while ROW_NUMBER returns exactly one per department.
• The candidate can adapt the query to the top three earners by changing the filter to rnk <= 3 and switching to DENSE_RANK if needed.
• The candidate can explain why the ranking sits in a subquery or CTE and not in the WHERE clause.

Putting the five questions to work

These five questions test ties, duplicates, joins, aggregation order, and window functions. Together they take about 30 minutes and tell you far more than a syntax quiz. Ask for the query first, then spend your time on the follow-ups, because that is where understanding shows. If you are the candidate, write each query from memory, then change the table and rerun it.

If you are the recruiter and need people to run this screen on, you can spend your interview time on candidates who already fit the role by choosing vetted SQL developers from Olibr's verified profiles.

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.

Browse jobsHow it works