Classes Per Department SQL | Dataford Interview Questions

Your question is Classes Per Department SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Taulia University needs a department-level view of its internal learning catalog. Write a PostgreSQL query that joins departments to classes and reports how many classes belong to each department.

Requirements

Return every department, including departments with no matching classes.
Count classes using class_id, so departments without classes return 0 rather than NULL.
Return department_name and the count as class_count.
Sort by class_count descending, then department_name ascending for deterministic results.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

classes

Column	Type	Description
class_idPK	INT	Unique class identifier
department_id	INT	Owning department identifier
class_name	VARCHAR(150)	Taulia University class name
instructor_name	VARCHAR(100)	Class instructor