SQL Server Queries for Employee Data Management

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);


No comments:

Post a Comment