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
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.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. | 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
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.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. | 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.
|
|
لطفا منتظر باشید ...
