SQL Query Interview Preparation (Questions and Answers)

1. Retrieve all records from the Employees table

SELECT * FROM Employees;

2. Retrieve only employee names

SELECT name FROM Employees;

3. Retrieve employees with salary greater than 50000

SELECT * FROM Employees
WHERE salary > 50000;

4. Retrieve employees from IT department

SELECT * FROM Employees
WHERE department = 'IT';

5. Sort employees by salary in ascending order

SELECT * FROM Employees
ORDER BY salary ASC;

6. Sort employees by salary in descending order

SELECT * FROM Employees
ORDER BY salary DESC;

7. Find total number of employees

SELECT COUNT(*) FROM Employees;

8. Find highest salary

SELECT MAX(salary) FROM Employees;

9. Find lowest salary

SELECT MIN(salary) FROM Employees;

10. Find average salary

SELECT AVG(salary) FROM Employees;

11. Count employees in each department

SELECT department, COUNT(*)
FROM Employees
GROUP BY department;

12. Find total salary by department

SELECT department, SUM(salary)
FROM Employees
GROUP BY department;

13. Find departments having more than 5 employees

SELECT department, COUNT(*)
FROM Employees
GROUP BY department
HAVING COUNT(*) > 5;

14. Find employees whose name starts with ‘A’

SELECT * FROM Employees
WHERE name LIKE 'A%';

15. Find employees whose name ends with ‘n’

SELECT * FROM Employees
WHERE name LIKE '%n';

16. Find employees whose name contains ‘ar’

SELECT * FROM Employees
WHERE name LIKE '%ar%';

17. Retrieve employees hired after 2022

SELECT * FROM Employees
WHERE joining_date > '2022-01-01';

18. Find employees with salary between 30000 and 60000

SELECT * FROM Employees
WHERE salary BETWEEN 30000 AND 60000;

19. Retrieve employees from multiple departments

SELECT * FROM Employees
WHERE department IN ('IT','HR');

20. Retrieve employees not in HR department

SELECT * FROM Employees
WHERE department NOT IN ('HR');

21. Find second highest salary

SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

22. Find third highest salary

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

23. Find duplicate employee names

SELECT name, COUNT(*)
FROM Employees
GROUP BY name
HAVING COUNT(*) > 1;

24. Remove duplicate records

DELETE FROM Employees
WHERE emp_id NOT IN (
SELECT MIN(emp_id)
FROM Employees
GROUP BY name
);

25. Retrieve employees without manager

SELECT * FROM Employees
WHERE manager_id IS NULL;

26. Find employees earning above average salary

SELECT *
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

27. Find employees earning below average salary

SELECT *
FROM Employees
WHERE salary < (SELECT AVG(salary) FROM Employees);

28. Find department with highest average salary

SELECT department, AVG(salary)
FROM Employees
GROUP BY department
ORDER BY AVG(salary) DESC
LIMIT 1;

29. Find employee with longest tenure

SELECT *
FROM Employees
ORDER BY joining_date ASC
LIMIT 1;

30. Find latest joined employee

SELECT *
FROM Employees
ORDER BY joining_date DESC
LIMIT 1;

31. Find top 5 highest paid employees

SELECT *
FROM Employees
ORDER BY salary DESC
LIMIT 5;

32. Find employees whose salary is same as another employee

SELECT *
FROM Employees
WHERE salary IN (
SELECT salary
FROM Employees
GROUP BY salary
HAVING COUNT(*) > 1
);

33. Retrieve unique departments

SELECT DISTINCT department
FROM Employees;

34. Find employees with NULL salary

SELECT *
FROM Employees
WHERE salary IS NULL;

35. Update employee salary

UPDATE Employees
SET salary = 60000
WHERE emp_id = 5;

36. Delete employees with salary less than 20000

DELETE FROM Employees
WHERE salary < 20000;

37. Insert new employee

INSERT INTO Employees
(emp_id, name, department, salary)
VALUES (101,'Rahul','IT',55000);

38. Rename column

ALTER TABLE Employees
RENAME COLUMN name TO employee_name;

39. Add new column

ALTER TABLE Employees
ADD email VARCHAR(100);

40. Drop column

ALTER TABLE Employees
DROP COLUMN email;

41. Find employee count in each department

SELECT department, COUNT(*) AS total_employees
FROM Employees
GROUP BY department;

42. Find departments with average salary greater than 50000

SELECT department, AVG(salary)
FROM Employees
GROUP BY department
HAVING AVG(salary) > 50000;

43. Retrieve employees whose salary equals the maximum salary

SELECT *
FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);

44. Retrieve employees whose salary equals the minimum salary

SELECT *
FROM Employees
WHERE salary = (SELECT MIN(salary) FROM Employees);

45. Find employees hired in 2023

SELECT *
FROM Employees
WHERE YEAR(joining_date) = 2023;

46. Find employees whose salary is greater than department average

SELECT *
FROM Employees e
WHERE salary >
(
SELECT AVG(salary)
FROM Employees
WHERE department = e.department
);

47. Retrieve employees ordered by joining date

SELECT *
FROM Employees
ORDER BY joining_date ASC;

48. Retrieve employees who joined before 2021

SELECT *
FROM Employees
WHERE joining_date < '2021-01-01';

49. Find employees whose name length is greater than 5

SELECT *
FROM Employees
WHERE LENGTH(name) > 5;

50. Retrieve employees whose name contains ‘a’

SELECT *
FROM Employees
WHERE name LIKE '%a%';

51. Find employees whose salary is divisible by 1000

SELECT *
FROM Employees
WHERE salary % 1000 = 0;

52. Retrieve employees sorted by department and salary

SELECT *
FROM Employees
ORDER BY department, salary DESC;

53. Find department with maximum number of employees

SELECT department, COUNT(*) AS total
FROM Employees
GROUP BY department
ORDER BY total DESC
LIMIT 1;

54. Find department with minimum number of employees

SELECT department, COUNT(*) AS total
FROM Employees
GROUP BY department
ORDER BY total ASC
LIMIT 1;

55. Retrieve employees whose salary is between 40000 and 80000

SELECT *
FROM Employees
WHERE salary BETWEEN 40000 AND 80000;

56. Find total salary expense of company

SELECT SUM(salary) AS total_salary
FROM Employees;

57. Retrieve employees hired in the last year

SELECT *
FROM Employees
WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

58. Retrieve employees whose department is not NULL

SELECT *
FROM Employees
WHERE department IS NOT NULL;

59. Find employees with same department

SELECT department, COUNT(*)
FROM Employees
GROUP BY department
HAVING COUNT(*) > 1;

60. Find employee names in uppercase

SELECT UPPER(name)
FROM Employees;

61. Find employee names in lowercase

SELECT LOWER(name)
FROM Employees;

62. Retrieve first 5 employees

SELECT *
FROM Employees
LIMIT 5;

63. Retrieve employees excluding first 5 rows

SELECT *
FROM Employees
LIMIT 5 OFFSET 5;

64. Find employees with salary greater than 70000

SELECT *
FROM Employees
WHERE salary > 70000;

65. Find employee with second lowest salary

SELECT MIN(salary)
FROM Employees
WHERE salary >
(SELECT MIN(salary) FROM Employees);

66. Find employees who joined in current month

SELECT *
FROM Employees
WHERE MONTH(joining_date) = MONTH(CURDATE());

67. Retrieve employees whose manager id is 101

SELECT *
FROM Employees
WHERE manager_id = 101;

68. Count employees with salary above 60000

SELECT COUNT(*)
FROM Employees
WHERE salary > 60000;

69. Retrieve employees whose salary is below department average

SELECT *
FROM Employees e
WHERE salary <
(
SELECT AVG(salary)
FROM Employees
WHERE department = e.department
);

70. Find employees hired in last 30 days

SELECT *
FROM Employees
WHERE joining_date >= CURDATE() - INTERVAL 30 DAY;

71. Retrieve employees whose salary equals department maximum

SELECT *
FROM Employees e
WHERE salary =
(
SELECT MAX(salary)
FROM Employees
WHERE department = e.department
);

72. Retrieve employees whose salary equals department minimum

SELECT *
FROM Employees e
WHERE salary =
(
SELECT MIN(salary)
FROM Employees
WHERE department = e.department
);

73. Find departments with more than 3 employees

SELECT department
FROM Employees
GROUP BY department
HAVING COUNT(*) > 3;

74. Retrieve employees sorted by name

SELECT *
FROM Employees
ORDER BY name;

75. Find employees whose salary is not between 30000 and 50000

SELECT *
FROM Employees
WHERE salary NOT BETWEEN 30000 AND 50000;

76. Retrieve employees whose department starts with ‘S’

SELECT *
FROM Employees
WHERE department LIKE 'S%';

77. Find employees whose department ends with ‘e’

SELECT *
FROM Employees
WHERE department LIKE '%e';

78. Find employees hired after manager

SELECT e.*
FROM Employees e
JOIN Employees m
ON e.manager_id = m.emp_id
WHERE e.joining_date > m.joining_date;

79. Retrieve employees with same salary

SELECT salary
FROM Employees
GROUP BY salary
HAVING COUNT(*) > 1;

80. Find employee count by salary

SELECT salary, COUNT(*)
FROM Employees
GROUP BY salary;

81. Retrieve employees whose salary rank is top 3

SELECT *
FROM (
SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM Employees
) t
WHERE rnk <= 3;

82. Find department wise employee count

SELECT department, COUNT(*)
FROM Employees
GROUP BY department;

83. Find department wise average salary

SELECT department, AVG(salary)
FROM Employees
GROUP BY department;

84. Find department wise maximum salary

SELECT department, MAX(salary)
FROM Employees
GROUP BY department;

85. Find department wise minimum salary

SELECT department, MIN(salary)
FROM Employees
GROUP BY department;

86. Retrieve employees whose name starts with vowel

SELECT *
FROM Employees
WHERE name REGEXP '^[AEIOUaeiou]';

87. Find employees hired today

SELECT *
FROM Employees
WHERE joining_date = CURDATE();

88. Retrieve employees whose salary is multiple of 500

SELECT *
FROM Employees
WHERE salary % 500 = 0;

89. Retrieve employees whose name has exactly 5 characters

SELECT *
FROM Employees
WHERE LENGTH(name) = 5;

90. Find employee with highest salary in IT department

SELECT *
FROM Employees
WHERE department = 'IT'
ORDER BY salary DESC
LIMIT 1;

91. Find employees hired earliest

SELECT *
FROM Employees
ORDER BY joining_date ASC
LIMIT 1;

92. Find employees hired most recently

SELECT *
FROM Employees
ORDER BY joining_date DESC
LIMIT 1;

93. Retrieve employees whose salary equals company average

SELECT *
FROM Employees
WHERE salary =
(SELECT AVG(salary) FROM Employees);

94. Find employees with no department

SELECT *
FROM Employees
WHERE department IS NULL;

95. Find employees whose manager id is NULL

SELECT *
FROM Employees
WHERE manager_id IS NULL;

96. Retrieve employees whose salary greater than 50000 and department IT

SELECT *
FROM Employees
WHERE salary > 50000
AND department = 'IT';

97. Retrieve employees whose salary less than 40000 or department HR

SELECT *
FROM Employees
WHERE salary < 40000
OR department = 'HR';

98. Find total employees hired each year

SELECT YEAR(joining_date), COUNT(*)
FROM Employees
GROUP BY YEAR(joining_date);

99. Retrieve employees whose salary is highest in company

SELECT *
FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);

100. Retrieve employees whose salary is lowest in company

SELECT *
FROM Employees
WHERE salary = (SELECT MIN(salary) FROM Employees);

📢 Join Our WhatsApp Channel

💼 Get Daily IT Job Updates, Interview Preparation Tips & Instant Alerts directly on WhatsApp.

👉 Join WhatsApp Now

📢 Join Our Telegram Channel

💼 Get Daily IT Job Updates, Interview Tips & Exclusive Alerts directly on Telegram!

👉 Join Telegram