The A-Range List

Fairhaven University marks work on a 0-4 scale, and anything from 3.7 upward is recorded as a distinction. The registrar is assembling the distinction register for the year — every result that reached that bar, listed as a result rather than as a student, because a student can earn several and each one is recorded separately.

A sitting still waiting on a mark has not earned a distinction. The register is read down from the best result, and two results on the same mark need a settled order so the register reads the same way every time it is produced.

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 student_id, course_id and grade for every sitting whose mark is 3.7 or better, the bar itself included. Best mark first; results sharing a mark are separated by student_id, smallest first, with course_id smallest first settling anything still level. Sittings with no mark entered never qualify.

Example output

Shape only — these sittings and marks are invented: