Top 20 SQL Interview Questions and Answers (2026)

SQL interviews test your ability to write queries, understand database concepts, and optimize performance. Here are the most frequently asked questions with concise answers.

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after GROUP BY. You cannot use aggregate functions in WHERE, but you can in HAVING.

SELECT department, COUNT(*) as cnt
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5;

2. Explain the difference between INNER JOIN and LEFT JOIN

INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table plus matching rows from the right. Unmatched right rows show NULL.

3. What is a subquery?

A query nested inside another query. Can appear in SELECT, FROM, WHERE, or HAVING clauses.

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

4. What are window functions?

Functions that perform calculations across a set of rows related to the current row, without collapsing them into a single output row like GROUP BY does.

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

5. Difference between DELETE, TRUNCATE, and DROP?

DELETE — removes specific rows, can be rolled back, logs each row
TRUNCATE — removes all rows, faster, minimal logging
DROP — removes the entire table structure and data

6. What is a CTE (Common Table Expression)?

A temporary named result set defined with WITH. Improves readability and allows recursion.

WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users;

7. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows. UNION ALL keeps all rows including duplicates. UNION ALL is faster.

8. How do you find the second highest salary?

-- Method 1: Subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: Window function
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rn
  FROM employees
) WHERE rn = 2;

9. What is an index and why use one?

An index is a data structure that speeds up data retrieval. Like a book index — it lets the database find rows without scanning the entire table. Trade-off: slower writes, more storage.

10. Explain GROUP BY

Groups rows with the same values into summary rows. Used with aggregate functions: COUNT, SUM, AVG, MIN, MAX.

11-20. More Common Questions

What is a primary key? — Unique identifier, NOT NULL, one per table
What is a foreign key? — Column referencing another table's primary key
What is normalization? — Organizing data to reduce redundancy
What is denormalization? — Adding redundancy for read performance
What is a view? — Virtual table defined by a query
What is a stored procedure? — Precompiled SQL code stored in the database
What is a trigger? — Automatic action on INSERT/UPDATE/DELETE
What is ACID? — Atomicity, Consistency, Isolation, Durability
What is a correlated subquery? — Subquery that references outer query columns
What is the execution order of SQL clauses? — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Practice Tools

Format your practice queries with the SQL Formatter and convert sample data to SQL with the CSV to SQL converter.