Rank Departments by Student Count

Rank Departments by Student Count

In a university management system, you need to generate a report showing the number of enrolled students per department. The report must be comprehensive, listing all registered departments regardless of whether they currently have enrolled students.

Schema

You are provided with two tables: departments and students.

-- Table: departments
-- dept_id: Primary key uniquely identifying each department
-- dept_name: Name of the department
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- Table: students
-- student_id: Primary key uniquely identifying each student
-- student_name: Name of the student
-- dept_id: Foreign key referencing departments(dept_id)
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    dept_id INT
);

Task

Write a SQL query that returns the department ID, department name, and total count of enrolled students for every department.

• The output columns must be named: dept_id, dept_name, and student_count.
• Departments with zero students must be included and display a student_count of 0.
• Results must be ordered:
  1. student_count in descending order
  2. dept_name in ascending order (alphabetical)
  3. dept_id in ascending order (numeric tie-breaker)

Example

Input:

departments

students

Output:

Explanation:

• Computer Science has 3 students.
• Mathematics and Physics both have 1 student, so they are ordered alphabetically by dept_name ('Mathematics' before 'Physics').
• Philosophy has no students and correctly shows a count of 0.