SQL Query Interview Questions and Answers

Introduction

Preparing for an sql query interview questions and answers interview means more than memorizing syntax it means understanding how and why queries work, being able to explain results, and writing clean, efficient SQL on the spot. Below is a compact, high-value guide that covers common interview questions (basic → advanced), clear answers, sample queries, and practical tips you can use during interviews.

Quick tips before you start

• Talk through your thought process as you write queries. Interviewers value clear reasoning.
• Ask about the SQL dialect (MySQL, PostgreSQL, SQL Server, Oracle) and sample schema names before coding.
• Prefer readability: consistent casing, aliases, and indentation.
• Mention performance considerations (indexes, joins, explain plans) when relevant.

Basic (fundamental) questions

1. What is SQL?

Answer: SQL (Structured Query Language) is a language for managing and querying relational databases. It supports data definition (CREATE, ALTER), data manipulation (SELECT, INSERT, UPDATE, DELETE), and transaction control (COMMIT, ROLLBACK).

2. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before aggregation; HAVING filters groups after aggregation. Use WHERE for row-level conditions and HAVING when you need to filter based on aggregated values.

Example:

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE active = 1
GROUP BY department
HAVING COUNT(*) > 10;

3. How do INNER JOIN, LEFT JOIN, and RIGHT JOIN differ?

Answer:

• INNER JOIN returns rows with matching keys in both tables.
• LEFT JOIN returns all rows from the left table and matched rows from the right (NULL if no match).
• RIGHT JOIN returns all rows from the right table and matched rows from the left.

4. What is a primary key vs. unique key?

Answer: Primary key uniquely identifies rows in a table and cannot be NULL. Unique key enforces uniqueness but can (in some DBMS) allow one NULL (behavior varies).

Common interview SQL query questions (with answers and sample queries)

5. Find duplicate rows in a table (by a column)

Question: How do you find duplicate emails in a users table?
Answer & Query:

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

6. Delete duplicate rows but keep one

Answer (common approach using CTE & ROW_NUMBER in modern SQL):

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

(If DELETE with CTE is not supported, use a join on the CTE or temporary table.)

7. Select top N rows per group

Question: Get the top 3 salaries per department.
Answer (ROW_NUMBER):

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

8. Find the second highest salary

Answer (multiple ways):

-- Using DISTINCT and LIMIT (MySQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Using subquery (ANSI)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

9. How to pivot rows to columns?

Answer: Use CASE with aggregation for a simple pivot, or use PIVOT (SQL Server/Oracle) or crosstab (Postgres) for built-in solutions.

Example (sales by quarter):

SELECT product,
       SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
       SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2
FROM sales
GROUP BY product;

10. Explain EXPLAIN / EXPLAIN ANALYZE

Answer: EXPLAIN shows the query plan the optimizer will use (join order, index usage). EXPLAIN ANALYZE executes the query and shows actual runtime stats. Use these to diagnose performance bottlenecks.

Intermediate / performance-focused questions

11. When would you use an index? What are downsides?

Answer: Indexes speed up search, join, and ORDER BY operations. Downsides: slower writes (INSERT/UPDATE/DELETE), extra storage, and sometimes poor choice of index can hurt performance (e.g., indexing low-cardinality columns).

12. How to optimize a slow query?

Answer checklist:

• Check EXPLAIN plan for scans vs index seeks.
• Add appropriate indexes (covering indexes for frequently selected columns).
• Avoid SELECT *; select needed columns only.
• Reduce use of functions on indexed columns in WHERE clauses (they can prevent index usage).
• Break complex queries into temp tables or CTEs only when helpful.
• Consider denormalization if appropriate for read-heavy workloads.

13. What are transactions and isolation levels?

Answer: A transaction is a sequence of operations treated as a single unit (ACID). Isolation levels control visibility of uncommitted changes: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Higher isolation reduces anomalies but can increase locking and reduce concurrency.

Advanced questions

14. How do window functions differ from GROUP BY?

Answer: Window functions compute values across rows related to the current row without collapsing rows. GROUP BY aggregates rows into fewer rows. Window functions (e.g., ROW_NUMBER(), RANK(), SUM() OVER (...)) are ideal when you need per-row aggregates or rankings.

15. How to perform recursive queries? (e.g., hierarchical data)

Answer: Use recursive CTEs (common table expressions).

Example (get all descendants):

WITH RECURSIVE descendants AS (
  SELECT id, parent_id, name FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

16. Describe common join algorithms (nested loop, hash join, merge join)

Answer:

• Nested loop: loops over outer rows and finds matches in inner; simple but can be slow for large sets.
• Hash join: builds a hash table on smaller input and probes with the other good for large, unsorted data.
• Merge join: requires both inputs sorted on join key; efficient for large, already-sorted datasets.

Practical “on-the-spot” scenario questions with approach

17. If asked to combine two tables where some columns overlap, what do you ask?

Answer: Clarify desired behavior: should overlapping columns be prioritized from one table, merged, or should duplicates be removed? Confirm primary/foreign keys and expected row counts (to pick efficient join strategy).

18. How to handle NULLs in comparisons?

Answer: NULL is unknown; = NULL returns false. Use IS NULL / IS NOT NULL for direct checks. Use COALESCE(column, default_value) to replace NULLs when needed.

Common gotchas & best practices

• Avoid ORDER BY without LIMIT on large tables in production queries unless necessary.
• Be explicit about JOIN types accidental CROSS JOIN can explode row counts.
• Use aliases for readability, especially with multiple table joins (e, o, c).
• For pagination, prefer keyset pagination (WHERE + ORDER BY on indexed key) over OFFSET for large datasets.
• When showing results in interviews, include sample output and explain edge cases (empty sets, NULLs, ties).

Sample mini mock interview — 5 questions to practice

1. Write a query to get users who registered in the last 30 days.
2. Find customers with no orders.
3. Return total sales by month for the past year.
4. Remove duplicate rows while keeping the earliest created record.
5. Explain how you would investigate a suddenly slow query in production.

(Practicing writing both SQL and the spoken explanation will boost interview performance.)

Closing — how to use this article

• Study the sample queries and practice on a local database (SQLite, Postgres, or MySQL).
• Be ready to adapt queries to the DBMS the interviewer uses.
• Practice explaining not just what your query does but why you chose that approach, and what you’d change if performance or scale were a concern.