zcw

alter table takes
    add column score numeric(5, 2) null;

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_student_gpa_avg_score(in ID varchar(5), out gpa numeric(3, 2), out score numeric(5, 2)) deterministic
begin
    select (sum(powered_score) / sum(credits)), sum(powered_point) / sum(credits)
    into score,gpa
    from (select credits,
                 grade_to_score(grade) * credits as powered_score,
                 grade_to_point(grade) * credits as powered_point
          from takes
                   join course using (course_id)
          where takes.ID = ID
         ) as stat;
end;

create trigger compute_insert_score
    before insert
    on takes
    for each row
begin
    if NEW.score is null
    then
        set NEW.score = grade_to_score(NEW.grade);
    end if;
end;

create trigger compute_update_score
    before update
    on takes
    for each row
begin
    set NEW.score = grade_to_score(NEW.grade);
end;

create table gpa_avg_score_stat
(
    ID        varchar(5),
    gpa       numeric(3, 2) null,
    avg_score numeric(5, 2) null,
    primary key (ID),
    foreign key (ID) references student (ID)
);

create procedure init_stat() not deterministic
begin
    declare id_done int default false;
    declare cur_id varchar(5);
    declare ids cursor for select distinct ID from takes;
    declare continue handler for not found set id_done = true;
    open ids;
    process_id_init:
    loop
        begin
            fetch ids into cur_id;
            call compute_student_gpa_avg_score(cur_id, @gpa, @avg_score);
            insert into gpa_avg_score_stat value (cur_id, @gpa, @avg_score);
            commit;
        end;
        if id_done then
            leave process_id_init;
        end if;
    end loop;
end;


call init_stat();