Lab 6.3 Exercise Answers

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

a)

Modify script ch06_4a.sql. Substitute the CASE expression with the NULLIF function.

A1:

Answer: Your script should look similar to the following script. Changes are shown in bold letters . -- ch06_4b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_final_grade NUMBER; BEGIN SELECT NULLIF(g.numeric_grade, e.final_grade) INTO v_final_grade FROM enrollment e JOIN grade g ON (e.student_id = g.student_id AND e.section_id = g.section_id) WHERE e.student_id = 102 AND e.section_id = 86 AND g.grade_type_code = 'FI'; DBMS_OUTPUT.PUT_LINE ('Final grade: 'v_final_grade); END;

In the original version of the script, you used CASE expression in order to assign a value to the variable v_final_grade as follows :

  CASE   WHEN e.final_grade = g.numeric_grade THEN NULL   ELSE g.numeric_grade   END  

The value stored in the column FINAL_GRADE is compared to the value stored in the column NUMERIC_GRADE. If these values are equal, then NULL is assigned to the variable v_final_grade ; otherwise , the value stored in the column NUMERIC_GRADE is assigned to the variable v_letter_grade .

In the new version of the script you substitute the CASE expression with the NULLIF function as follows:

  NULLIF(g.numeric_grade, e.final_grade)  

It is important to note that the NUMERIC_GRADE column is referenced first in the NULLIF function. You will recall that the NULLIF function compares expression1 to expression2. If expression1 equals expression2, the NULLIF functions returns NULL. If expression1 does not equal expression2, the NULLIF function returns expression1. In order to return the value stored in the column NUMERIC_GRADE, you must reference it first in the NULLIF function.

b)	Run the modified version of the script and explain the output produced.
A2:	Answer: Your output should look similar to the following: Final grade: 85 PL/SQL procedure successfully completed. The NULLIF function compares values stored in the columns NUMERIC_GRADE and FINAL_GRADE. Because the column FINAL_GRADE is not populated , the NULLIF function returns the value stored in the column NUMERIC_GRADE. This value is assigned to the variable v_final_grade and displayed on the screen with the help of the DBMS_OUTPUT.PUT_LINE statement.
c)	Change the order of columns in the NULLIF function. Run the modified version of the script and explain the output produced.
A3:	Answer: Your script should look similar to the following. Changes are shown in bold letters. -- ch06_4c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE v_final_grade NUMBER; BEGIN SELECT NULLIF(e.final_grade, g.numeric_grade) INTO v_final_grade FROM enrollment e JOIN grade g ON (e.student_id = g.student_id AND e.section_id = g.section_id) WHERE e.student_id = 102 AND e.section_id = 86 AND g.grade_type_code = 'FI'; DBMS_OUTPUT.PUT_LINE ('Final grade: 'v_final_grade); END;

The example produces the following output:

  Final grade:   PL/SQL procedure successfully completed.  

In this version of the script, the columns NUMERIC_GRADE and FINAL_GRADE are listed in the opposite order as follows:

  NULLIF(e.final_grade, g.numeric_grade)  

The value stored in the column FINAL_GRADE is compared to the value stored in the column NUMERIC_GRADE. Because these values are not equal, the NULLIF function returns the value of the column FINAL_GRADE. This column is not populated, so NULL is assigned to the variable v_final_grade .

6.3.2 Answers

a)	Modify script ch06_5a.sql. Substitute the CASE expression with the COALESCE function.
A1:	Answer: Your script should look similar to the following script. Changes are shown in bold letters. -- ch06_5b.sql, version 2.0 SET SERVEROUTPUT ON DECLARE v_num1 NUMBER := &sv_num1; v_num2 NUMBER := &sv_num2; v_num3 NUMBER := &sv_num3; v_result NUMBER; BEGIN v_result := COALESCE(v_num1, v_num2, v_num3); DBMS_OUTPUT.PUT_LINE ('Result: 'v_result); END;

In the original version of the script you used nested CASE expression in order to assign a value to the variable v_result as follows:

  CASE   WHEN v_num1 IS NOT NULL THEN v_num1   ELSE   CASE   WHEN v_num2 IS NOT NULL THEN v_num2   ELSE v_num3   END   END;  

In the new version of the script you substitute the CASE expression with the COALESCE function as follows:

  COALESCE(v_num1, v_num2, v_num3)  

Based on the values stored in the variables v_num1 , v_num2 , and v_num3 , the COALESCE function returns the first non-null variable.

b)	Run the modified version of the script and explain the output produced. Use the following values for the list of numbers : NULL, 1, 2.
A2:	Answer: Your output should look similar to the following: Enter value for sv_num1: null old 2: v_num1 NUMBER := &sv_num1; new 2: v_num1 NUMBER := null; Enter value for sv_num2: 1 old 3: v_num2 NUMBER := &sv_num2; new 3: v_num2 NUMBER := 1; Enter value for sv_num3: 2 old 4: v_num3 NUMBER := &sv_num3; new 4: v_num3 NUMBER := 2; Result: 1 PL/SQL procedure successfully completed. The COALESCE function evaluates its expressions in the sequential order. The variable v_num1 is evaluated first. Because the variable v_num1 is NULL, the COALESCE function evaluates the variable v_num2 next . Because the variable v_num2 is not NULL, the COALSECE function returns the value of the variable v_num2 . This value is assigned to the variable v_result and is displayed on the screen via DBMS_OUTPUT.PUT_LINE statement.
c)	What output will be produced by the modified version of the script if NULL is provided for all three numbers? Try to explain your answer before you run the script.
A3:	Answer: The variables v_num1 , v_num2 , and v_num3 are evaluated in the sequential order by the COALESCE function. When NULL is assigned to these variables, none of the evaluations produce a non-null result. So the COALESCE function returns NULL when all expressions evaluate to NULL. Your output should look similar to the following: Enter value for sv_num1: null old 2: v_num1 NUMBER := &sv_num1; new 2: v_num1 NUMBER := null; Enter value for sv_num2: null old 3: v_num2 NUMBER := &sv_num2; new 3: v_num2 NUMBER := null; Enter value for sv_num3: null old 4: v_num3 NUMBER := &sv_num3; new 4: v_num3 NUMBER := null; Result: PL/SQL procedure successfully completed.