Average Grade per Course (Ignoring Missing Grades)
Fairhaven University's exam board reviews each course's mean grade at the end of the year. One registration on the books has no grade against it — the student sat the course, but the paper has not come back from the marker.
The board's instruction is explicit. An unmarked registration is not a zero, and treating it as one would drag that course's mean down for a reason that has nothing to do with how the students did. It should play no part in the calculation at all: not in the total, and not in the divisor either.
enrollments — one row per student-course registration. grade is on the 0–4 scale, or NULL if the paper is not yet marked.
| 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 returning two columns, course_id and avg_grade, with one row per course that has at least one registration, the mean rounded to 2 decimals. Rows come back sorted by course_id, smallest first.
Example output — shape only; the figures below are invented, not this data's answer.
| course_id | avg_grade |
|---|---|
| 6 | 2.75 |
| 7 | 3.4 |
| 9 | 2.9 |
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