Grade Extremes
An audit at Fairhaven University turned up a transcript with a mark of 40 on it — someone had typed a percentage into a field that runs 0 to 4. Before trusting any of the grade reporting, the data team wants the two extreme marks on record: if the smallest is below 0 or the largest above 4, something else has been typed in wrong.
Sittings with no mark entered are not marks. They should have no effect on either extreme.
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 with two columns, min_grade and max_grade, holding the smallest and the largest mark on record across all sittings. Sittings with no mark entered are ignored.
Example output
Shape only — these two marks are invented. A whole mark comes back plain, as 2 rather than 2.0:
| min_grade | max_grade |
|---|---|
| 2 | 3.85 |
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