SQL Interview Questions with Answers

✅ 16. Write a query to find the 2nd highest salary from Employee table using subquery OR window function.
⭐ Using Subquery

SELECT MAX(salary) AS second_highest_salary 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

⭐ Using Window Function

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

✅ 17. Explain INNER JOIN vs LEFT JOIN vs FULL JOIN with examples for employees and departments.
⭐ INNER JOIN → Only matching records

SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

⭐ LEFT JOIN → All employees + matching departments

SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

⭐ FULL JOIN → All records from both tables

SELECT e.name, d.department_name 
FROM employees e 
FULL JOIN departments d ON e.department_id = d.id;

✅ 18. Find and remove duplicate records using CTE + ROW_NUMBER().
⭐ Find Duplicates

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) rn 
  FROM employees
)
SELECT * FROM cte WHERE rn > 1;

⭐ Remove Duplicates

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) rn 
  FROM employees
)
DELETE FROM cte WHERE rn > 1;

✅ 19. Explain WHERE vs HAVING with GROUP BY. Show department-wise avg salary > 50k.
👉 Difference
WHERE → filter before grouping
HAVING → filter after grouping

SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id 
HAVING AVG(salary) > 50000;

✅ 20. Explain RANK vs DENSE_RANK vs ROW_NUMBER partitioned by department ordered by salary.

SELECT name, department_id, salary, 
  ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) rn,
  RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) rnk,
  DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) drnk
FROM employees;

✅ 21. Find top 5 products by total sales using GROUP BY + LIMIT.

SELECT product_id, SUM(sales_amount) AS total_sales 
FROM sales 
GROUP BY product_id 
ORDER BY total_sales DESC 
LIMIT 5;

✅ 22. Write a self join to show employee name and manager name.

SELECT e.name AS employee, m.name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.employee_id;

✅ 23. Handle NULL salaries using COALESCE, IS NULL, IFNULL.
⭐ Using COALESCE

SELECT name, COALESCE(salary, 0) AS salary 
FROM employees;

⭐ Using IS NULL

SELECT * FROM employees WHERE salary IS NULL;

✅ 24. Pivot sales data by month using CASE statement.

SELECT 
  SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
  SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM sales;

✅ 25. Subquery vs JOIN — which is faster? Why?
JOIN is usually faster, subquery is easier to read.

✅ 26. Write a recursive CTE for company hierarchy (CEO → managers → employees).

WITH RECURSIVE emp_hierarchy AS (
  SELECT employee_id, name, manager_id 
  FROM employees 
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id 
  FROM employees e 
  JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy;

✅ 27. Explain clustered vs non-clustered indexes. When to use each?
⭐ Clustered Index: physically sorts table data
⭐ Non-Clustered Index: separate structure pointing to data

SQL Resources: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Double Tap ♥️ For More