Count Student Number in Departments - Problem

You are given two tables: Student and Department.

The Student table contains information about students including their ID, name, gender, and department ID. The Department table contains department information with department ID and name.

Write a SQL query to report the department name and number of students in each department for all departments, even those with no current students.

Requirements:

  • Include all departments from the Department table
  • Count the number of students in each department
  • Order results by student count in descending order
  • If counts are equal, order by department name alphabetically

Table Schema

Student
Column Name Type Description
student_id PK int Primary key, unique student identifier
student_name varchar Name of the student
gender varchar Gender of the student
dept_id int Foreign key referencing Department table
Primary Key: student_id
Department
Column Name Type Description
dept_id PK int Primary key, unique department identifier
dept_name varchar Name of the department
Primary Key: dept_id

Input & Output

Example 1 — Multiple Departments with Students
Input Tables:
Student
student_id student_name gender dept_id
1 Jack M 1
2 Jane F 1
3 Mark M 2
Department
dept_id dept_name
1 Engineering
2 Science
3 Law
Output:
dept_name student_number
Engineering 2
Science 1
Law 0
💡 Note:

Engineering has 2 students (Jack and Jane), Science has 1 student (Mark), and Law has 0 students. Results are ordered by student count descending: 2, 1, 0.
Example 2 — Equal Counts with Alphabetical Ordering
Input Tables:
Student
student_id student_name gender dept_id
1 Alice F 2
2 Bob M 3
Department
dept_id dept_name
1 Mathematics
2 Physics
3 Chemistry
Output:
dept_name student_number
Chemistry 1
Physics 1
Mathematics 0
💡 Note:

Chemistry and Physics both have 1 student each. Since counts are equal, they are ordered alphabetically: Chemistry comes before Physics. Mathematics has 0 students.
Example 3 — All Departments Empty
Input Tables:
Student
student_id student_name gender dept_id
Department
dept_id dept_name
1 Art
2 Music
Output:
dept_name student_number
Art 0
Music 0
💡 Note:

No students exist in the Student table. All departments have 0 students and are ordered alphabetically: Art, Music.

Constraints

  • 1 ≤ student_id, dept_id ≤ 1000
  • student_name and dept_name consist of English letters
  • gender is either 'M' or 'F'
  • Each department in Student table exists in Department table

Visualization

Tap to expand
Count Student Number in DepartmentsInput: Department & Student Tablesdept_iddept_name1Engineering2Science3Lawstudent_iddept_id112132LEFT JOINGROUP BYOutput: Department Countsdept_namestudent_numberEngineering2Science1Law0LEFT JOIN ensures all departments appear, even with zero students
Understanding the Visualization
1
Input Tables
Department and Student tables
2
LEFT JOIN
Preserve all departments
3
Result
Department names with student counts
Key Takeaway
🎯 Key Insight: Use LEFT JOIN when you need to preserve all records from the left table (Department) regardless of matches in the right table (Student)

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18
34.5K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen