Count Student Number in Departments
Problem
Student(student_id, student_name, gender, dept_id) and Department(dept_id, dept_name). Return dept_name and student count, including departments with zero students.
stu=[(1,'A','M',1),(2,'B','F',1)] dept=[(1,'CS'),(2,'EE')][('CS',2),('EE',0)]SELECT d.dept_name, COUNT(s.student_id) AS student_number
FROM Department d LEFT JOIN Student s ON d.dept_id = s.dept_id
GROUP BY d.dept_id
ORDER BY student_number DESC, d.dept_name ASC;Explanation
We need a student count for every department, including the empty ones. The key choice is a LEFT JOIN starting from Department, so departments with no students still appear with a count of zero.
FROM Department d LEFT JOIN Student s keeps every department row even when no student matches. When a department has no students, the joined student columns are NULL.
That is why we count COUNT(s.student_id) rather than COUNT(*): COUNT ignores NULL values, so an empty department correctly counts as 0 instead of 1. The GROUP BY d.dept_id produces one row per department.
Finally ORDER BY student_number DESC, d.dept_name ASC sorts busiest departments first, breaking ties alphabetically by name. Example: CS has students 1 and 2 (count 2), while EE has none (count 0), giving [('CS', 2), ('EE', 0)].