SQL Exercise – 1

Query:

Write a query to find the name of each employee, their department name, their manager’s name, and the total budget of the projects their department is handling. Exclude employees working in departments with a total project budget less than 100,000.

Table Creation Scripts:

1. Create Employees Table

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
DepartmentID INT,
Salary DECIMAL(10, 2)
);

2. Create Departments Table

CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50),
ManagerID INT
);

3. Create Projects Table

CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
DepartmentID INT,
Budget DECIMAL(10, 2)
);

Let’s insert the data into tables.

INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary)
VALUES 
(1, 'Alice', 101, 60000),
(2, 'Bob', 102, 75000),
(3, 'Charlie', 101, 55000),
(4, 'David', 103, 80000),
(5, 'Eve', 102, 70000);


INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID)
VALUES 
(101, 'HR', 1),
(102, 'IT', 2),
(103, 'Sales', 4);


INSERT INTO Projects (ProjectID, ProjectName, DepartmentID, Budget)
VALUES 
(201, 'Recruitment', 101, 50000),
(202, 'IT Automation', 102, 120000),
(203, 'CRM Development', 103, 95000);

Solution:

Here’s the SQL query to solve the problem:

SELECT 
e.Name AS EmployeeName,
d.DepartmentName,
m.Name AS ManagerName,
SUM(p.Budget) AS TotalProjectBudget
FROM 
Employees e
JOIN 
Departments d ON e.DepartmentID = d.DepartmentID
JOIN 
Employees m ON d.ManagerID = m.EmployeeID
JOIN 
Projects p ON d.DepartmentID = p.DepartmentID
GROUP BY 
e.Name, d.DepartmentName, m.Name
HAVING 
SUM(p.Budget) >= 100000;

Output:

Explanation:

1. Joins:
   • Join the Employees table with Departments to link employees to their department.
   • Join the Departments table back to the Employees table to get the manager’s details (ManagerID).
   • Join the Departments table with Projects to associate projects with departments.
2. Aggregate Function:
   • Use SUM(p.Budget) to calculate the total project budget for each department.
3. Filtering with HAVING:
   • Filter departments whose total project budget is greater than or equal to 100,000.
4. Grouping:
   • Group by EmployeeName, DepartmentName, and ManagerName to get individual records.