Top SQL Interview Questions and Answers (2026)

Beginner Level Questions

Q1: Write a query to find the second highest salary from an Employee table

This is the most classic SQL interview question in India. Every interviewer has asked it at least once.

Table structure:

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    [salary](/salary/cost-of-living-mumbai-fresher-salary) DECIMAL(10,2)
);

Solution using LIMIT/OFFSET:

SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Solution using subquery (if LIMIT/OFFSET is not allowed):

SELECT MAX(salary) FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Follow-up: What if there are duplicate salaries? The DISTINCT keyword handles that. Without it, LIMIT 1 OFFSET 1 might return the same salary as the first if there are duplicates.

Real-world context: This tests whether you understand ordering and can handle edge cases like duplicates.

Q2: Write a query to find employees who earn more than their manager

Table structure:

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2),
    manager_id INT
);

Solution using self-JOIN:

SELECT e1.emp_name AS employee_name, e1.salary AS employee_salary
FROM Employee e1
JOIN Employee e2 ON e1.manager_id = e2.emp_id
WHERE e1.salary > e2.salary;

Real-world context: This tests your understanding of self-joins, which appear frequently in hierarchical data scenarios like org charts, categories, and referral systems.

Q3: Difference between WHERE and HAVING

This is a conceptual question that tests your SQL fundamentals.

WHERE	HAVING
Filters rows before grouping	Filters groups after aggregation
Cannot use aggregate functions	Can use aggregate functions (SUM, COUNT, AVG)
Used with SELECT, UPDATE, DELETE	Used only with GROUP BY
Faster since it reduces rows early	Operates on already-grouped data

Example:

-- WHERE: Find departments with more than 10 employees in the Sales location
SELECT department_id, COUNT(*)
FROM Employee
WHERE location = 'Sales'
GROUP BY department_id;

-- HAVING: Find departments with more than 10 employees total
SELECT department_id, COUNT(*)
FROM Employee
GROUP BY department_id
HAVING COUNT(*) > 10;

Q4: Find duplicate records in a table

Table:

CREATE TABLE Users (
    user_id INT,
    email VARCHAR(255)
);

Solution:

SELECT email, COUNT(*)
FROM Users
GROUP BY email
HAVING COUNT(*) > 1;

To get the full duplicate records:

SELECT *
FROM Users
WHERE email IN (
    SELECT email
    FROM Users
    GROUP BY email
    HAVING COUNT(*) > 1
);

Intermediate Level Questions

Q5: What are the different types of JOINs? Explain with examples.

This question tests your understanding of one of SQL's most important concepts.

INNER JOIN — Returns only matching rows from both tables

SELECT e.emp_name, d.department_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id;

LEFT JOIN — Returns all rows from left table, matching rows from right (NULL for non-matches)

SELECT e.emp_name, d.department_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

RIGHT JOIN — Returns all rows from right table, matching rows from left

SELECT e.emp_name, d.department_name
FROM Employee e
RIGHT JOIN Department d ON e.dept_id = d.dept_id;

FULL OUTER JOIN — Returns all rows from both tables

SELECT e.emp_name, d.department_name
FROM Employee e
FULL OUTER JOIN Department d ON e.dept_id = d.dept_id;

CROSS JOIN — Cartesian product (every row from A �- every row from B)

SELECT e.emp_name, d.department_name
FROM Employee e
CROSS JOIN Department d;

Real-world logic: Most interviewers will ask you to solve a problem and then say "now convert this LEFT JOIN to INNER JOIN and explain how results change." Understand the difference practically.

Q6: Write a query to find the top 3 highest-paid employees in each department

Solution using window functions:

WITH RankedEmployees AS (
    SELECT
        emp_name,
        department_id,
        salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM Employee
)
SELECT emp_name, department_id, salary
FROM RankedEmployees
WHERE salary_rank <= 3;

Solution without window functions:

SELECT e1.emp_name, e1.department_id, e1.salary
FROM Employee e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employee e2
    WHERE e2.department_id = e1.department_id
    AND e2.salary > e1.salary
) < 3
ORDER BY e1.department_id, e1.salary DESC;

Real-world context: This is called a "top N per group" problem. It appears in dashboards, reporting, and analytics everywhere.

Q7: What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

These window functions are frequently confused. Here's the difference with an example:

SELECT
    emp_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM Employee;

Sample output for salaries: 100, 90, 90, 80

Name	Salary	ROW_NUMBER	RANK	DENSE_RANK
A	100	1	1	1
B	90	2	2	2
C	90	3	2	2
D	80	4	4	3

ROW_NUMBER gives a sequential number with no ties, meaning every row gets a unique number. RANK assigns the same rank for ties but skips numbers after ties, while DENSE_RANK also gives the same rank for ties but never skips a number.

Q8: Explain the difference between UNION and JOIN

UNION combines rows vertically by stacking results, while JOIN combines columns horizontally side by side. With UNION, both queries must have the same number and order of columns, and it removes duplicates by default (use UNION ALL to keep them). JOINs, on the other hand, can work with different columns and do not remove duplicates. UNION is used to merge similar data from different sources, whereas JOIN is used to combine related data from different tables.

Example of UNION:

SELECT emp_name, 'Employee' AS type FROM current_employees
UNION ALL
SELECT emp_name, 'Former' AS type FROM former_employees;

Advanced Level Questions

Q9: Write a CTE (Common Table Expression) to calculate running total

WITH SalesCTE AS (
    SELECT
        sale_date,
        amount,
        SUM(amount) OVER (ORDER BY sale_date) AS running_total
    FROM Sales
)
SELECT * FROM SalesCTE;

Without window functions (using self-join):

SELECT
    s1.sale_date,
    s1.amount,
    SUM(s2.amount) AS running_total
FROM Sales s1
JOIN Sales s2 ON s2.sale_date <= s1.sale_date
GROUP BY s1.sale_date, s1.amount
ORDER BY s1.sale_date;

Q10: Write a query to find users who logged in on consecutive days

Table:

CREATE TABLE LoginHistory (
    user_id INT,
    login_date DATE
);

Solution:

WITH LoginCTE AS (
    SELECT
        user_id,
        login_date,
        LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date
    FROM LoginHistory
)
SELECT DISTINCT user_id
FROM LoginCTE
WHERE DATEDIFF(login_date, prev_login_date) = 1;

Alternative using difference of row numbers:

WITH RankedLogins AS (
    SELECT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
    FROM LoginHistory
),
Groups AS (
    SELECT
        user_id,
        login_date,
        DATEADD(day, -rn, login_date) AS group_date
    FROM RankedLogins
)
SELECT user_id
FROM Groups
GROUP BY user_id, group_date
HAVING COUNT(*) >= 2;

Q11: Explain query execution order in SQL

This tests your deep understanding of how SQL actually processes queries. The logical order of execution begins with FROM, where the tables are identified and JOINed together. Next comes WHERE, which filters rows, followed by GROUP BY to group the remaining rows. HAVING then filters those groups before SELECT chooses the columns and computes expressions. ORDER BY sorts the results, and LIMIT/OFFSET finally paginates the output. This is why you cannot use column aliases from SELECT in WHERE, because WHERE runs before SELECT.

Q12: How would you optimize a slow SQL query?

Interviewers love this question because it tests practical experience, not textbook knowledge. The first step is to identify the bottleneck by using EXPLAIN to see the query plan.

-- Use EXPLAIN to see query plan
EXPLAIN ANALYZE SELECT * FROM Orders WHERE customer_id = 123;

From there, common optimization strategies include adding indexes, especially on columns used in WHERE, JOIN, and ORDER BY clauses. Covering indexes that include all needed columns can avoid table lookups entirely. You should avoid SELECT * and only select the columns you actually need. Using EXISTS instead of IN for subqueries when checking existence can also improve performance. Limit the dataset early by applying WHERE filters to avoid processing unnecessary rows. Large tables benefit from partitioning by date, region, or other logical boundaries. Avoid functions in WHERE clauses, since something like WHERE DATE(created_at) = '2026-01-01' prevents index usage, whereas a range condition using WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02' works far better. Finally, use JOINs instead of subqueries when possible, though modern optimizers often handle both well.

Real-world example:

-- Slow: function on column prevents index usage
SELECT * FROM Orders WHERE YEAR(order_date) = 2026;

-- Fast: range condition uses index
SELECT * FROM Orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Q13: Write a query to find the median salary

WITH RankedSalaries AS (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
        COUNT(*) OVER () AS total_count
    FROM Employee
    GROUP BY salary
)
SELECT AVG(salary) AS median_salary
FROM RankedSalaries
WHERE rn IN (FLOOR((total_count + 1) / 2.0), CEIL((total_count + 1) / 2.0));

Q14: Difference between clustered and non-clustered index

A clustered index determines the physical order of data storage, and you can only have one per table since the data can only be sorted one way. It is faster for range queries like BETWEEN and comparisons. Its leaf nodes contain the actual data. A non-clustered index, on the other hand, is a separate structure from the data. You can have multiple per table (up to 999 in SQL Server), and it is faster for exact match lookups. Its leaf nodes contain pointers to the data rather than the data itself. By default, the primary key creates a clustered index, while non-clustered indexes are created explicitly with CREATE INDEX.

Q15: Write a query to find gaps in a sequence (e.g., missing order IDs)

WITH OrderCTE AS (
    SELECT
        order_id,
        LEAD(order_id) OVER (ORDER BY order_id) AS next_order_id
    FROM Orders
)
SELECT (order_id + 1) AS gap_start,
       (next_order_id - 1) AS gap_end
FROM OrderCTE
WHERE next_order_id - order_id > 1;

Practice Problems for Self-Study

Problem 1: Employee Attendance

Table: Attendance(emp_id, date, status) where status is 'Present', 'Absent', 'Leave'. Find employees with 5+ consecutive absences.

Problem 2: Product Sales

Table: Sales(sale_id, product_id, sale_date, amount). Find products whose sales increased month-over-month for 3 consecutive months.

Problem 3: Customer Retention

Table: Orders(order_id, customer_id, order_date). Calculate the monthly customer retention rate (customers who ordered in month N who also ordered in month N+1).

Problem 4: Session Duration

Table: PageViews(user_id, page_url, timestamp). Calculate the average session duration per user (a session is 30 minutes of inactivity).

Solutions to Practice Problems

Problem 1 - Consecutive Absences:

WITH AttendanceCTE AS (
    SELECT
        emp_id, date, status,
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY date) AS rn
    FROM Attendance
    WHERE status = 'Absent'
),
GapAnalysis AS (
    SELECT
        emp_id, date,
        DATEADD(day, -rn, date) AS group_key
    FROM AttendanceCTE
)
SELECT emp_id, COUNT(*) AS consecutive_absences
FROM GapAnalysis
GROUP BY emp_id, group_key
HAVING COUNT(*) >= 5;

Problem 2 - Consecutive Monthly Increase:

WITH MonthlySales AS (
    SELECT
        product_id,
        FORMAT(sale_date, 'yyyy-MM') AS month,
        SUM(amount) AS total_sales
    FROM Sales
    GROUP BY product_id, FORMAT(sale_date, 'yyyy-MM')
),
SalesComparison AS (
    SELECT
        product_id, month, total_sales,
        LAG(total_sales) OVER (PARTITION BY product_id ORDER BY month) AS prev_month_sales,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY month) AS rn
    FROM MonthlySales
),
IncreasingMonths AS (
    SELECT product_id, month,
        CASE WHEN total_sales > prev_month_sales THEN 1 ELSE 0 END AS is_increase
    FROM SalesComparison
    WHERE prev_month_sales IS NOT NULL
)
SELECT product_id
FROM IncreasingMonths
GROUP BY product_id
HAVING SUM(is_increase) >= 3;

Quick Reference: SQL Cheat Sheet

Common Functions

String functions include CONCAT, SUBSTRING, LENGTH, REPLACE, TRIM, UPPER, and LOWER. Date functions cover DATEADD, DATEDIFF, GETDATE, FORMAT, YEAR, MONTH, and DAY. Aggregate functions are COUNT, SUM, AVG, MIN, and MAX. Window functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and NTILE. For conditional logic, you have CASE WHEN, COALESCE, and NULLIF.

Important Keywords

The essential keywords to know are DISTINCT, GROUP BY, HAVING, ORDER BY, and LIMIT/OFFSET for basic query control, UNION, INTERSECT, and EXCEPT for set operations, and WITH for CTEs, along with EXISTS, IN, BETWEEN, LIKE, and IS NULL.

Index Types

The main index types include Clustered, Non-clustered, Unique, Composite, Covering, and Full-text indexes.

Final Advice for SQL Interviews

Practice writing queries by hand, since most interviews use a whiteboard or shared document with no syntax highlighting. Think out loud and explain your approach before writing any code. Always test edge cases, considering what happens with NULLs, empty tables, or duplicate data. Know your database system, because MySQL, PostgreSQL, and SQL Server all have syntax differences, and you should know which one the company uses. Most importantly, optimize last, getting a working solution first and then discussing optimization afterward. SQL interviews test whether you can think in sets and manipulate data logically. Master these 15 questions, do 30 more on LeetCode by filtering for Database problems, and you will walk into any SQL interview in India with confidence.

Need more interview prep? Check out our guides on system design interviews, coding challenge strategies, and behavioral interview techniques.

Your Move

Record three answers using the STAR method: situation, task, action, result.
Replay each answer and check whether it is specific, concise, and tied to the role you want.
Rewrite the weakest answer, then practice it once more without reading notes.