For these exercises, we use the Employees Database presented at the end of Chapt

Question

For these exercises, we use the Employees Database presented at the end of Chapter 1. Answer each question with a single SQL statement. Your query must work for any set of data in the Employees Database, not just the set of data we provide.

5. For each employee, list the employee ID, number of projects, and the total percentage of time for the current projects to which she is assigned. Include employees not assigned to any project.

24 Chapter I:Databasks projects projectd deparmenls code managerid subdeplof description startdate encdate revenue workson empore edi assignedime employeeld firstname lastname deplcode salary Figure 1.5: Schema for the Imployees Database

Explanation / Answer

THIS QUERY TO PULL ONLY SPECIFIC COLUMNS. YOU CAN ADD COLUMNS NEEDED IN SELECT CLAUSE

SELECT E.EMPLOYEEID,E.FIRSTNAME,E.LASTNAME,E.SALARY,P.PROJECTID,SUM(ASSIGNEDTIME),P.DESCRIPTION,P.STARTTIME,P.ENDTIME,P.REVENUE

FROM EMPLOYEES E

LEFT JOIN WORKSON W ON E.EMPLOYEEID = W.EMPLOYEEID
LEFT JOIN PROJECTS P ON P.PROJECTID = W.PROJECTID
GROUP BY E.EMPLOYEEID,P.PROJECTID

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---