Fall vs Spring
The provost at Fairhaven University wants to know whether marks moved between the two terms on record. A rise might mean the teaching improved; it might equally mean the marking got softer, or that a hard course only ran in one of the two terms. The number does not settle the argument on its own, but nobody can start the argument without it.
Sittings with no mark entered contribute nothing to a term's mean, so a term that closed with results outstanding is measured on the results it actually has.
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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row per term, with semester and that term's mean mark rounded to two decimal places as avg_grade, terms in alphabetical order of their name.
Example output
Shape only — these terms and means are invented:
| semester | avg_grade |
|---|---|
| Fall2021 | 3.05 |
| Spring2022 | 3.41 |
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