need SQL help, Find names of all departments whose professors collectively teach less than 3 courses

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?

Steffy Alen

Steffy Alen