Infosys SP/DSE – 10 Most Common SQL Query-Based Interview Questions and Answers
1. Find the Second Highest Salary
Table: Employee
emp_id name salary
1 John 50000
2 Alice 70000
3 Bob 60000
4 Carol 70000
Efficient Query
SELECT MAX(salary) AS second_highest_salary
FROM Employee
WHERE salary < (
SELECT MAX(salary)
FROM Employee
);
Why This Is Efficient
• Uses aggregate functions instead of sorting the entire table.
• Works correctly even when the highest salary appears multiple times.
2. Find Employees Earning More Than Their Manager
Table: Employee
emp_id name salary manager_id
Efficient Query
SELECT [Link]
FROM Employee e
JOIN Employee m
ON e.manager_id = m.emp_id
WHERE [Link] > [Link];
Why This Is Efficient
• Uses a self-join with indexed primary keys.
• Avoids correlated subqueries.
3. Find Duplicate Records in a Table
Example: Duplicate Email Addresses
SELECT email, COUNT(*) AS duplicate_count
FROM Users
GROUP BY email
HAVING COUNT(*) > 1;
Why This Is Efficient
• Performs a single aggregation pass.
• Commonly used in data-cleaning tasks.
4. Delete Duplicate Records While Keeping One Copy
Efficient Query
DELETE FROM Users
WHERE user_id NOT IN (
SELECT MIN(user_id)
FROM Users
GROUP BY email
);
Why This Is Efficient
• Keeps the row with the smallest primary key.
• Removes all other duplicates.
Note: Some databases require wrapping the subquery in an additional derived table.
5. Find the Top 3 Highest Salaries
Efficient Query
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;
Alternative (MySQL/PostgreSQL)
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 3;
Why This Is Efficient
• Returns only the required number of rows.
• Uses indexes effectively when available.
6. Find Departments with More Than 5 Employees
SELECT department_id, COUNT(*) AS employee_count
FROM Employee
GROUP BY department_id
HAVING COUNT(*) > 5;
Why This Is Efficient
• Aggregates once and filters grouped results.
7. Find Customers Who Never Placed an Order
Efficient Query
SELECT c.customer_id, c.customer_name
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
Why This Is Efficient
• Standard anti-join pattern.
• Often more optimizer-friendly than NOT IN.
8. Find the Nth Highest Salary (Using Dense Ranking)
Example: 3rd Highest Salary
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employee
)t
WHERE rnk = 3;
Why This Is Efficient
• Uses a window function in one pass.
• Handles duplicate salaries correctly.
9. Find the Running Total of Sales
SELECT sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM Sales;
Why This Is Efficient
• Uses a window function instead of self-joins or correlated subqueries.
10. Find the Most Recent Order for Each Customer
SELECT customer_id, order_id, order_date
FROM (
SELECT customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM Orders
)t
WHERE rn = 1;
Why This Is Efficient
• Uses ROW_NUMBER() to identify the latest row per customer.
• Deterministic when multiple orders share the same date.