Rank Departments by Student Count

## Question Given these relational tables: ```text Student(Id, Name, DeptId) Department(Id, Name) ``` Write a SQL query that returns every department with its number of students. Sort by student count descending and, for tied counts, department name ascending. ### Constraints & Assumptions - `Student.DeptId` references `Department.Id` or is `NULL`. - Departments with no students must be included with count zero. - Department names need not be unique, but department IDs are unique. - Use portable SQL where practical. ### Clarifying Questions to Ask - Should departments with zero students appear? Yes. - Should students with `NULL` department be grouped into a synthetic department? No. - Which column should be counted? Count a non-null student key, not all joined rows. - Is a deterministic tie beyond department name required? Yes; add department ID after the requested ordering. ```hint Preserve empty departments Start from `Department` and left join students; an inner join would remove departments whose count should be zero. ``` ### Evaluation Focus - Correct outer join direction and join key. - Counting `Student.Id` so the null-extended row contributes zero. - Grouping by department identity and name. - Required descending count and ascending name ordering, with a stable final tie-break. - Awareness of indexes and aggregate cost. ### Extensions to Discuss 1. How would you return only departments tied for the largest count? 2. Which indexes help when the student table is large? 3. What changes if students can belong to multiple departments?