Question

Employee        (empID, fName, lName, address, DOB, sex, position, deptNo)

            Department     (deptNo, deptName, mgrEmpID)

            Project            (projNo, projName, deptNo)

            WorksOn         (empID, projNo, hoursWorked)

            where Employee        contains employee details and empID is the key.

                        Department     contains department details and deptNo is the key. mgrEmpID identifies the employee who is the manager of the department. There is only one manager for each department.

                        Project            contains details of the projects in each department and the key isprojNo (no two departments can run the same project).

            and      WorksOn         contains details of the hours worked by employees on each project, and empID/projNo form the key.

WRITE SQL QUERIES FOR THE FOLLOWING:

1) list the FName, addresses, DeptNo and DeptName of all employees who are managers (USE 'JOIN' STRICTLY)

2) list the IDs of all employees who do not work for the 'Computer' department (USE 'NOT IN' STRICTLY)

3) find out how many hours each employee (empID) works on each project

ANSWER ONLY IF YOU ARE 100% SURE

Answer 1

1) We need to understand that Department table stores the ids of all managers in field mgrEmpID therefore we need to join Employee and Department table based on empId of Employee with mgrEmpID

select emp.fName, emp.address,

dept.deptNo,dept.DeptName

from Employee emp

join Department dept

on emp.empID=dept.mgrEmpID

2) Here in inner query we are finding all department Ids where department is "computer" and by using not in we will end up with all employess who do not work in computer department

Select emp.empID from Employee emp where

emp.deptNo not in (select dept.deptno from Departrment dept where dept.deptName="computer")

3) Here we need to use the Sum() and group and joins all together by to get the desired results

Select emp.empId, Sum(prj.hoursWorked)

from Employee emp

join Project prj on emp.empID= prj.EmpID

group by prj.empID /* This will provide the sum of hours worked by each employee*/