Top 25 SQL Query Interview Questions & Answers (2026 Edition)

In almost every technical interview for software engineering, data engineering, application support, and DevOps roles, SQL questions are virtually guaranteed. While theoretical questions about ACID properties are common, interviewers judge your practical capability by making you write raw SQL queries on a whiteboard or shared editor.

Here are the top SQL query interview questions with clear explanations, schemas, and optimal queries.

---

Sample Schema Reference

For the queries below, assume the following standard tables:

```sql

-- Employees Table

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

salary DECIMAL(10,2),

department_id INT,

manager_id INT,

hire_date DATE

);

-- Departments Table

CREATE TABLE Department (

department_id INT PRIMARY KEY,

department_name VARCHAR(50)

);

```

---

1. Finding the Nth Highest Salary

This is the single most frequently asked SQL query question in Indian IT interviews.

Approach 1: Using DENSE_RANK() Window Function (Recommended for Modern SQL)

```sql

WITH RankedSalaries AS (

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank_val

FROM Employee

)

SELECT DISTINCT salary

FROM RankedSalaries

WHERE rank_val = 2; -- Change 2 to N

```

*Why DENSE_RANK() instead of ROW_NUMBER() or RANK()?*

If two employees earn the same top salary of 100,000, DENSE_RANK() gives both rank 1, and the next salary gets rank 2. RANK() would skip to rank 3, which causes bugs.

Approach 2: Using Correlated Subquery (Universal ANSI SQL)

```sql

SELECT DISTINCT salary

FROM Employee e1

WHERE 2 = (

SELECT COUNT(DISTINCT salary)

FROM Employee e2

WHERE e2.salary >= e1.salary

);

```

---

2. Finding Duplicate Records in a Table

Query: Find duplicate email addresses

```sql

SELECT email, COUNT(*) as count

FROM Users

GROUP BY email

HAVING COUNT(*) > 1;

```

Follow-Up: How to delete duplicate records while keeping one?

```sql

WITH CTE AS (

SELECT user_id,

ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) as rn

FROM Users

)

DELETE FROM Users

WHERE user_id IN (

SELECT user_id FROM CTE WHERE rn > 1

);

```

---

3. Department-Wise Highest Salary

Query: Find the employee who earns the highest salary in each department

```sql

WITH RankedDeptSalaries AS (

SELECT emp_id, first_name, salary, department_id,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk

FROM Employee

)

SELECT d.department_name, r.first_name, r.salary

FROM RankedDeptSalaries r

JOIN Department d ON r.department_id = d.department_id

WHERE r.rnk = 1;

```

---

4. Employees Earning More Than Their Managers (Self-Join)

```sql

SELECT e.first_name AS EmployeeName,

e.salary AS EmployeeSalary,

m.first_name AS ManagerName,

m.salary AS ManagerSalary

FROM Employee e

JOIN Employee m ON e.manager_id = m.emp_id

WHERE e.salary > m.salary;

```

---

5. Finding Departments with No Employees

```sql

-- Approach 1: LEFT JOIN with NULL check (Efficient)

SELECT d.department_name

FROM Department d

LEFT JOIN Employee e ON d.department_id = e.department_id

WHERE e.emp_id IS NULL;

-- Approach 2: NOT EXISTS

SELECT d.department_name

FROM Department d

WHERE NOT EXISTS (

SELECT 1 FROM Employee e WHERE e.department_id = d.department_id

);

```

---

6. Difference Between WHERE and HAVING

Interviewers love testing this conceptual boundary:

• `WHERE` clause: Filters individual rows before any aggregation happens. Cannot be used with aggregate functions like SUM(), AVG(), or COUNT().
• `HAVING` clause: Filters aggregated groups after the GROUP BY operation has taken place.

Example:

```sql

SELECT department_id, AVG(salary) as avg_sal

FROM Employee

WHERE salary > 30000 -- Filters individual salaries before grouping

GROUP BY department_id

HAVING AVG(salary) > 60000; -- Filters departments after average calculation

```

---

7. Understanding SQL Window Functions

Master the distinction between these three window functions:

• `ROW_NUMBER()`: Generates a sequential integer (1, 2, 3, 4) regardless of ties.
• `RANK()`: Assigns identical rank for ties, but skips subsequent ranks (1, 2, 2, 4).
• `DENSE_RANK()`: Assigns identical rank for ties, and does NOT skip ranks (1, 2, 2, 3).
• `LEAD()` and `LAG()`: Fetches value from subsequent or preceding row without performing a self-join. Ideal for year-over-year revenue comparisons.

---

8. Query Optimization Tips for Interviews

When asked *"How would you optimize a slow-running SQL query?"*, follow this structured checklist:

1. Explain Plan: Run EXPLAIN ANALYZE to check whether the database is performing full table scans or index scans.
2. Indexing: Ensure columns used in WHERE, JOIN, and ORDER BY clauses have appropriate B-Tree or Hash indexes. Avoid over-indexing on write-heavy tables.
3. Avoid `SELECT *`: Only fetch required columns to minimize I/O and network overhead.
4. Avoid leading wildcards: Queries like WHERE name LIKE '%john' cannot utilize standard B-Tree indexes and trigger full scans.
5. Use `EXISTS` instead of `IN` for subqueries: EXISTS stops scanning as soon as the first match is found.