The Most Popular Course
Room allocation at Fairhaven University starts with the biggest class, because the largest teaching space gets assigned first and everything else is fitted around it. The timetabling office needs one line: the course carrying the most sittings, and how many that is.
If two courses are tied at the top, the office takes the one whose title comes first alphabetically and sorts the other out by hand. A sitting counts towards class size whether or not a mark has been entered against it — the student is still in the room.
enrollments — one row per sitting: one student taking one course in one term. student_id points at a row in students and course_id at a row in courses. grade is the mark for that sitting, on the same 0-4 scale, and is NULL when no mark has been entered yet. semester is the term the sitting belongs to.
| student_id | course_id | grade | semester |
|---|---|---|---|
| 1 | 1 | 3.7 | Fall2023 |
| 1 | 2 | 3.9 | Spring2024 |
| 2 | 1 | 3.2 | Fall2023 |
| 2 | 3 | 3.5 | Fall2023 |
| 3 | 2 | 4.0 | Spring2024 |
| 3 | 4 | 3.8 | Spring2024 |
| 4 | 3 | 2.9 | Fall2023 |
| 5 | 1 | 3.6 | Fall2023 |
| 5 | 2 | NULL | Spring2024 |
courses — one row per course the university runs. credits is how much the course counts towards a degree.
| id | title | credits |
|---|---|---|
| 1 | Intro to Databases | 3 |
| 2 | Machine Learning | 4 |
| 3 | Linear Algebra | 3 |
| 4 | Data Visualization | 2 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns exactly one row, with the busiest course's title and its number of sittings as enrolled. If two courses share the highest number, return the one whose title comes first alphabetically.
Example output
If the fullest course were one called Discrete Maths, with forty-six sittings, you would get:
| title | enrolled |
|---|---|
| Discrete Maths | 46 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in