SQL Assistance Needed: Retrieve Departments with Professors Teaching Fewer Than Three Courses

Courses
1

Identify department names having professors who, in total, teach under 3 distinct courses. Sample query:

SELECT dept.name
FROM department AS dept
JOIN professor AS prof ON dept.id = prof.dept_id
JOIN semester_course AS sem ON prof.id = sem.professor_id
GROUP BY dept.name
HAVING COUNT(DISTINCT sem.course_code) < 3;
2

The query posted offers a clear approach for retrieving departments with professors teaching less than three distinct courses. In my experience, it is important to double-check the data relationships to ensure that all relevant professors are being captured, even if they haven’t taught a course in a given semester. It may also be beneficial to verify that there are no null entries in sem.course_code that could skew the results. The use of GROUP BY and COUNT(DISTINCT …) is effective and practical for this scenario.