SQL Queries on emp table

SQL Queries on emp table, Creating a schema for an employee table depends on the specific requirements of your application and database system. However, we can provide you with a basic example schema for an employee table, and then we’ll give you 15 + complex queries with solutions and explanations based on that schema.

Schema for Employee Table:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    HireDate DATE,
    DepartmentID INT,
    Salary DECIMAL(10, 2),
    ManagerID INT
);

In this schema, we have an Employee table with columns for employee details, including EmployeeID, FirstName, LastName, BirthDate, HireDate, DepartmentID, Salary, and ManagerID.

Now, let’s dive into the 15 + complex queries:

SQL Queries on emp table:

SQL Queries on emp table

1. List all employees’ names and salaries.

   SELECT FirstName, LastName, Salary FROM Employee;

2. List the average salary for each department.

   SELECT DepartmentID, AVG(Salary) AS AvgSalary
   FROM Employee
   GROUP BY DepartmentID;

3. Find the highest-paid employee.

   SELECT FirstName, LastName, Salary
   FROM Employee
   WHERE Salary = (SELECT MAX(Salary) FROM Employee);

4. List all employees with their manager’s name.

   SELECT e.FirstName AS EmployeeFirstName, e.LastName AS EmployeeLastName,
          m.FirstName AS ManagerFirstName, m.LastName AS ManagerLastName
   FROM Employee e
   LEFT JOIN Employee m ON e.ManagerID = m.EmployeeID;

5. List employees hired in the last year.

   SELECT FirstName, LastName, HireDate
   FROM Employee
   WHERE HireDate >= DATEADD(YEAR, -1, GETDATE());

6. Find employees who were born before 1980 and have a salary greater than $50,000.

   SELECT FirstName, LastName, BirthDate, Salary
   FROM Employee
   WHERE BirthDate < '1980-01-01' AND Salary > 50000;

7. List employees with their department names.

   SELECT e.FirstName, e.LastName, d.DepartmentName
   FROM Employee e
   JOIN Department d ON e.DepartmentID = d.DepartmentID;

8. Find the department with the highest average salary.

   SELECT DepartmentID
   FROM Employee
   GROUP BY DepartmentID
   HAVING AVG(Salary) = (SELECT MAX(AvgSalary) FROM (SELECT DepartmentID, AVG(Salary) AS AvgSalary FROM Employee GROUP BY DepartmentID) AS AvgSalaries);

9.List employees who have the same manager.

   SELECT e1.FirstName AS Employee1FirstName, e1.LastName AS Employee1LastName,
          e2.FirstName AS Employee2FirstName, e2.LastName AS Employee2LastName
   FROM Employee e1, Employee e2
   WHERE e1.ManagerID = e2.ManagerID AND e1.EmployeeID <> e2.EmployeeID;

10. Find employees who have not been assigned to a department.

SELECT FirstName, LastName 
FROM Employee 
WHERE DepartmentID IS NULL;

11. List the top 5 highest-paid employees in each department.

SELECT DepartmentID, FirstName, LastName, Salary 
FROM ( SELECT DepartmentID, FirstName, LastName, Salary, ROW_NUMBER() 
OVER (PARTITION BY DepartmentID 
ORDER BY Salary DESC) 
AS RowNum FROM Employee ) 
AS Ranked 
WHERE RowNum <= 5;

12. Calculate the total salary expenses for each department.

SELECT DepartmentID, SUM(Salary) AS TotalSalaryExpense FROM Employee GROUP BY DepartmentID;

13. Find the oldest employee.

SELECT FirstName, LastName, BirthDate 
FROM Employee 
WHERE BirthDate = (SELECT MIN(BirthDate) 
FROM Employee);

14. List employees who have the same birthdate.

SELECT e1.FirstName 
AS Employee1FirstName, e1.LastName 
AS Employee1LastName, e2.FirstName 
AS Employee2FirstName, e2.LastName 
AS Employee2LastName, e1.BirthDate 
FROM Employee e1, Employee e2 
WHERE e1.EmployeeID < e2.EmployeeID 
AND e1.BirthDate = e2.BirthDate;

15.Find the department with the highest total salary expense.

SELECT DepartmentID 
FROM Employee 
GROUP BY DepartmentID 
HAVING SUM(Salary) = (SELECT MAX(TotalSalaryExpense) 
FROM (SELECT DepartmentID, SUM(Salary) 
AS TotalSalaryExpense FROM Employee GROUP BY DepartmentID) AS TotalSalaries);

16. List employees who have the same last name but different first names

SELECT e1.FirstName 
AS Employee1FirstName, e1.LastName 
AS Employee1LastName,
           e2.FirstName 
           AS Employee2FirstName, e2.LastName 
           AS Employee2LastName
    FROM Employee e1, Employee e2
    WHERE e1.EmployeeID < e2.EmployeeID 
    AND e1.LastName = e2.LastName 
    AND e1.FirstName <> e2.FirstName;

17. Find employees who have the same hire date

SELECT e1.FirstName 
AS Employee1FirstName, e1.LastName 
AS Employee1LastName,
           e2.FirstName 
           AS Employee2FirstName, e2.LastName 
           AS Employee2LastName,
           e1.HireDate
    FROM Employee e1, Employee e2
    WHERE e1.EmployeeID < e2.EmployeeID 
    AND e1.HireDate = e2.HireDate

18. List employees who have a manager with a higher salary

SELECT e.FirstName 
AS EmployeeFirstName, e.LastName 
AS EmployeeLastName,
           m.FirstName 
           AS ManagerFirstName, m.LastName 
           AS ManagerLastName
    FROM Employee e
    JOIN Employee m 
    ON e.ManagerID = m.EmployeeID
    WHERE e.Salary > m.Salary;

19. Find the department where the number of employees is greater than the average number of employees in all departments.

SELECT DepartmentID
    FROM (
        SELECT DepartmentID, COUNT(*) AS NumEmployees
        FROM Employee
        GROUP BY DepartmentID
    ) AS DepartmentCounts
    WHERE NumEmployees > (SELECT AVG(NumEmployees)
   FROM (SELECT DepartmentID, COUNT(*) AS NumEmployees 
   FROM Employee 
   GROUP BY DepartmentID) AS AvgCounts);

20. List employees who have been employed for more than 10 years and have a salary greater than $60,000.

SELECT FirstName, LastName, HireDate, Salary
    FROM Employee
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 10 
    AND Salary > 60000;

SQL Queries on emp table | Related Articles:

Leave a Comment Cancel reply