MediumUniversity12 min

Enrollment counts by course

Challenge

Count students enrolled in each course.

Business context

The registrar is comparing section sizes. A course with no students should still appear with a count of zero if one exists. In this term every course has at least one enrollment, but the query should not assume that.

Instructions

Return course_name and students. Count enrollment rows per course. Sort by students descending, then course name.

Schema

students

id INTEGER · primary key
name TEXT
program TEXT
enrolled_on TEXT

courses

id INTEGER · primary key
name TEXT
program TEXT

enrollments

id INTEGER · primary key
student_id INTEGER
course_id INTEGER
grade TEXT · nullable until graded

Sample rows

students
id	name	program	enrolled_on
1	Amina Diallo	Computer Science	2024-09-01
2	Hugo Berger	Computer Science	2023-09-01
3	Sara Iqbal	Economics	2024-09-01

courses
id	name	program
1	Databases	Computer Science
2	Algorithms	Computer Science
3	Microeconomics	Economics

enrollments
id	student_id	course_id	grade
1	1	1	A
2	1	2	B
3	2	1	A

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Active shipments per warehouse