SQL Interview Questions for 9+ Years Java Developers

This guide consolidates common SQL interview questions for experienced Java developers (9+ years), with solutions and explanations.

Topic Overview

flowchart TD
    A[Advanced SQL Interviews]
    A --> B[Query Writing]
    A --> C[Window Functions]
    A --> D[Joins]
    A --> E[Aggregations]
    A --> F[Performance]
    F --> G[Indexing]
    F --> H[Execution Plans]

1. Find the 2nd Highest Salary

Using a subquery:

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

Using DENSE_RANK:

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) rnk
    FROM Employee
) t
WHERE rnk = 2;

2. Find the Nth Highest Salary

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) rnk
    FROM Employee
) t
WHERE rnk = N;

3. Find Duplicate Records

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

4. Delete Duplicate Records

Keep the smallest ID.

DELETE e1
FROM Employee e1
JOIN Employee e2
ON e1.email = e2.email
AND e1.id > e2.id;

5. Find Employees Without a Department

SELECT e.*
FROM Employee e
LEFT JOIN Department d
ON e.dept_id = d.id
WHERE d.id IS NULL;

6. INNER JOIN vs LEFT JOIN

INNER JOIN

Returns only matching rows.

SELECT *
FROM Employee e
INNER JOIN Department d
ON e.dept_id = d.id;

LEFT JOIN

Returns all employees, even when no department exists.

SELECT *
FROM Employee e
LEFT JOIN Department d
ON e.dept_id = d.id;

JOIN Type	Returns
INNER JOIN	Matching rows only
LEFT JOIN	All left-table rows plus matches

7. Department-wise Highest Salary

SELECT dept_id,
       MAX(salary)
FROM Employee
GROUP BY dept_id;

8. Highest Paid Employee in Each Department

SELECT *
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (
               PARTITION BY dept_id
               ORDER BY salary DESC
           ) rn
    FROM Employee e
) x
WHERE rn = 1;

9. Running Total

SELECT id,
       salary,
       SUM(salary) OVER (ORDER BY id) AS running_total
FROM Employee;

10. Employees Earning More Than Department Average

SELECT *
FROM Employee e
WHERE salary >
(
    SELECT AVG(salary)
    FROM Employee
    WHERE dept_id = e.dept_id
);

11. Employees Joined in the Last 30 Days

Standard SQL:

SELECT *
FROM Employee
WHERE joining_date >= CURRENT_DATE - INTERVAL 30 DAY;

MySQL:

SELECT *
FROM Employee
WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

12. Find Consecutive Records

Note: num appearing 3+ times anywhere in the table is a different, easier question than 3 consecutive rows with the same num — the two queries below solve the actual “consecutive” version. The correct self-join solution:

SELECT DISTINCT l1.num
FROM Logs l1
JOIN Logs l2
ON l1.id = l2.id - 1
AND l1.num = l2.num
JOIN Logs l3
ON l2.id = l3.id - 1
AND l2.num = l3.num;

If you only needed “appears at least 3 times anywhere” (not necessarily consecutively), a simple GROUP BY/HAVING would do instead:

SELECT num
FROM Logs
GROUP BY num
HAVING COUNT(*) >= 3;

13. Top 3 Salaries in Each Department

SELECT *
FROM (
    SELECT e.*,
           DENSE_RANK() OVER (
               PARTITION BY dept_id
               ORDER BY salary DESC
           ) rnk
    FROM Employee e
) t
WHERE rnk <= 3;

14. Find Missing IDs

Example IDs:

1
2
3
5
6
8

SELECT t1.id + 1
FROM Employee t1
LEFT JOIN Employee t2
ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;

15. Customers Who Never Ordered

SELECT c.*
FROM Customers c
LEFT JOIN Orders o
ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

16. Monthly Sales

SELECT YEAR(order_date),
       MONTH(order_date),
       SUM(amount)
FROM Orders
GROUP BY YEAR(order_date),
         MONTH(order_date);

17. Pivot Query

SELECT
SUM(CASE WHEN dept='IT' THEN salary ELSE 0 END) AS IT,
SUM(CASE WHEN dept='HR' THEN salary ELSE 0 END) AS HR,
SUM(CASE WHEN dept='Finance' THEN salary ELSE 0 END) AS Finance
FROM Employee;

18. Employees Sharing the Same Salary

SELECT salary
FROM Employee
GROUP BY salary
HAVING COUNT(*) > 1;

19. ROW_NUMBER vs RANK vs DENSE_RANK

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC),
       RANK() OVER (ORDER BY salary DESC),
       DENSE_RANK() OVER (ORDER BY salary DESC)
FROM Employee;

Function	Behavior with Duplicate Values
ROW_NUMBER	Always unique sequence
RANK	Skips ranking numbers after ties
DENSE_RANK	No gaps in ranking

20. SQL Performance Questions

Improving Slow Queries

• Create indexes.
• Avoid SELECT *.
• Use pagination.
• Analyze execution plans.
• Avoid unnecessary joins.
• Partition large tables.
• Use appropriate data types.

Identify Slow Queries

EXPLAIN
SELECT *
FROM Employee
WHERE email = 'abc@test.com';

Clustered vs Non-Clustered Index

Clustered Index	Non-Clustered Index
Data stored physically in index order	Separate index structure
One per table	Multiple per table

Topics to Practice

1. Top-N per group
2. Window functions
3. Common Table Expressions (CTEs)
4. Recursive queries
5. Self joins
6. Pivot and unpivot
7. Ranking functions
8. Query optimization
9. Indexing
10. Execution plans
11. Partitioning
12. Transactions and isolation levels
13. Deadlocks
14. Pagination
15. Stored procedures
16. WHERE vs HAVING

Summary

• Senior SQL interviews focus on solving business problems efficiently rather than memorizing syntax.
• Window functions (ROW_NUMBER, RANK, DENSE_RANK) are frequently tested.
• Understanding joins, aggregation, ranking, and query optimization is essential.
• Interviewers also evaluate knowledge of indexing, execution plans, and performance tuning.
• Practicing real-world query patterns is key for Java developers with 9+ years of experience.