For each department, list the professors who have the number of PHD students he/

Question

For each department, list the professors who have the number of PHD students he/she advises more than the average number of PHD students these professors advise in their department.

NEED HELP ON HOW TO GET THE AVERAGE NUMBER OF PHD STUDENTS THESE PROFESSOR ADVISE in ORACLE

select d.deptid, e.empName, count(s.stdNumber)

from department d

left join employee_department ed on d.deptid = ed.deptid

left join employee e on ed.empSSN = e.empSSN

left join student s on s.stdadvisorSSN = e.empSSN

where e.empTypeId in (1,2,3,4)

and e.empCategoryID = 1

and s.stddegreeid = 7

and s.majordeptid = d.deptid

group by d.deptid, e.empName;

Explanation / Answer

select deptid,empName,Avg(std_count) as Avg_Students from
(select d.deptid, e.empName, count(s.stdNumber) as std_count
from department d
left join employee_department ed on d.deptid = ed.deptid
left join employee e on ed.empSSN = e.empSSN
left join student s on s.stdadvisorSSN = e.empSSN
where e.empTypeId in (1,2,3,4)
and e.empCategoryID = 1
and s.stddegreeid = 7
and s.majordeptid = d.deptid
group by d.deptid, e.empName) std;

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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