The Consistency Check

Two courses at Fairhaven University can post the same mean mark and be nothing alike: one gives almost everybody the same result, the other splits its room into people who flew and people who struggled. The examinations board wants a measure of that split for each course — the gap between its best mark and its worst.

The board wants the gap exactly as the database computes it, with no rounding applied, because a rounded figure hides how these numbers are actually stored and the board has been burned by that before. Sittings with no mark entered are not marks and are ignored at both ends. A course with a single mark has a gap of zero.

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.

The table already exists in the database — there is nothing to create or load.

Task: Write a query that returns one row per course that has at least one mark on record, with course_id and the gap between its best and its worst mark as spread, unrounded. Widest gap first; courses sharing a gap are separated by course_id, smallest first.

Example output

Shape only — these courses and gaps are invented. The long tails are the point: nothing is rounded here, so a gap of one point two can surface as 1.2000000000000002, and a course with one mark on record — or several identical ones — gives a flat 0: