Tables:
- department (dept_id, dept_name)
- student (student_id, student_name, major, level, age)
- professor (prof_id, prof_name, dept_id)
- course (course_code, name)
- semester_course (course_code, quarter, year, prof_id)
- enrolled (student_id, course_code, quarter, year, enrolled_at)
the question is Find names of all departments whose professors collectively teach less than 3 courses
my query right now looks like
select d.dept_name,d.dept_id FROM department as d
WHERE d.dept_id in (
select d1.dept_id from department d1,professor p1
where p1.dept_id = d1.dept_id AND p1.prof_id IN (
select p2.prof_id from professor p2,semester_course sc
WHERE sc.prof_id = p2.prof_id GROUP BY sc.course_code having count(*) < 3
) );
and it brings back wrong results, any help?