subqueries for employee management system

Consider a company database with the following relations:

• Employees(EmployeeID, Name, Department, Salary)
• Projects(ProjectID, ProjectName, Department, Budget)
• Managers(ManagerID, ManagerName, Department, Designation)
• Manages(ManagerID, ProjectID)

Using subqueries, write SQL queries to answer the following:

1. Find the manager(s) who manage the project with the highest budget.
2. Find the names of all IT employees whose salary is higher than the average salary of HR employees.
3. List the projects managed by managers from the IT department.
4. Identify the department that has the maximum number of employees.
5. Find the manager(s) who manage all projects in the IT department.

Company Database SQL Queries with Sample Data

Database Schema

Employees(EmployeeID, Name, Department, Salary)

Projects(ProjectID, ProjectName, Department, Budget)

Managers(ManagerID, ManagerName, Department, Designation)

Manages(ManagerID, ProjectID)

---

Sample Data

Employees

EmployeeID	Name	Department	Salary
1	Alice	IT	75000
2	Bob	IT	60000
3	Carol	HR	50000
4	David	HR	55000
5	Eve	Finance	65000

Projects

ProjectID	ProjectName	Department	Budget
101	Website Upgrade	IT	120000
102	Payroll System	HR	90000
103	Cloud Migration	IT	150000
104	Recruitment Drive	HR	60000

Managers

ManagerID	ManagerName	Department	Designation
201	Frank	IT	Senior Manager
202	Grace	HR	Manager
203	Henry	IT	Manager

Manages

ManagerID	ProjectID
201	101
201	103
203	103
202	102
202	104

---

SQL Queries Using Subqueries

1. Manager(s) of the project with the highest budget

SELECT m.ManagerID, m.ManagerName
FROM Managers m
WHERE m.ManagerID IN (
    SELECT ma.ManagerID
    FROM Manages ma
    WHERE ma.ProjectID = (
        SELECT ProjectID
        FROM Projects
        WHERE Budget = (SELECT MAX(Budget) FROM Projects)
    )
);

Expected Output:

ManagerID	ManagerName
201	Frank
203	Henry

---

2. IT employees with salary higher than average HR salary

SELECT Name
FROM Employees
WHERE Department = 'IT'
  AND Salary > (
      SELECT AVG(Salary)
      FROM Employees
      WHERE Department = 'HR'
  );

Expected Output:

Name
Alice
Bob

---

3. Projects managed by managers from IT department

SELECT p.ProjectID, p.ProjectName
FROM Projects p
WHERE p.ProjectID IN (
    SELECT ma.ProjectID
    FROM Manages ma
    WHERE ma.ManagerID IN (
        SELECT ManagerID
        FROM Managers
        WHERE Department = 'IT'
    )
);

Expected Output:

ProjectID	ProjectName
101	Website Upgrade
103	Cloud Migration

---

4. Department with the maximum number of employees

SELECT Department
FROM Employees
GROUP BY Department
HAVING COUNT(*) = (
    SELECT MAX(DeptCount)
    FROM (
        SELECT Department, COUNT(*) AS DeptCount
        FROM Employees
        GROUP BY Department
    ) AS DeptCounts
);

Expected Output:

Department
IT
HR

---

5. Managers who manage all projects in IT department

SELECT m.ManagerID, m.ManagerName
FROM Managers m
WHERE NOT EXISTS (
    SELECT p.ProjectID
    FROM Projects p
    WHERE p.Department = 'IT'
      AND p.ProjectID NOT IN (
          SELECT ma.ProjectID
          FROM Manages ma
          WHERE ma.ManagerID = m.ManagerID
      )
);

Expected Output:

ManagerID	ManagerName
201	Frank

---

Notes:

• Subqueries are used to filter and calculate values dynamically.
• “NOT EXISTS” in query 5 ensures the manager handles all IT projects.
• Aggregate functions (MAX, AVG) are used in subqueries for comparison.
• IN and NOT IN are used to check membership for sets of ProjectIDs.