Hey everyone, I’m stuck on a tricky SQL problem. I’m trying to find the names of departments where the professors teach less than 3 courses in total. Here’s what I’m working with:
CREATE TABLE department (dept_id INT, dept_name VARCHAR(50));
CREATE TABLE professor (prof_id INT, prof_name VARCHAR(50), dept_id INT);
CREATE TABLE semester_course (course_code VARCHAR(10), quarter INT, year INT, prof_id INT);
-- More tables exist but aren't directly used in this query
I’ve come up with this query, but it’s not giving me the right results:
SELECT d.dept_name
FROM department d
WHERE d.dept_id IN (
SELECT p.dept_id
FROM professor p
JOIN semester_course sc ON p.prof_id = sc.prof_id
GROUP BY p.dept_id
HAVING COUNT(DISTINCT sc.course_code) < 3
);
Can anyone spot what I’m doing wrong or suggest a better approach? I feel like I’m close, but something’s off. Thanks in advance for any help!
try using not exists to filter out profs with 3+ courses. e.g.
SELECT d.dept_name FROM department d
WHERE NOT EXISTS (
SELECT 1 FROM professor p
JOIN semester_course sc ON p.prof_id = sc.prof_id
WHERE p.dept_id=d.dept_id GROUP BY p.prof_id HAVING COUNT(DISTINCT sc.course_code)>=3
);
I noticed that your query is close, but there’s a logical nuance affecting the results. The issue lies in aggregating at the department level. Your query doesn’t filter out departments where even one professor might be teaching 3 or more courses, hence the result inconsistency.
One way to improve that logic is to change your approach and ensure you consider each professor individually. For instance, using a NOT EXISTS clause can help ensure that you retrieve only those departments where no professor teaches 3 or more courses.
Here’s an alternative solution:
SELECT d.dept_name
FROM department d
WHERE NOT EXISTS (
SELECT 1
FROM professor p
LEFT JOIN semester_course sc ON p.prof_id = sc.prof_id
WHERE p.dept_id = d.dept_id
GROUP BY p.prof_id
HAVING COUNT(DISTINCT sc.course_code) >= 3
);
This query ensures that for the selected department, every professor teaches fewer than 3 courses. The LEFT JOIN also accounts for professors who might not be assigned any course. I hope this revised solution clarifies and solves your problem.
I see you’re tackling a tricky SQL problem. It’s always fun to dig into these puzzles, isn’t it?
Your query is on the right track, but I think I spotted the issue. You’re grouping by department, but that might mask individual professors teaching 3+ courses. What if we flip it around and focus on the professors first?
How about something like this:
SELECT DISTINCT d.dept_name
FROM department d
JOIN professor p ON d.dept_id = p.dept_id
LEFT JOIN semester_course sc ON p.prof_id = sc.prof_id
GROUP BY d.dept_name, p.prof_id
HAVING COUNT(DISTINCT sc.course_code) < 3 OR COUNT(DISTINCT sc.course_code) = 0
This way, we’re checking each professor individually and including those with no courses. The DISTINCT on dept_name ensures we don’t get duplicates.
What do you think? Does this align with what you’re trying to achieve? I’m curious to hear if this works for your specific case or if there are any other wrinkles we should consider. Let me know how it goes!
