- 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.
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