Post #2620

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