SQL Interview Questions and Answers for 2026 — Complete Guide

Introduction

SQL (Structured Query Language) remains the most fundamental skill for any data professional. Whether you are applying for a Data Analyst, Data Engineer, Business Intelligence, or Data Scientist role, SQL proficiency is expected and tested in virtually every technical interview. In 2026, SQL interviews have evolved to include not just basic queries but complex window functions, CTEs, query optimization, and database design questions.

This comprehensive guide covers SQL Interview Questions and Answers for 2026, organized by difficulty level, to help you ace your next data interview.

SQL Basics — Interview Questions

1. What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation occurs, meaning it works on individual rows. HAVING filters after aggregation, working on grouped results. You cannot use aggregate functions (SUM, COUNT, AVG) in a WHERE clause, but you can in HAVING.

-- WHERE filters individual rows
SELECT * FROM employees WHERE salary > 50000;

-- HAVING filters after aggregation
SELECT department, AVG(salary) as avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

2. Explain the different types of SQL Joins with examples.

SQL supports several join types: INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right (NULL for non-matches). RIGHT JOIN returns all rows from the right table. FULL OUTER JOIN returns all rows from both tables. CROSS JOIN returns the Cartesian product (all combinations). SELF JOIN joins a table with itself.

SELECT e.name, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

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

UNION combines results from two queries and removes duplicates (slower, requires additional processing). UNION ALL combines results without removing duplicates (faster). Always use UNION ALL unless you specifically need to eliminate duplicates.

4. What is a Primary Key vs. Foreign Key?

A Primary Key uniquely identifies each row in a table — it must be unique and NOT NULL. A Foreign Key is a column that references the Primary Key of another table, establishing a referential integrity relationship between tables. One table can have one primary key but multiple foreign keys.

5. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows based on a WHERE clause, is logged, and can be rolled back. TRUNCATE removes all rows from a table quickly by deallocating pages, is minimally logged, and in most databases cannot be rolled back. DROP removes the entire table structure and all data permanently.

Intermediate SQL Interview Questions

6. What are Window Functions? Explain with examples.

Window functions perform calculations across a set of related rows (a window) without collapsing them into a single group. Unlike GROUP BY, window functions retain all rows in the result.

-- ROW_NUMBER: assigns unique sequential integers
SELECT name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;

-- Running Total
SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
FROM orders;

-- Lag (access previous row)
SELECT name, salary,
  LAG(salary, 1) OVER (ORDER BY hire_date) as previous_salary,
  salary - LAG(salary, 1) OVER (ORDER BY hire_date) as salary_change
FROM employees;

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

A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve readability, allow recursive queries, and can be referenced multiple times within the same query.

WITH ranked_employees AS (
  SELECT name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
  FROM employees
)
SELECT * FROM ranked_employees
WHERE salary_rank <= 3;  -- Top 3 earners per department

8. How do you find the Nth highest salary in SQL?

This is one of the most common SQL interview questions. There are multiple approaches:

-- Using DENSE_RANK (recommended)
WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
  FROM employees
)
SELECT salary FROM ranked WHERE rnk = 3;  -- 3rd highest salary

-- Using subquery (for 2nd highest)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

9. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

All three are window ranking functions but differ in how they handle ties:

• ROW_NUMBER(): Assigns unique sequential numbers — no ties (e.g., 1, 2, 3, 4)
• RANK(): Assigns same rank for ties but skips next ranks (e.g., 1, 2, 2, 4)
• DENSE_RANK(): Assigns same rank for ties without skipping (e.g., 1, 2, 2, 3)

10. How do you remove duplicate rows from a table?

-- Method 1: Using ROW_NUMBER CTE
WITH duplicates AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY name, email, department 
    ORDER BY id
  ) as rn
  FROM employees
)
DELETE FROM duplicates WHERE rn > 1;

-- Method 2: Using DISTINCT into a new table
SELECT DISTINCT * INTO employees_clean FROM employees;

Advanced SQL Interview Questions

11. What is a Self Join? Give a real-world example.

A self join is when a table is joined with itself. This is useful for hierarchical data, finding relationships within the same table, or comparing rows within a table.

-- Find employees and their managers (both in same table)
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- Find pairs of employees in the same city
SELECT e1.name, e2.name, e1.city
FROM employees e1
INNER JOIN employees e2 
ON e1.city = e2.city AND e1.id < e2.id;

12. What are Subqueries? Types of Subqueries?

A subquery is a query nested within another query. Types include: Scalar subquery (returns single value), Row subquery (returns single row), Column subquery (returns single column), Correlated subquery (references outer query — executed once per row), and Inline view/derived table (used in FROM clause).

13. How do you optimize a slow SQL query?

Query optimization strategies include: add appropriate indexes on join columns and WHERE clause columns; avoid SELECT * — specify only needed columns; use EXISTS instead of IN for subqueries; avoid functions on indexed columns in WHERE clause; analyze execution plans; partition large tables; use covering indexes; denormalize when appropriate for read-heavy workloads; and use CTEs or temp tables to break complex queries.

14. What is an Index? Types of Indexes?

An index is a database object that improves query retrieval speed at the cost of additional storage and slower writes. Main types: Clustered Index — physically sorts table data (one per table); Non-Clustered Index — separate structure pointing to data (multiple allowed); Composite Index — covers multiple columns; Unique Index — ensures uniqueness; Covering Index — includes all columns needed for a query.

15. Write a query to find customers who placed orders in every month of the year.

SELECT customer_id
FROM orders
WHERE YEAR(order_date) = 2025
GROUP BY customer_id
HAVING COUNT(DISTINCT MONTH(order_date)) = 12;

16. What is a Pivot Table in SQL? How do you create one?

-- Dynamic pivot: Monthly sales by product
SELECT product_name,
  SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) as Jan,
  SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) as Feb,
  SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) as Mar
  -- ... add more months
FROM sales
GROUP BY product_name;

17. Explain Normalization and its forms (1NF, 2NF, 3NF).

1NF (First Normal Form): Eliminate repeating groups; each column should contain atomic values; no duplicate rows. 2NF (Second Normal Form): Must be in 1NF; eliminate partial dependencies — every non-key column must depend on the entire primary key. 3NF (Third Normal Form): Must be in 2NF; eliminate transitive dependencies — non-key columns should not depend on other non-key columns.

18. What is a Stored Procedure? Advantages?

A stored procedure is a precompiled SQL code stored in the database that can be executed on demand. Advantages: reusability, security (users can execute without direct table access), performance (precompiled), reduced network traffic, and encapsulation of business logic.

19. Write a query to calculate a 7-day moving average.

SELECT 
  sale_date,
  daily_sales,
  AVG(daily_sales) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7day
FROM daily_sales_summary;

20. What is the difference between correlated and non-correlated subqueries?

A non-correlated subquery executes independently of the outer query and runs only once. A correlated subquery references the outer query and executes once for each row processed by the outer query — making it slower but more flexible.

SQL Performance Interview Questions

21. What is query execution plan? How do you use it?

A query execution plan shows how the SQL engine processes a query — which indexes are used, join methods, sort operations, and estimated costs. Use EXPLAIN (MySQL), EXPLAIN PLAN (Oracle), or SET STATISTICS IO (SQL Server) to view execution plans. Look for table scans (inefficient), key lookups, and high estimated costs to identify optimization opportunities.

Practical SQL Interview Tips

• Always start with clarifying questions before writing SQL
• Think through edge cases (NULLs, duplicates, empty tables)
• Consider multiple approaches and discuss trade-offs
• Practice writing SQL by hand without IDE assistance
• Know the most common aggregate and window functions by heart

Free SQL Resources

Download our comprehensive SQL e-books to further your preparation:

• 1000 SQL Interview Questions (Free Download)
• SQL Cheat Sheet — Quick Reference

Written by Ravikant Pandey, founder of DataInked.com — a platform dedicated to helping data professionals succeed in their careers through quality resources and practical knowledge.