Credits Attempted

The bursar at Fairhaven University bills students by credits attempted, not credits passed. A student who signs up for a four-credit course is billed for four credits from the moment the registration closes, however the term goes and whether or not a mark has been entered yet.

The bill therefore depends on three things held in three different places: which sittings a student has, what each of those courses is worth, and the student's name to put on the invoice.

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.

courses — one row per course the university runs. credits is how much the course counts towards a degree.

All three 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 total credits they are being billed for as total_credits. Credits from a sitting with no mark entered are still billed. Largest bill first; if two students are billed the same, the one whose name comes first alphabetically goes above the other.

Example output

Shape only — these students and credit loads are invented: