The Transcript Average

Mid-year reviews are coming up at Fairhaven University and each academic advisor gets a sheet listing their students with how each one is doing so far. "How they are doing" means the mean of the marks that student has actually received — a sitting still waiting on a mark tells the advisor nothing and must not be allowed to drag the figure down.

A student who has not sat anything yet has no mean to report and simply does not appear on the sheet.

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 their name and the mean of their marks rounded to two decimal places as avg_grade. Strongest first; if two students share a mean, the one whose name comes first alphabetically goes above the other.

Example output

Shape only — these students and means are invented: