Question

SQL ONLY-

Given Tables-

Employee (EmployeeID, FirstName, LastName, Address)

Department (DeptNo, DepartmentName, City)

DepartmentEmployee(DeptNo, EmployeeID, Position)

Project (ProjectNo, ProjectName, DeptNo)

Project Team ( ProjectNo, EmployeeID, Role)

MAKE THE QUERY FOR-

1. List the name and position of all the employee who works in the (DeptNo = ‘22C’).

2. List the total number of employees in each department for those departments with more than 5 employees.

3. List the project number, project name and the number of employees who worked on each project role.

Answer 1

Answer:

1)Select e.FirstName,e.LastName,de.Position from Employee e
join DepartmentEmployee de on de.EmployeeID = e.EmploeeID
where de.DeptNo = '22C'
order by e.FirstName;
/*This query will list down all the name and position of the employees who works in the (DeptNo = ‘22C’)*/

2)Select d.DeptNo,d.DepartmentName,count(de.DeptNo) from Department d
join DepartmentEmployee de on de.DeptNo = d.DeptNo
Group by d.DepartmentName having count(de.Deptno)>5
order by d.DepartmentName;
/*This query will list down the total number of employees in each department with more than 5 employees*/

3)select p.ProjectNo,p.ProjectName,pt.Role,count(pt.EmployeeID) from Project p
join ProjectTeam pt on pt.ProjectNo=p.ProjectNo
order by p.ProjectName;
/*This query will list down the project number, project name and the number of employees who worked on each project role*/

Let me know in case if you have any questions. Thanks and all the best.