Transcript vs GPA

The stored grade point average on a student's record at Fairhaven University is maintained by a separate nightly job, and the data team has stopped trusting it. Their check is to put the stored figure next to the mean of the marks actually on record for that student and look for rows that disagree — both are on the same 0-4 scale, so they should be close.

Sittings with no mark entered contribute nothing to the computed figure, and a student with no sittings at all has nothing to compare and stays off the report.

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.

students — one row per enrolled student. gpa is the grade point average already on file for that student, on a 0-4 scale.

Both tables already exist in the database — there is nothing to create or load.

Task: Write a query that returns one row per student who has at least one sitting on record, with name, the stored gpa, and the mean of that student's marks rounded to two decimal places as avg_grade. Highest stored gpa first; students sharing a stored figure are separated alphabetically by name.

Example output

Shape only — these students and figures are invented: