Lab 6.1 Exercise Answers

This section gives you some suggested answers to the questions in Lab 6.1, 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.1.1 Answers

a)	If the value of v_date equals '15-JAN-2002', what output is printed on the screen?
A1:	Answer: Your output should look like the following: Enter value for sv_user_date: 15-JAN-2002 old 2: v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY'); new 2: v_date DATE := TO_DATE('15-JAN-2002', 'DD-MON-YYYY'); Today is Tuesday PL/SQL procedure successfully completed.

When the value of 15-JAN-2002 is entered for v_date , the number of the day of the week is determined for the variable v_day with the help of the TO_CHAR function. Next , each expression of the CASE statement is compared sequentially to the value of the selector. Because the value of the selector equals 3, the DBMS_OUTPUT.PUT_LINE statement associated with the third WHEN clause is executed. As a result, the message 'Today is Tuesday' is displayed on the screen. The rest of the expressions are not evaluated, and control is passed to the first executable statement after END CASE.

b)	How many times is the CASE selector v_day evaluated?
A2:	Answer: The CASE selector v_day is evaluated only once. However, the WHEN clauses are checked sequentially. When the value of the expression in the WHEN clause equals the value of the selector, the statements associated with the WHEN clause are executed.
c)	Rewrite this script using the ELSE clause in the CASE statement.
A3:	Answer: Your script should look similar to the following. Changes are shown in bold letters . -- ch06_1b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY'); v_day VARCHAR2(1); BEGIN v_day := TO_CHAR(v_date, 'D'); CASE v_day WHEN '1' THEN DBMS_OUTPUT.PUT_LINE ('Today is Sunday'); WHEN '2' THEN DBMS_OUTPUT.PUT_LINE ('Today is Monday'); WHEN '3' THEN DBMS_OUTPUT.PUT_LINE ('Today is Tuesday'); WHEN '4' THEN DBMS_OUTPUT.PUT_LINE ('Today is Wednesday'); WHEN '5' THEN DBMS_OUTPUT.PUT_LINE ('Today is Thursday'); WHEN '6' THEN DBMS_OUTPUT.PUT_LINE ('Today is Friday'); ELSE DBMS_OUTPUT.PUT_LINE ('Today is Saturday'); END CASE; END;

Notice that the last WHEN clause has been replaced by the ELSE clause. If '19-JAN-2002' is provided at runtime, the example produces the following output:

  Enter value for sv_user_date: 19-JAN-2002   old 2: v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');   new 2: v_date DATE := TO_DATE('19-JAN-2002', 'DD-MON-YYYY');   Today is Saturday   PL/SQL procedure successfully completed.  

None of the expressions listed in the WHEN clauses are equal to the value of the selector because the date '19-JAN-2002' falls on Saturday, which is the seventh day of the week. As a result, the ELSE clause is executed, and the message 'Today is Saturday' is displayed on the screen.

d)

Rewrite this script using the searched CASE statement.

A4:

Answer: Your script should look similar to the following. Changes are shown in bold letters. -- ch06_1c.sql, version 1.0 SET SERVEROUTPUT ON DECLARE v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY'); BEGIN CASE WHEN TO_CHAR(v_date, 'D') = '1' THEN DBMS_OUTPUT.PUT_LINE ('Today is Sunday'); WHEN TO_CHAR(v_date, 'D') = '2' THEN DBMS_OUTPUT.PUT_LINE ('Today is Monday'); WHEN TO_CHAR(v_date, 'D') = '3' THEN DBMS_OUTPUT.PUT_LINE ('Today is Tuesday'); WHEN TO_CHAR(v_date, 'D') = '4' THEN DBMS_OUTPUT.PUT_LINE ('Today is Wednesday'); WHEN TO_CHAR(v_date, 'D') = '5' THEN DBMS_OUTPUT.PUT_LINE ('Today is Thursday'); WHEN TO_CHAR(v_date, 'D') = '6' THEN DBMS_OUTPUT.PUT_LINE ('Today is Friday'); WHEN TO_CHAR(v_date, 'D') = '7' THEN DBMS_OUTPUT.PUT_LINE ('Today is Saturday'); END CASE; END;

Notice that in the new version of the example there is no need to declare variable v_day because the searched CASE statement does not need a selector. The expression that you used to assign a value to the variable v_day is now used as part of the searched conditions. When run, this example produces output identical to the output produced by the original version:

  Enter value for sv_user_date: 15-JAN-2002   old 2: v_date DATE := TO_DATE('&sv_user_date', 'DD-MON-YYYY');   new 2: v_date DATE := TO_DATE('15-JAN-2002', 'DD-MON-YYYY');   Today is Tuesday   PL/SQL procedure successfully completed.  

6.1.2 Answers

a)	What letter grade will be displayed on the screen: if the value of v_final_grade is equal to 60? if the value of v_final_grade is greater than 60 and less than 70? if the value of v_final_grade is NULL?
A1:	Answer: If the value of v_final_grade is equal to 60, value "D" of the letter grade will be displayed on the screen.

The searched conditions of the CASE statement are evaluated in sequential order. The searched condition

  WHEN v_final_grade >= 60 THEN  

yields TRUE, and as a result, letter "D" is assigned to the variable v_letter_grade . Control is then passed to the first executable statement after END IF, and the message "Letter grade is: D" is displayed on the screen.

If the value of v_final_grade is greater than 60 and less than 70, value "D" of the letter grade will be displayed on the screen.

If the value of the v_final_grade falls between 60 and 70, then the searched condition

  WHEN v_final_grade >= 70 THEN  

yields FALSE because the value of the variable v_final_grade is less that 70. However, the next searched condition

  WHEN v_final_grade >= 60 THEN  

of the CASE statement evaluates to TRUE, and letter "D" is assigned to the variable v_letter_grade .

If the value of v_final_grade is NULL, value "F" of the letter grade will be displayed on the screen.

All searched conditions of the CASE statement evaluate to FALSE because NULL cannot be compared to a value. Such a comparison will always yield FALSE, and as a result, the ELSE clause is executed.

b)

How would you change this script so that a message "There is no final grade" is displayed if v_final_grade is null? In addition, make sure that the message "Letter grade is: " is not displayed on the screen.

A2:

Answer: Your script should look similar to this script. Changes are shown in bold letters. -- ch06_2b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; 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; CASE -- outer CASE WHEN v_final_grade IS NULL THEN DBMS_OUTPUT.PUT_LINE ('There is no final grade.'); ELSE CASE “- inner 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; -- control resumes here after inner CASE terminates DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END CASE; -- control resumes here after outer CASE terminates END;

In order to achieve the desired results, you are nesting CASE statements one inside the other just like IF statements in the previous chapter. The outer CASE statement evaluates the value of the variable v_final_grade . If the value of v_final_grade is NULL, then the message "There is no final grade." is displayed on the screen. If the value of v_final_grade is not NULL, then the ELSE part of the outer CASE statement is executed.

Notice that in order to display the letter grade only when there is a final grade, you have associated the statement

  DBMS_OUTPUT.PUT_LINE ('Letter grade is: 'v_letter_grade);  

with the ELSE clause of the outer CASE statement. This guarantees that the message "Letter grade " will be displayed on the screen only when the variable v_final_grade is not NULL.

In order to test this script fully, you have also introduced a substitution variable. This enables you to run the script for the different values of v_student_id . For the first run, enter value of 136, and for the second run enter the value of 102.

The first output displays the message "There is no final grade." and does not display the message "Letter grade ":

  Enter value for sv_student_id: 136   old 2: v_student_id NUMBER := &sv_student_id;   new 2: v_student_id NUMBER := 136;   There is no final grade.   PL/SQL procedure successfully completed.  

The second run produced output similar to the output produced by the original version:

  Enter value for sv_student_id: 102   old 2: v_student_id NUMBER := &sv_student_id;   new 2: v_student_id NUMBER := 102;   Letter grade is: A   PL/SQL procedure successfully completed.  

c)

Rewrite this script, changing the order of the searched conditions as follows : CASE WHEN v_final_grade >= 60 THEN v_letter_grade := 'D'; WHEN v_final_grade >= 70 THEN v_letter_grade := 'C'; WHEN v_final_grade >= 80 THEN ... WHEN v_final_grade >= 90 THEN ... ELSE ...

A3:

Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch06_2c.sql, version 3.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; CASE WHEN v_final_grade >= 60 THEN v_letter_grade := 'D'; WHEN v_final_grade >= 70 THEN v_letter_grade := 'C'; WHEN v_final_grade >= 80 THEN v_letter_grade := 'B'; WHEN v_final_grade >= 90 THEN v_letter_grade := 'A'; ELSE v_letter_grade := 'F'; END CASE; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END; This script produces the following output: Letter grade is: D PL/SQL procedure successfully completed. The first searched condition of the CASE statement evaluates to TRUE, because the value of v_final_grade equals 92, and it is greater than 60.

You learned earlier that the searched conditions are evaluated sequentially. Therefore, the statements associated with the first condition that yields TRUE are executed, and the rest of the searched conditions are discarded. In this example, the searched condition

  WHEN v_final_grade >= 60 THEN  

evaluates to TRUE, and the value of "D" is assigned to the variable v_letter_grade . Then control is passed to the first executable statement after END CASE, and the message "Letter grade is: D" is displayed on the screen. In order for this script to assign the letter grade correctly, the CASE statement may be modified as follows:

  CASE   WHEN v_final_grade < 60 THEN v_letter_grade := 'F';   WHEN v_final_grade < 70 THEN v_letter_grade := 'D';   WHEN v_final_grade < 80 THEN v_letter_grade := 'C';   WHEN v_final_grade < 90 THEN v_letter_grade := 'B';   WHEN v_final_grade < 100 THEN v_letter_grade := 'A';   END CASE;  

However, there is a small problem with this CASE statement also. What do you think will happen when v_final_grade is greater than 100?

With the CASE constructs, as with the IF constructs, a group of statements that is executed will generally depend on the order in which its condition is listed.