Managing employee data efficiently requires a strong understanding of SQL queries. Below are 32 essential SQL Server queries that cover common use cases, such as fetching, filtering, updating, and analyzing employee records.
-- 01. Fetch all employees whose salary is greater than 50,000.
SELECT * FROM Employees WHERE Salary > 50000;
-- 02. Fetch all employees working in the "IT" department.
SELECT * FROM Employees WHERE Department = 'IT';
-- 03. Fetch employee names and their departments.
SELECT Name, Department FROM Employees;
-- 04. Fetch the top 3 highest-paid employees.
SELECT TOP 3 * FROM Employees ORDER BY Salary DESC;
-- 05. Fetch employees whose names start with the letter "A".
SELECT * FROM Employees WHERE Name LIKE 'A%';
-- 06. Fetch employees who were hired in the year 2022.
SELECT * FROM Employees WHERE YEAR(HireDate) = 2022;
-- 07. Count the total number of employees in each department.
SELECT Department, COUNT(*) AS TotalEmployees FROM Employees GROUP BY Department;
-- 08. Find the average salary of employees in the "Finance" department.
SELECT AVG(Salary) AS AvgSalary FROM Employees WHERE Department = 'Finance';
-- 09. Fetch employees with salaries between 30,000 and 60,000.
SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 60000;
-- 10. Fetch all employees who do not belong to the "HR" department.
SELECT * FROM Employees WHERE Department <> 'HR';
-- 11. Fetch the details of employees who have not been assigned a department (NULL department).
SELECT * FROM Employees WHERE Department IS NULL;
-- 12. Fetch employee details sorted by their salaries in descending order.
SELECT * FROM Employees ORDER BY Salary DESC;
-- 13. Fetch duplicate employee names from the employee table.
SELECT Name, COUNT(*) FROM Employees GROUP BY Name HAVING COUNT(*) > 1;
-- 14. Fetch the department name and the highest salary in each department.
SELECT Department, MAX(Salary) AS HighestSalary FROM Employees GROUP BY Department;
-- 15. Update the salary of employees in the "IT" department by 10%.
UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'IT';
-- 16. Delete employees whose salaries are below 20,000.
DELETE FROM Employees WHERE Salary < 20000;
-- 17. Insert a new employee into the table.
INSERT INTO Employees (Name, Department, Salary, HireDate, Email, ManagerID)
VALUES ('New Employee', 'IT', 55000, '2024-01-31', 'new.employee@company.com', NULL);
-- 18. Fetch employees whose names contain the substring "Ajay".
SELECT * FROM Employees WHERE Name LIKE '%Ajay%';
-- 19. Fetch employees whose email IDs end with "@sqldbanow.com".
SELECT * FROM Employees WHERE Email LIKE '%@sqldbanow.com';
-- 20. Find the total salary paid to employees in each department.
SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department;
-- 21. Fetch the employee with the second-highest salary.
SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees));
-- 22. Fetch the number of employees hired in each year.
SELECT YEAR(HireDate) AS Year, COUNT(*) AS TotalEmployees FROM Employees GROUP BY YEAR(HireDate);
-- 23. Fetch employees who share the same salary as another employee.
SELECT * FROM Employees WHERE Salary IN (SELECT Salary FROM Employees GROUP BY Salary HAVING COUNT(*) > 1);
-- 24. Fetch employees along with their manager's name (using self-join).
SELECT e1.Name AS Employee, e2.Name AS Manager FROM Employees e1 LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
-- 25. Fetch employees with the same department and job title as "John Doe."
SELECT * FROM Employees WHERE Department = (SELECT Department FROM Employees WHERE Name = 'John Doe')
AND JobTitle = (SELECT JobTitle FROM Employees WHERE Name = 'John Doe');
-- 26. Find the maximum and minimum salary of employees.
SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;
-- 27. Fetch employees who were hired in the last 6 months.
SELECT * FROM Employees WHERE HireDate >= DATEADD(MONTH, -6, GETDATE());
-- 28. Fetch employees who have worked for more than 5 years.
SELECT * FROM Employees WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;
-- 29. Fetch all employees who do not have a manager assigned.
SELECT * FROM Employees WHERE ManagerID IS NULL;
-- 30. Fetch the first name, last name, and full name of employees.
SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
-- 31. Fetch employees grouped by department and sorted by the total salary in descending order.
SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department ORDER BY TotalSalary DESC;
-- 32. Fetch all employees whose salaries are more than the average salary.
SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);