This section gives you some suggested answers to the questions in Lab 6.2, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with. 6.2.1 Answers
In the original version of the script (ch06_2a.sql), you used a searched CASE statement in order to assign a value to the variable v_letter_grade as follows : CASE WHEN v_final_grade >= 90 THEN v_letter_grade := 'A'; WHEN v_final_grade >= 80 THEN v_letter_grade := 'B'; WHEN v_final_grade >= 70 THEN v_letter_grade := 'C'; WHEN v_final_grade >= 60 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END CASE; Notice that the variable v_letter_grade was used as part of the CASE statement. In the new version of the script, the CASE expression CASE WHEN v_final_grade >= 90 THEN 'A' WHEN v_final_grade >= 80 THEN 'B' WHEN v_final_grade >= 70 THEN 'C' WHEN v_final_grade >= 60 THEN 'D' ELSE 'F' END; does not contain any references to the variable v_letter_grade . Each search condition is evaluated. As soon as a particular condition evaluates to TRUE, its corresponding value is returned and then assigned to the variable v_letter_grade .
In the previous version of the script, the variable v_final_grade was used to hold the value of the numeric grade. SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; This value was used by the CASE expression to assign proper letter grade to the variable v_letter_grade . CASE WHEN v_final_grade >= 90 THEN 'A' WHEN v_final_grade >= 80 THEN 'B' WHEN v_final_grade >= 70 THEN 'C' WHEN v_final_grade >= 60 THEN 'D' ELSE 'F' END; In the current version of the script, the CASE expression is used as part of the SELECT INTO statement. As a result, the column FINAL_GRADE can be used by the CASE expression CASE WHEN final_grade >= 90 THEN 'A' WHEN final_grade >= 80 THEN 'B' WHEN final_grade >= 70 THEN 'C' WHEN final_grade >= 60 THEN 'D' ELSE 'F' END as part of the searched conditions in order to assign a value to the variable v_letter_grade . |