Top SQL Interview Questions (With Answers)

Why interviewers ask this

SQL interview questions repeat across data roles, backend roles, and analytics roles because the same handful of query shapes cover nearly everything asked in practice. This guide collects the fifteen that come up most, grouped by the kind of thinking each one tests. For every question you get a working query, the result it produces, a note on performance, and, because the people setting these questions read guides too, a short note on what a strong answer actually signals.

The first five are covered in depth, including the less efficient alternative an interviewer is usually steering you away from. The remaining ten are tighter: the working query and the one insight that matters.

How interviewers read a SQL answer

1How do you find the second highest salary in a table?

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

This works for a simple case, but breaks down the moment you need the Nth highest for an arbitrary N, or need to handle ties correctly. The more general, and now more commonly expected, approach uses a window function:

SELECT salary AS second_highest
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

DENSE_RANK handles ties correctly (two people tied for highest salary both get rank 1, and the second-highest is still rank 2), and the same query generalises to any N by changing one number.

Evaluates: whether the candidate reaches for a window function once the problem generalises beyond "second," rather than stacking subqueries for every additional level.

2How do you find duplicate rows in a table?

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

This is correct and is what most interviewers expect first. The follow-up question is usually "now delete all but one copy of each duplicate," which needs a different tool:

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

The self-join keeps the row with the smaller id for each duplicate email and removes the rest. GROUP BY ... HAVING is O(n) with an index on the grouped column; the self-join delete is closer to O(n²) without one.

Evaluates: correctness under a follow-up. Finding duplicates is the easy half; deleting them safely (without deleting every copy) is where candidates often make a mistake.

3What is the difference between INNER JOIN and LEFT JOIN?

-- INNER JOIN: only rows with a match in both tables
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;


-- LEFT JOIN: all rows from orders, matched customer data where it exists
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

If a customer has been deleted but their order record remains, INNER JOIN silently drops that order from the results. LEFT JOIN keeps it, with c.name returned as NULL.

Evaluates: correctness. This question is less about syntax and more about whether the candidate can predict what happens to unmatched rows, which is the actual source of most join bugs in production code.

4How do you find employees who earn more than their manager?

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

A self-join, joining the table to itself, is the tool here, since manager and employee data live in the same table. The naive alternative uses a correlated subquery:

-- Works, but re-runs the subquery once per row
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT salary FROM employees m WHERE m.id = e.manager_id
);

Both return the same rows. The self-join runs as a single set operation; the correlated subquery re-executes for every row in the outer query, which gets expensive as the table grows.

Evaluates: recognising a self-join pattern instead of defaulting to a correlated subquery. This is one of the most common "same table, different role" questions in SQL interviews.

5How do you find the Nth highest value in any column, generically?

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = :n;

The correlated-subquery alternative, extended from Q1's naive approach, requires a new nested NOT IN clause for every additional level of N:

-- Extremely fragile for N > 2 or 3
SELECT MAX(salary) FROM employees
WHERE salary NOT IN (
    SELECT DISTINCT salary FROM employees
    ORDER BY salary DESC LIMIT :n - 1
);

Evaluates: whether the candidate treats "Nth highest" as one problem with a parameter, rather than a new problem for every value of N. This is the same underlying insight as Q1, tested at a level that makes the naive approach visibly break down.

6What's the difference between WHERE and HAVING?

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 70000;

WHERE filters rows before grouping; HAVING filters groups after aggregation. Evaluates: understanding query execution order, since WHERE can't reference an aggregate like AVG(salary) directly.

7How do you find total salary per department?

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Evaluates: basic GROUP BY fluency. Simple, but frequently asked as a warm-up before a harder aggregation question.

8How do you find employees with no assigned manager?

SELECT name
FROM employees
WHERE manager_id IS NULL;

Evaluates: knowing to use IS NULL rather than = NULL, which silently returns no rows in SQL since NULL isn't comparable with =.

9What's the difference between UNION and UNION ALL?

-- UNION: removes duplicate rows across both result sets
SELECT city FROM customers
UNION
SELECT city FROM suppliers;


-- UNION ALL: keeps every row, including duplicates
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Evaluates: knowing that UNION does an implicit deduplication pass, which costs performance. UNION ALL is faster whenever duplicates are acceptable or already known not to exist.

10How do you calculate a running total?

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Evaluates: reaching for SUM() OVER (ORDER BY ...) instead of a self-join or correlated subquery to accumulate values row by row.

11How do you find the highest-paid employee in each department?

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

Evaluates: using PARTITION BY to reset the ranking within each group, the core idea that separates window functions from a single global ORDER BY.

12What's the difference between a correlated and a non-correlated subquery?

-- Non-correlated: runs once, independent of the outer query
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);


-- Correlated: re-evaluated for every row of the outer query
SELECT name FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees WHERE department = e.department
);

Evaluates: recognising that a correlated subquery references a column from the outer query, which forces it to re-run per row, while a non-correlated subquery executes once regardless of table size.

13How do you find customers who have never placed an order?

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Evaluates: choosing NOT EXISTS over NOT IN, which is safer, since NOT IN returns unexpected empty results if the subquery's column contains even one NULL value.

14How do you get the top 3 records per group?

SELECT name, department, salary
FROM (
    SELECT name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn <= 3;

Evaluates: knowing that ROW_NUMBER, unlike RANK or DENSE_RANK, always assigns unique sequential numbers, which matters when the requirement is an exact count per group rather than a tie-inclusive one.

15What's the difference between a clustered and a non-clustered index?

A clustered index determines the physical order rows are stored on disk, so a table can have only one. A non-clustered index is a separate structure that points back to the row's location, so a table can have several. Evaluates: understanding that index choice affects both read performance and how expensive writes become, not just query speed in isolation.

How should you prepare for a SQL interview?

1. Run every query, don't just read it. Small syntax details (IS NULL versus = NULL, HAVING versus WHERE) are easy to misremember until you've hit the error once.
2. Say the trade-off out loud. Practise the one-sentence version: "a correlated subquery here would re-run per row, so I'd use a window function instead." That sentence is what most of these questions are actually testing.
3. Know your join types cold. More SQL interview mistakes come from picking the wrong join than from any other single cause, and it's almost always about what happens to unmatched rows.

Frequently asked questions

Who is this guide for, and what SQL experience does it assume? This guide is written for candidates preparing for intermediate to senior interviews at data, backend, or analytics roles. It assumes you already know basic SELECT, WHERE, GROUP BY, and simple joins; if you're completely new to SQL, start with basic syntax first, since several questions here (window functions, self-joins, correlated subqueries) build on that foundation rather than teaching it.

What are the most commonly asked SQL interview questions? Joins, GROUP BY/HAVING, and window functions for ranking or running totals account for most of what's asked. The fifteen here cover the patterns behind nearly all of them.

Do I need to know window functions for SQL interviews? Increasingly, yes. Questions that used to expect a correlated subquery (second highest, top N per group) now often expect a window-function answer, especially for intermediate and senior roles.

Is MySQL syntax different from PostgreSQL or SQL Server for these questions? The core logic is the same across all three. Syntax differences mostly appear in pagination (LIMIT in MySQL/Postgres versus TOP or OFFSET FETCH in SQL Server) and string functions, not in joins or window functions.

How important is query performance in a SQL interview? Often the deciding factor between a correct answer and a strong one. Naming why one approach avoids repeated table scans, unprompted, is a common signal interviewers listen for.

How long does it take to prepare for a SQL interview? If you're comfortable with basic syntax, a few focused weeks on joins, aggregation, and window functions covers the patterns behind most interview questions. The goal is pattern recognition, not memorising queries.

Related tutorials: SQL JOIN Interview Questions (With Examples) · SQL Query Practice Questions (Basic to Advanced) · SQL Subquery & Window Function Questions