Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create function grade_to_score(grade varchar(2)) returns integer deterministic
- begin
- declare score integer;
- select (case
- when grade = 'A+' then 100
- when grade = 'A' then 94
- when grade = 'A-' then 89
- when grade = 'B+' then 84
- when grade = 'B' then 79
- when grade = 'B-' then 74
- when grade = 'C+' then 70
- when grade = 'C' then 67
- when grade = 'C-' then 64
- when grade = 'D' then 61
- else 59 end)
- into score;
- return score;
- end;
- create function grade_to_point(grade varchar(2)) returns decimal deterministic
- begin
- declare score integer;
- select (case
- when grade = 'A+' then 4.3
- when grade = 'A' then 4.0
- when grade = 'A-' then 3.7
- when grade = 'B+' then 3.3
- when grade = 'B' then 3.0
- when grade = 'B-' then 2.7
- when grade = 'C+' then 2.3
- when grade = 'C' then 2.0
- when grade = 'C-' then 1.7
- when grade = 'D' then 1.0
- else 0.0 end)
- into score;
- return score;
- end;
- create procedure compute_gpa_avg_score() deterministic
- begin
- select ID, sum(powered_score) / sum(credits), sum(powered_point) / sum(credits)
- from (select ID,
- credits,
- grade_to_score(grade) * credits as powered_score,
- grade_to_point(grade) * credits as powered_point
- from takes
- join course using (course_id)) as stat
- group by ID;
- end;
- call compute_gpa_avg_score();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement