Most Frequently Asked SQL Queries for Experienced Java Developers

SQL interviews for experienced Java developers are very different from fresher-level interviews. Interviewers expect strong knowledge in:

• Query optimization
• Complex joins
• Subqueries
• Performance tuning
• Real-world production scenarios
• Transaction handling
• Indexing
• Data consistency
• Scalability

For experienced developers, interviewers focus on:

• Writing optimized SQL queries
• Understanding execution flow
• Handling large datasets
• Production issue troubleshooting
• Real-time enterprise use cases

These queries are commonly asked in interviews for:

• Senior Java Developer
• Spring Boot Developer
• Backend Engineer
• Full Stack Developer
• Production Support Engineer
• Microservices Developer

1. Find the Second Highest Salary

One of the most frequently asked SQL interview questions.

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

Why Interviewers Ask This

Tests understanding of:

• Subqueries
• Aggregate functions
• Problem-solving ability

Optimized Approach Using DENSE_RANK()

Preferred in enterprise systems:

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

This approach handles:

• Duplicate salaries
• Large datasets
• Better readability

2. Find Duplicate Records in a Table

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

Real-World Usage

Used in:

• Data validation
• Production issue analysis
• Duplicate customer detection

3. Remove Duplicate Records Without Temporary Table

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

Interview Focus

Tests:

• Self join knowledge
• Data cleanup strategies
• Real-world DB maintenance

4. Find Employees Earning More Than Department Average Salary

Very common experienced-level query.

SELECT emp_name,
       department,
       salary
FROM employee e
WHERE salary >
(
    SELECT AVG(salary)
    FROM employee
    WHERE department = e.department
);

Why Important

Tests:

• Correlated subqueries
• Real-world reporting queries

5. Find Top 3 Highest Salaries Department Wise

Enterprise-level interview favorite.

SELECT *
FROM
(
    SELECT emp_name,
           department,
           salary,
           DENSE_RANK() OVER
           (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rnk
    FROM employee
) t
WHERE rnk <= 3;

Concepts Tested

• Window functions
• Partitioning
• Ranking functions

6. Find Employees Who Have Not Logged in for Last 30 Days

Production support scenario-based query.

SELECT *
FROM employee
WHERE last_login <
CURDATE() - INTERVAL 30 DAY;

Real-World Usage

Used in:

• User inactivity reports
• Security auditing
• Cleanup operations

7. Find Records Present in One Table but Missing in Another

SELECT customer_id
FROM customers
WHERE customer_id NOT IN
(
    SELECT customer_id
    FROM orders
);

Real-World Usage

Used for:

• Data reconciliation
• Failed transaction analysis
• Missing data detection

Optimized Version Using NOT EXISTS

Preferred for large datasets:

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS
(
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

8. Find Nth Highest Salary

SELECT salary
FROM
(
    SELECT salary,
           DENSE_RANK() OVER
           (ORDER BY salary DESC) AS rnk
    FROM employee
) t
WHERE rnk = 5;

Why Frequently Asked

Tests:

• Analytical thinking
• Window function knowledge

9. Fetch Latest Order for Each Customer

Very important real-world query.

SELECT *
FROM orders o
WHERE order_date =
(
    SELECT MAX(order_date)
    FROM orders
    WHERE customer_id = o.customer_id
);

Real-World Usage

Used in:

• Customer dashboards
• Analytics systems
• Latest transaction retrieval

10. Find Employees Working on Multiple Projects

SELECT emp_id,
       COUNT(project_id)
FROM employee_project
GROUP BY emp_id
HAVING COUNT(project_id) > 1;

Interview Focus

Tests:

• GROUP BY
• HAVING clause
• Many-to-many relationship handling

11. Write Query to Detect Slow Running Queries

MySQL:

SHOW PROCESSLIST;

OR:

SELECT *
FROM information_schema.processlist
WHERE COMMAND != 'Sleep';

Real-World Usage

Production support engineers use this heavily.

12. Find Deadlocks in MySQL

SHOW ENGINE INNODB STATUS;

Interview Focus

Experienced developers are expected to understand:

• Deadlocks
• Transaction blocking
• Lock contention

13. Find Total Sales Month Wise

SELECT MONTH(order_date) AS month,
       SUM(amount) AS total_sales
FROM orders
GROUP BY MONTH(order_date);

Real-World Usage

Used in:

• Reporting systems
• BI dashboards
• Analytics

14. Find Continuous Duplicate Values

Advanced SQL question.

SELECT DISTINCT num
FROM
(
    SELECT num,
           LAG(num) OVER (ORDER BY id) AS prev_num
    FROM logs
) t
WHERE num = prev_num;

Concepts Tested

• Window functions
• Analytical SQL
• Event sequence analysis

15. Find Running Total of Salary

SELECT emp_name,
       salary,
       SUM(salary) OVER
       (ORDER BY emp_id) AS running_total
FROM employee;

Real-World Usage

Used in:

• Financial reports
• Payroll systems
• Analytics

16. Find Employees with Same Salary

SELECT e1.emp_name,
       e2.emp_name,
       e1.salary
FROM employee e1
JOIN employee e2
ON e1.salary = e2.salary
AND e1.emp_id <> e2.emp_id;

17. Optimize a Slow Query

Interviewers may ask:

How would you optimize slow SQL queries?

Expected answers:

• Add indexes
• Avoid SELECT *
• Optimize joins
• Analyze execution plan
• Avoid full table scans
• Use pagination
• Partition large tables

18. Explain Execution Plan

Common production-level interview topic.

MySQL:

EXPLAIN SELECT * FROM employee
WHERE salary > 50000;

What Interviewers Expect

Understanding of:

• Index usage
• Query cost
• Full table scan
• Join execution

19. Find Highest Salary in Each Department

SELECT department,
       MAX(salary)
FROM employee
GROUP BY department;

Advanced Version

SELECT *
FROM employee e
WHERE salary =
(
    SELECT MAX(salary)
    FROM employee
    WHERE department = e.department
);

20. Pagination Query

Very important for backend developers.

MySQL:

SELECT *
FROM employee
LIMIT 10 OFFSET 20;

Real-World Usage

Used in:

• REST APIs
• UI pagination
• Search results

21. Find Missing IDs in Sequence

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

22. Difference Between EXISTS and IN

Frequently asked optimization question.

23. Explain Indexing Strategy

Experienced developers should know:

• Clustered index
• Non-clustered index
• Composite index
• Covering index

Interviewers often ask:

How do indexes improve performance?

24. Explain ACID Properties

Very common enterprise interview topic.

25. Difference Between DELETE, TRUNCATE, and DROP

Experienced developers are expected to know:

• Logging behavior
• Rollback support
• Performance difference
• Storage handling