Question

Part I (60 Pts.). Please answer questions (a) and (b) using the following tables held in a Relational Database Management Systems. Employee (eupllName Wane, address, DOB, sex, position, deptNo Department (deptNe, deptNane mgrEupID Project where Employeecontains employee details and empll is the key Department contains department details and deptNe is the key mgrEplD identifies the employee who is the manager of the department. There is only one manager for each department. contains details of the projects in each department and the key is proiNe (no two departments can run the same project) Project and MorksQncontains details of the hours worked by employees on each project, and eutplDpreiNaform the key Produce a report of the total hours worked by each employee, arranged in order of department number and within department, alphabetically by employee surname. (Show the query) a.

Answer 1

If you have any doubts, please give me comment...

a)

SELECT E.empID, fName, lName, deptNo, SUM(hoursWorked) total_hours

FROM Employee E, WorksOn W

WHERE E.empID = W.empID

GROUP BY E.empID, fName, lName, deptNo

ORDER BY deptNo, lName;

b)

SELECT P.projNo, projName, COUNT(*) AS No_of_employees

FROM Project P, WorksOn W

WHERE P.projNo = W.projNo

GROUP BY P.projNo, projName

HAVING COUNT(*)>2;