The Missing Grade
Marks at Fairhaven University are entered by hand at the end of each term, and every year a few never arrive — an incomplete, a student who deferred, or an examiner who simply forgot. The registrar chases them, but only once she knows which ones they are, and an empty mark is invisible on a printed transcript.
Each one needs enough detail to chase: which student, which course, and which term it belongs to, so the right examiner can be emailed.
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 student_id, course_id and semester for every sitting that has no mark entered, smallest student_id first.
Example output
If one sitting were left unmarked, the row would look like this — invented ids and term:
| student_id | course_id | semester |
|---|---|---|
| 23 | 11 | Spring2022 |
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