List Departments with Student Counts Including Zero

Students

+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
| 1  | Alice  | 1       |
| 2  | Bob    | 2       |
| 3  | Carol  | 1       |
+----+--------+---------+

Departments

+----+------------+
| id | dept_name  |
+----+------------+
| 1  | CS         |
| 2  | Math       |
| 3  | Physics    |
+----+------------+

Scenario

University dashboard must display every department and how many students it currently has, including departments with zero students.

Question

Write a SQL query that lists each department name and the corresponding student count. Departments with no students should appear with count = 0.

Hints

LEFT JOIN Departments to Students, then GROUP BY department ID or name.

Department student counts including empty departments

Given the Students and Departments tables, write a SQL query that lists each department name and the corresponding number of students in that department. Departments with no students must still appear in the result with a student_count of 0.

Table Students

column	type	constraints
id	INTEGER	PRIMARY KEY
name	VARCHAR
dept_id	INTEGER	FOREIGN KEY REFERENCES Departments(id)

Sample data:

dept_id	id	name
1	1	Alice
2	2	Bob
1	3	Carol

Table Departments

column	type	constraints
id	INTEGER	PRIMARY KEY
dept_name	VARCHAR

Sample data:

dept_name	id
CS	1
Math	2
Physics	3

Expected output

One row per department with its name and the count of students; departments without students appear with student_count = 0.

dept_name	student_count
CS	2
Math	1
Physics	0

Hints

1. Start from the Departments table and LEFT JOIN to Students on dept_id.
2. Use COUNT(s.id) so that departments with no matching students return 0.
3. GROUP BY the department identifier (and name) to aggregate counts per department.