Lab 6.2 Exercise Answers

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

a)

Modify the script ch06_2a.sql. Substitute the CASE statement with the searched CASE expression, and assign the value returned by the expression to the variable v_letter_grade .

A1:

Answer: Your script should look similar to the script below. Changes are shown in bold letters . -- ch06_3a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := 102; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; 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; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END;

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 .

b)	Run the script created in part a and explain the output produced.
A2:	Answer: Your output should look similar to the following: Letter grade is: A PL/SQL procedure successfully completed. The SELECT INTO statement returns a value of 92 that is assigned to the variable v_final_grade . As a result, the first searched condition of the CASE expression evaluates to TRUE and returns a value of 'A'. This value is then assigned to the variable v_letter_grade and displayed on the screen via the DBMS_OUTPUT.PUT_LINE statement.
c)	Rewrite the script created in part a so that the result of the CASE expression is assigned to the v_letter_grade variable via a SELECT INTO statement.
A3:	Answer: Your script should look similar to the following. Changes are shown in bold letters. -- ch06_3b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := 102; v_section_id NUMBER := 89; v_letter_grade CHAR(1); BEGIN SELECT 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 INTO v_letter_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END;

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 .