Post #2298

Top 10 SQL interview questions with solutions by @sqlspecialist 1. What is the difference between WHERE and HAVING? Solution: WHERE filters rows before aggregation. HAVING filters rows after aggregation. SELECT department, AVG(salary) FROM employees WHERE salary > 3000 GROUP BY department HAVING AVG(salary) > 5000; 2. Write a query to find the second-highest salary. Solution: SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); 3. How do you fetch the first 5 rows of a table? Solution: SELECT * FROM employees LIMIT 5; -- (MySQL/PostgreSQL) For SQL Server: SELECT TOP 5 * FROM employees; 4. Write a query to find duplicate records in a table. Solution: SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1; 5. How do you find employees who don’t belong to any department? Solution: SELECT * FROM employees WHERE department_id IS NULL; 6. What is a JOIN, and write a query to fetch data using INNER JOIN. Solution: A JOIN combines rows from two or more tables based on a related column. SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id; 7. Write a query to find the total number of employees in each department. Solution: SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id; 8. How do you fetch the current date in SQL? Solution: SELECT CURRENT_DATE; -- MySQL/PostgreSQL SELECT GETDATE(); -- SQL Server 9. Write a query to delete duplicate rows but keep one. Solution: WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn FROM table_name ) DELETE FROM CTE WHERE rn > 1; 10. What is a Common Table Expression (CTE), and how do you use it? Solution: A CTE is a temporary result set defined within a query. WITH EmployeeCTE AS ( SELECT department_id, COUNT(*) AS total_employees FROM employees GROUP BY department_id ) SELECT * FROM EmployeeCTE WHERE total_employees > 10; Hope it helps :) #sql#dataanalysts