Writing a query for: Retrieve the first and last names of employees who work on

Question

Writing a query for:

Retrieve the first and last names of employees who work on more than the average number of projects. (Note: employees who do not work on any project are to be included in the average.) Display their names, the number of projects they work on, and the average number of projects. (The same average should be repeated in each row.) Show results in ascending alpha order (by last name and then first name). [The average number of projects is the
rem average number of projects worked on per employee.]

Tables:

Column Headings of the query: FNAME LNAME NUM_PRJ PRJ_AVG

Explanation / Answer

SELECT r.*
FROM   (SELECT e.fname, e.lname, COUNT(p.pno) num_prj, AVG(p.pno) prj_avg
   FROM   employee e, works_on w, project p
   WHERE e.ssn = w.essn
   AND    w.pno = p.pnumber
   GROUP BY e.fname, e.lname) r
WHERE   r.prj_avg > (SELECT AVG(pno) overall_avg FROM project p)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.