SQL Interview Questions with Answers (2026)

SQL shows up in almost every data, analytics and backend interview, and it’s where a lot of strong candidates quietly fail — not because the questions are hard, but because they haven’t said the answer out loud recently. Below are 15 real questions, ordered from beginner to advanced, each with a concise answer or query you can rehearse.

Beginner SQL questions

1. What’s the difference between WHERE and HAVING? WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY runs. You can’t use an aggregate like COUNT(*) in WHERE, but you can in HAVING.

2. Explain the main types of JOIN.

• INNER JOIN — only rows matching in both tables.
• LEFT JOIN — all left rows, with NULLs where the right has no match.
• RIGHT JOIN — the mirror of left.
• FULL OUTER JOIN — all rows from both sides, matched where possible.
• CROSS JOIN — every combination (Cartesian product).

3. What’s the difference between UNION and UNION ALL? UNION combines two result sets and removes duplicates (which costs a sort/dedupe); UNION ALL keeps everything, including duplicates, and is faster. Use UNION ALL unless you specifically need distinct rows.

4. Find the second-highest salary in an employees table.

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

A cleaner, tie-safe version uses a window function (see Q12).

5. How do NULL values behave in comparisons? NULL means “unknown,” so NULL = NULL is not true — it’s unknown. Use IS NULL / IS NOT NULL, and remember that COUNT(column) skips nulls while COUNT(*) doesn’t.

Intermediate SQL questions

6. Write a query to find duplicate emails in a users table.

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

7. What’s the difference between DELETE, TRUNCATE and DROP? DELETE removes rows (with a WHERE filter) and can be rolled back; it logs each row. TRUNCATE removes all rows fast, resets identity counters, and usually can’t be filtered. DROP removes the entire table structure.

8. Get the number of orders per customer, including customers with zero orders.

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

The LEFT JOIN plus COUNT(o.order_id) (not COUNT(*)) is what correctly returns 0 for customers with no orders.

9. What is an index, and what’s the trade-off? An index is a sorted data structure (usually a B-tree) that speeds up reads on indexed columns. The trade-off: it slows down INSERT/UPDATE/DELETE (the index must be maintained) and uses disk. Index columns you filter, join or sort on frequently — not everything.

10. Explain the difference between a clustered and a non-clustered index. A clustered index determines the physical order of rows on disk — a table has at most one. A non-clustered index is a separate structure pointing back to the rows, and you can have many.

11. Write a query to find each department’s highest-paid employee.

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

Advanced SQL questions

12. Find the second-highest salary using a window function.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) t
WHERE dr = 2;

DENSE_RANK handles ties correctly — if two people share the top salary, the next distinct value is still rank 2.

13. What’s the difference between RANK, DENSE_RANK and ROW_NUMBER?

• ROW_NUMBER — unique sequential numbers, no ties (1,2,3,4).
• RANK — ties share a number, then it skips (1,1,3,4).
• DENSE_RANK — ties share a number, no gap (1,1,2,3).

14. Write a running total of daily sales.

SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

15. What is a CTE, and why use one over a subquery? A Common Table Expression (WITH name AS (...)) is a named, temporary result set. It improves readability, lets you reference the same logic multiple times, and enables recursion (e.g., walking an org hierarchy). Functionally similar to a subquery, but far easier to read and maintain in complex queries.

16. How would you optimise a slow query? First, read the query plan with EXPLAIN (or EXPLAIN ANALYZE) and look for full table scans where an index could help. Then: add indexes on columns used in WHERE, JOIN and ORDER BY; select only the columns you need instead of SELECT *; avoid functions on indexed columns in the WHERE clause (they prevent index use); filter early; and check whether a JOIN is accidentally producing a Cartesian explosion. Naming EXPLAIN first is the answer that signals real experience.

17. What’s the order in which a SQL query is logically executed? Not the order you write it. The logical sequence is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This is why you can’t reference a SELECT alias in WHERE (the alias doesn’t exist yet) but can in ORDER BY (which runs after SELECT). Knowing this clears up a lot of “why doesn’t this work” confusion.

How to answer SQL questions well

Three habits separate strong candidates: state your assumptions about the schema before writing, name the trade-off (an index speeds reads but slows writes), and mention edge cases (nulls, ties, empty groups). Interviewers are scoring your reasoning, not just whether the query runs.

Rehearse these out loud — saying a query is very different from reading one. Practise full SQL rounds with OnJob’s AI mock interviews and get a confidence score on your explanations, then create a free account to find matched data and backend roles. If you’re targeting data jobs, our guide on how to become a data analyst in India pairs perfectly with this list.

FAQ

What SQL topics are most asked in interviews in 2026? Joins, GROUP BY with HAVING, finding duplicates and second-highest values, indexes and their trade-offs, and window functions like RANK, DENSE_RANK and running totals. Data roles lean harder on window functions and query optimisation.

What’s the difference between WHERE and HAVING in SQL? WHERE filters individual rows before grouping and can’t use aggregate functions; HAVING filters groups after GROUP BY and is designed to use aggregates like COUNT(*) or SUM().

Should I use a subquery or a CTE in interviews? Either works, but CTEs (WITH ... AS) are usually preferred for complex logic because they’re more readable, reusable within the query, and support recursion — which signals maturity to interviewers.

Free OnJob tools & guides

Interview questions by role Real questions + what a strong answer covers. Free mock tests Practice and benchmark before the real thing. ATS resume checker Clear the filters before you interview.