SQL Exercise – 2

3. Create Projects Table

CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
EmployeeID INT,
HoursWorked INT
);

4. Create Attendance Table

CREATE TABLE Attendance (
EmployeeID INT,
AttendanceDate DATE,
Status VARCHAR(10)
);

Data Insertion Scripts:

INSERT INTO Employees (EmployeeID, Name, DepartmentID, Salary)
VALUES 
(1, 'Raj', 101, 60000),
(2, 'Ajay', 102, 75000),
(3, 'Mahesh', 101, 55000),
(4, 'Sumit', 103, 80000),
(5, 'Kumar', 102, 70000);


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


INSERT INTO Projects (ProjectID, ProjectName, EmployeeID, HoursWorked)
VALUES 
(201, 'Recruitment', 1, 120),
(202, 'IT Automation', 2, 150),
(203, 'CRM Development', 3, 100),
(204, 'IT Automation', 5, 140),
(205, 'CRM Development', 4, 130);


INSERT INTO Attendance (EmployeeID, AttendanceDate, Status)
VALUES 
(1, '2020-10-01', 'Present'),
(2, '2020-10-01', 'Present'),
(3, '2020-10-01', 'Absent'),
(4, '2020-10-01', 'Present'),
(5, '2020-10-01', 'Present');

Solutions:

SELECT 
d.DepartmentName,
SUM(p.HoursWorked) AS TotalHoursWorked,
COUNT(DISTINCT a.EmployeeID) AS PresentEmployees
FROM 
Departments d
JOIN 
Employees e ON d.DepartmentID = e.DepartmentID
JOIN 
Projects p ON e.EmployeeID = p.EmployeeID
LEFT JOIN 
Attendance a ON e.EmployeeID = a.EmployeeID AND a.AttendanceDate = '2020-10-01' AND a.Status = 'Present'
GROUP BY 
d.DepartmentName
HAVING 
SUM(p.HoursWorked) >= 200;

Output:

Explanation:

1. Joins:
   • Join Departments with Employees to associate employees with their departments.
   • Join Employees with Projects to calculate total hours worked.
   • Left join with Attendance to count employees who were present on 2020-10-01.
2. Aggregate Functions:
   • Use SUM(p.HoursWorked) to get the total hours worked for each department.
   • Use COUNT(DISTINCT a.EmployeeID) to count unique employees who were present.
3. Filtering with HAVING:
   • Use the HAVING clause to exclude departments where the total hours worked are less than 200.
4. Grouping:
   • Group by department to calculate totals and counts for each department.