Advertisement
Guest User

zcw

a guest
Apr 5th, 2020
191
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 1.71 KB | None | 0 0
  1. create function grade_to_score(grade varchar(2)) returns integer deterministic
  2. begin
  3.     declare score integer;
  4.     select (case
  5.                 when grade = 'A+' then 100
  6.                 when grade = 'A' then 94
  7.                 when grade = 'A-' then 89
  8.                 when grade = 'B+' then 84
  9.                 when grade = 'B' then 79
  10.                 when grade = 'B-' then 74
  11.                 when grade = 'C+' then 70
  12.                 when grade = 'C' then 67
  13.                 when grade = 'C-' then 64
  14.                 when grade = 'D' then 61
  15.                 else 59 end)
  16.     into score;
  17.     return score;
  18. end;
  19.  
  20. create function grade_to_point(grade varchar(2)) returns decimal deterministic
  21. begin
  22.     declare score integer;
  23.     select (case
  24.                 when grade = 'A+' then 4.3
  25.                 when grade = 'A' then 4.0
  26.                 when grade = 'A-' then 3.7
  27.                 when grade = 'B+' then 3.3
  28.                 when grade = 'B' then 3.0
  29.                 when grade = 'B-' then 2.7
  30.                 when grade = 'C+' then 2.3
  31.                 when grade = 'C' then 2.0
  32.                 when grade = 'C-' then 1.7
  33.                 when grade = 'D' then 1.0
  34.                 else 0.0 end)
  35.     into score;
  36.     return score;
  37. end;
  38.  
  39. create procedure compute_gpa_avg_score() deterministic
  40. begin
  41.     select ID, sum(powered_score) / sum(credits), sum(powered_point) / sum(credits)
  42.     from (select ID,
  43.                  credits,
  44.                  grade_to_score(grade) * credits as powered_score,
  45.                  grade_to_point(grade) * credits as powered_point
  46.           from takes
  47.                    join course using (course_id)) as stat
  48.     group by ID;
  49. end;
  50.  
  51. call compute_gpa_avg_score();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement