Construct an SQL query to find the names of each department with more than 5 professors [closed]

Question

Schema

• Student (id integer, name string, address string, status string)
• Course (crsCode string, deptID string, crsName string, descr string)
• Transcript (studentID integer, crsCode string, semester string, numericGrade double)
• Teaching (profID integer, crsCode string, semester string)
• Department (deptID string, name string, budget integer)
• Professor (id integer, name string, deptID string)

SELECT Department.name
FROM Department
JOIN Professor
ON Professor.deptID == Department.deptID
HAVING COUNT(Professor.id) > 5;

I don't know why, but for some reason this SQL query is not working. Please help me if you can.

Answer 1

Let's do this a little more visually:

SELECT dept.name as department_name, COUNT(prof.id) as professor_count
  FROM Department dept INNER JOIN Professor prof ON prof.deptID = dept.deptID
 GROUP BY dept.name
 ORDER BY professor_count DESC;

This will give you all departments plus the count of professors sorted by (arguably) the most expensive departments. Seeing the data in front of you will allow you to verify its correctness before continuing.

Now, if you want to show just the departments with more than five professors, you can add HAVING professor_count > 5 between GROUP BY and ORDER BY.