Top 50 SQL Query Writing Questions with Example Queries
1. Second highest salary SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary
< (SELECT MAX(salary) FROM employees);
2. Top N salaries SELECT salary FROM employees ORDER BY salary DESC LIMIT N;
3. Employees above average salary SELECT * FROM employees WHERE salary > (SELECT AVG(salary)
FROM employees);
4. Employees with no manager SELECT * FROM employees WHERE manager_id IS NULL;
5. Find duplicate records SELECT name, COUNT() FROM employees GROUP BY name HAVING COUNT() > 1;
6. Delete duplicate records DELETE FROM employees WHERE id NOT IN (SELECT MIN(id) FROM
employees GROUP BY name, department_id);
7. Count employees per department SELECT department_id, COUNT(*) AS emp_count FROM employees
GROUP BY department_id;
8. Employee with max salary per department SELECT department_id, MAX(salary) AS max_salary FROM
employees GROUP BY department_id;
9. Employees' names in uppercase SELECT UPPER(name) AS uppercase_name FROM employees;
10. Employees whose name starts with a letter SELECT * FROM employees WHERE name LIKE 'A%';
11. Employees whose name ends with a letter SELECT * FROM employees WHERE name LIKE '%n';
12. Employees whose name contains a substring SELECT * FROM employees WHERE name LIKE
'%ohn%';
13. Employees with NULL in a column SELECT * FROM employees WHERE manager_id IS NULL;
14. Replace NULL with default value SELECT COALESCE(manager_id, 0) AS manager_id FROM employees;
15. Total salary of all employees SELECT SUM(salary) AS total_salary FROM employees;
16. Average salary per department SELECT department_id, AVG(salary) AS avg_salary FROM employees
GROUP BY department_id;
17. Employees hired in last N days SELECT * FROM employees WHERE hire_date >= CURDATE() -
INTERVAL N DAY;
18. Employees hired before a specific date SELECT * FROM employees WHERE hire_date < '2025-01-01';
1
19. Salary between two values SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;
20. Salary not between two values SELECT * FROM employees WHERE salary NOT BETWEEN 30000 AND
50000;
21. INNER JOIN two tables SELECT e.name, d.name AS department FROM employees e INNER JOIN
departments d ON e.department_id = d.id;
22. LEFT JOIN two tables SELECT e.name, d.name AS department FROM employees e LEFT JOIN
departments d ON e.department_id = d.id;
23. RIGHT JOIN two tables SELECT e.name, d.name AS department FROM employees e RIGHT JOIN
departments d ON e.department_id = d.id;
24. FULL OUTER JOIN SELECT e.name, d.name AS department FROM employees e FULL OUTER JOIN
departments d ON e.department_id = d.id;
25. Self join SELECT e1.name AS employee, e2.name AS manager FROM employees e1 LEFT JOIN
employees e2 ON e1.manager_id = e2.id;
26. Count employees grouped by department SELECT department_id, COUNT(*) AS emp_count FROM
employees GROUP BY department_id;
27. Employees along with department name SELECT e.name, d.name AS department FROM employees e
JOIN departments d ON e.department_id = d.id;
28. Employee with minimum salary SELECT * FROM employees WHERE salary = (SELECT MIN(salary)
FROM employees);
29. Employees whose salary is not maximum SELECT * FROM employees WHERE salary < (SELECT
MAX(salary) FROM employees);
30. Employees with same salary SELECT salary, COUNT() FROM employees GROUP BY salary HAVING
COUNT() > 1;
31. Employees salary greater than manager SELECT e.name FROM employees e JOIN employees m ON
e.manager_id = m.id WHERE e.salary > m.salary;
32. Employees not assigned to a department SELECT * FROM employees WHERE department_id IS NULL;
33. Employees with more than one project SELECT employee_id, COUNT() AS project_count FROM
employee_projects GROUP BY employee_id HAVING COUNT() > 1;
34. Rank employees by salary SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM
employees;
2
35. Cumulative salary per department SELECT department_id, salary, SUM(salary) OVER (PARTITION BY
department_id ORDER BY salary) AS cumulative_salary FROM employees;
36. Nth highest salary SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT N-1, 1;
37. Concatenate first and last name SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM
employees;
38. Employees' names sorted alphabetically SELECT name FROM employees ORDER BY name ASC;
39. Count distinct salaries SELECT COUNT(DISTINCT salary) FROM employees;
40. Salary in a given list SELECT * FROM employees WHERE salary IN (30000, 40000, 50000);
41. Salary not in a given list SELECT * FROM employees WHERE salary NOT IN (30000, 40000, 50000);
42. Name matches a pattern SELECT * FROM employees WHERE name LIKE 'A%';
43. Name does not match a pattern SELECT * FROM employees WHERE name NOT LIKE 'A%';
44. Employees and department even if department missing SELECT e.name, d.name AS department
FROM employees e LEFT JOIN departments d ON e.department_id = d.id;
45. Second oldest employee SELECT * FROM employees ORDER BY hire_date ASC LIMIT 1 OFFSET 1;
46. Delete employees joined before a date DELETE FROM employees WHERE hire_date < '2020-01-01';
47. Update salary in a department UPDATE employees SET salary = salary * 1.1 WHERE department_id =
10;
48. Department with highest total salary SELECT department_id, SUM(salary) AS total_salary FROM
employees GROUP BY department_id ORDER BY total_salary DESC LIMIT 1;
49. Employees salary higher than department average SELECT * FROM employees e WHERE salary >
(SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
50. Difference between max and min salary per department SELECT department_id, MAX(salary) -
MIN(salary) AS salary_diff FROM employees GROUP BY department_id;
