The Employee Management System project involves creating a T-SQL database to manage and query employee information efficiently. This system will enable seamless handling of employee records, salary details, and departmental data through well-structured SQL queries
/* Here is Table Script*/
CREATE TABLE EmployeeDetails (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2),
HireDate DATE);
/* Inserting data into EmployeesTable */
INSERT INTO EmployeeDetails (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES
(1, 'Amit' , 'Sharma' , 'HR' , 50000.00, '2015-05-20'),
(2, 'Anjali' , 'Singh' , 'IT' , 60000.00, '2017-08-15'),
(3, 'Rahul' , 'Verma' , 'Marketing', 55000.00, '2020-01-10'),
(4, 'Priya' , 'Reddy' , 'Finance' , 65000.00, '2017-04-25'),
(5, 'Vikram' , 'Patel' , 'HR' , 52000.00, '2017-09-30'),
(6, 'Amit' , 'Mishra' , 'IT' , 62000.00, '2021-11-18'),
(7, 'Suresh' , 'Iyer' , 'Marketing', 58000.00, '2024-02-26'),
(8, 'Lakshmi', 'Menon' , 'Finance' , 70000.00, '2022-07-12'),
(9, 'Arjun' , 'Menon' , 'HR' , 53000.00, '2023-10-05'),
(10, 'Nisha' , 'Mehta' , 'IT' , 64000.00, '2016-03-08'); Table : EmployeeDetails:
Question 1: Retrieve only the FirstName and LastName of all employees.
Question 2: Retrieve distinct departments from the employeeDetails table.
SELECT FirstName, LastName FROM employeeDetails;
SELECT DISTINCT Department FROM employeeDetails;
Question 3: Retrieve employees whose salary is greater than 55000.
Question 4 : Retrieve employees hired after 2019.
SELECT * FROM employeeDetails WHERE Salary > 55000;
SELECT * FROM employeeDetails WHERE HireDate > '2019-12-31'; Question 5: Retrieve employees whose first name starts with ‘A’.
Question 6 :Retrieve employees whose last name ends with ‘non’.
SELECT * FROM employeeDetails WHERE FirstName LIKE 'A%' SELECT * FROM employeeDetails WHERE LastName LIKE '%non' Question 7: Retrieve employees whose First name do not have ‘a’.
Question 8 :Retrieve employees sorted by their salary in descending order.
SELECT * FROM employeeDetails WHERE FirstName NOT LIKE '%a%'
SELECT * FROM employeeDetails ORDER BY Salary DESC;
Question 9: Retrieve the count of employees in each department.
Question 10 :Retrieve the average salary of employees in the Finance department.
SELECT Department, COUNT(*) AS EmployeeCount FROM employeeDetails GROUP BY Department
SELECT AVG(Salary) AS AverageSalary FROM employeeDetails WHERE Department = 'Finance';
Question 11: Retrieve the maximum salary among all employees.
Question 12 :Retrieve the total salary expense for the company.
SELECT MAX(Salary) AS MaxSalary FROM employeeDetails;
SELECT SUM(Salary) AS TotalSalaryExpense FROM employeeDetails;
Question 13: Retrieve the oldest and newest hire date among all employees.
Question 14 :Retrieve employees with a salary between 50000 and 60000.
SELECT MIN(HireDate) AS OldestHireDate, MAX(HireDate) AS NewestHireDate FROM employeeDetails;
SELECT * FROM employeeDetails WHERE Salary BETWEEN 50000 AND 60000;
Question 15: Retrieve employees who are in the HR department and were hired before 2019.
Question 16 :Retrieve employees with a salary less than the average salary of all employees.
SELECT * FROM employeeDetails WHERE Department = 'HR' AND HireDate < '2019-01-01';
SELECT * FROM employeeDetails WHERE Salary < (SELECT AVG(Salary) FROM employeeDetails)
Question 17: Retrieve the top 3 highest paid employees.
Question 18 : Retrieve employees whose hire date is not in 2017.
SELECT top 3 * FROM employeeDetails ORDER BY Salary DESC SELECT * FROM employeeDetails WHERE YEAR(HireDate) <> 2017;
Question 19 : Retrieve the nth highest salary (you can choose the value of n).
Question 20 : Retrieve employees who were hired in the same year as ‘Priya Reddy’.
SELECT DISTINCT Salary FROM employeeDetails ORDER BY Salary DESC OFFSET n-1 ROWS FETCH NEXT 1 ROWS ONLY;
SELECT * FROM employeeDetails WHERE YEAR(HireDate) = (SELECT YEAR(HireDate) FROM employeeDetails WHERE FirstName = 'Priya' AND LastName = 'Reddy');
Question 21 : Retrieve employees who have been hired on weekends (Saturday or Sunday).
Question 22 : Retrieve employees who have been hired in the last 6 years.
SELECT * FROM employeeDetails WHERE DATEPART(WEEKDAY, HireDate) IN (1, 7);
SELECT * FROM employeeDetails WHERE HireDate >= DATEADD(YEAR, -6, GETDATE());
Question 23 : Retrieve the department with the highest average salary.
Question 24 : Retrieve the top 2 highest paid employees in each department.
SELECT Department
FROM employeeDetails
GROUP BY Department
HAVING AVG(Salary) = (
SELECT MAX(AvgSalary)
FROM (
SELECT AVG(Salary) AS AvgSalary
FROM employeeDetails
GROUP BY Department
) AS AvgSalaries
); SELECT EmployeeID, FirstName, LastName, Department,
Salary
FROM (
SELECT EmployeeID, FirstName,LastName,
DepartmenT,
Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM employeeDetails
) AS RankedemployeeDetails
WHERE Rank <= 2;
Method 2 :
WITH CTE AS
(
SELECT EmployeeID, FirstName, LastName, Department, Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM employeeDetails
)
SELECT * FROM CTE WHERE Rank <= 2;
Question 25 : Retrieve the cumulative salary expense for each department sorted by department and hire date.
Question 26 : Retrieve the employee ID, salary, and the next highest salary for each employee.
SELECT EmployeeID, FirstName, LastName, Department, Salary,HireDate,
SUM(Salary) OVER(PARTITION BY Department ORDER BY HireDate) AS CumulativeSalaryExpense
FROM employeeDetails
ORDER BY Department, HireDate;
SELECT EmployeeID,
Salary,
LEAD(Salary) OVER (ORDER BY Salary DESC) AS NextHighestSalary
FROM employeeDetails; Question 27 : Retrieve the employee ID, salary, and the difference between the current salary and the next highest salary for each employee.
Question 28 : Retrieve the employee(s) with the highest salary in each department.
SELECT EmployeeID, Salary,
Salary - LEAD(Salary) OVER (ORDER BY Salary DESC) AS SalaryDifference
FROM employeeDetails;
SELECT *FROM (
SELECT *, dense_rank() OVER(PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM employeeDetails
) AS RankedemployeeDetails
WHERE Rank = 1;
Question 29 : Retrieve the department(s) where the total salary expense is greater than the average total salary expense across all departments.
Question 30 : Retrieve the employee(s) who have the same first name and last name as any other employee.
SELECT Department, SUM(Salary) AS TotalSalaryExpense
FROM employeeDetails
GROUP BY Department
HAVING SUM(Salary) >
(SELECT AVG(TotalSalaryExpense) FROM
(SELECT SUM(Salary) AS TotalSalaryExpense
FROM employeeDetails GROUP BY Department) AS AvgTotalSalary); SELECT *
FROM employeeDetails e1
WHERE EXISTS (
SELECT 1
FROM employeeDetails e2
WHERE e1.EmployeeID != e2.EmployeeID
AND e1.FirstName = e2.FirstName
AND e1.LastName = e2.LastName );
Question 31 : Retrieve the employee(s) who have been with the company for more then 7 Years.
Question 32 : Retrieve the department with the highest salary range (Difference b/w highest and lowest).
SELECT FirstName, LastName, DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsWithCompany
FROM employeeDetails
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 7;
SELECT Department, MAX(Salary) - MIN(Salary) AS Range
FROM employeeDetails
GROUP BY Department
ORDER BY Range DESC
Thanks for Visiting.
Leave a Reply