Lab 5.2 Exercise Answers


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

5.2.1 Answers

a)

What letter grade will be displayed on the screen:

  1. if the value of v_final_grade is equal to 85?

  2. if the value of v_final_grade is NULL?

  3. if the value of v_final_grade is greater than 100?

A1:

Answer: If the value of v_final_grade is equal to 85, the value "B" of the letter grade will be displayed on the screen.


The conditions of the ELSIF statement are evaluated in sequential order. The first condition

 
  v_final_grade BETWEEN 90 AND 100  

evaluates to FALSE, and control is passed to the first ELSIF part of the ELSIF statement. Then, the second condition

 
  v_final_grade BETWEEN 80 AND 89  

evaluates to TRUE, and the letter "B" is assigned to the variable v_letter_grade . Control is then passed to first executable statement after END IF, and message

 
  Letter grade is: B  

is displayed on the screen.

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

If the value of the v_final_grade is undefined or NULL, then all conditions of the ESLIF statement evaluate to NULL (notice, they do not evaluate to FALSE). As a result, the ELSE part of the ELSIF statement is executed, and letter "F" is assigned to the v_letter_grade .

If the value of v_final_grade is greater than 100, value "F" of the letter grade will be displayed of the screen.

The conditions specified for the ELSIF statement cannot handle a value of v_final_grade greater than 100. So, for any student whose letter grade should be A+, will result in a letter grade of "F." After the ELSIF statement has terminated , "The letter grade is: F" is displayed on the screen.

b)

How would you change this script so that a message 'v_final_grade is null' is displayed if v_final_grade is NULL?

A2:

Answer: Your script should look similar to this script. Changes are shown in bold letters .

 -- ch05_3b.sql, version 2.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;  IF v_final_grade IS NULL THEN   DBMS_OUTPUT.PUT_LINE('v_final_grade is null');   ELSIF v_final_grade BETWEEN 90 AND 100 THEN  v_letter_grade := 'A'; ELSIF v_final_grade BETWEEN 80 AND 89 THEN v_letter_grade := 'B'; ELSIF v_final_grade BETWEEN 70 AND 79 THEN v_letter_grade := 'C'; ELSIF v_final_grade BETWEEN 60 AND 69 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END; 

One more condition has been added to the ELSIF statement. The condition

 
  v_final_grade BETWEEN 90 AND 100  

becomes the first ELSIF condition. Now, if the value of v_final_grade is NULL, the message "v_final_grade is null" is displayed on the screen. However, there is no value assigned to the variable v_letter_grade . The message "Letter grade is:" is displayed on the screen as well.

c)

How would you change this script so that student ID and section ID are provided by a user ?

A3:

Answer: Your script should look similar to this script. Changes are shown in bold letters.

 -- ch05_3c.sql, version 3.0 SET SERVEROUTPUT ON DECLARE  v_student_id NUMBER := &sv_student_id;   v_section_id NUMBER := &sv_section_id;  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; IF v_final_grade BETWEEN 90 AND 100 THEN v_letter_grade := 'A'; ELSIF v_final_grade BETWEEN 80 AND 89 THEN v_letter_grade := 'B'; ELSIF v_final_grade BETWEEN 70 AND 79 THEN v_letter_grade := 'C'; ELSIF v_final_grade BETWEEN 60 AND 69 THEN v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; -- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END; 
d)

How would you change the script to define a letter grade without specifying the upper limit of the final grade? In the statement, v_final_grade BETWEEN 90 and 100, number 100 is the upper limit.

A4:

Answer: Your script should look similar to following. Changes are shown in bold letters.

 -- ch05_3d.sql, version 4.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;  IF v_final_grade >= 90 THEN  v_letter_grade := 'A';  ELSIF v_final_grade >= 80 THEN  v_letter_grade := 'B';  ELSIF v_final_grade >= 70 THEN  v_letter_grade := 'C';  ELSIF v_final_grade >= 60 THEN  v_letter_grade := 'D'; ELSE v_letter_grade := 'F'; END IF; --- control resumes here DBMS_OUTPUT.PUT_LINE ('Letter grade is: ' v_letter_grade); END; 

In this example, there is no upper limit specified for the variable v_final_grade because the BETWEEN operator has been replaced with ">=" operator. Thus, this script is able to handle a value of v_final_grade that is greater than 100. Instead of assigning letter "F" to v_letter_grade (in version 1.0 of the script), the letter "A" is assigned to the variable v_letter_grade . As a result, this script produces more accurate results.



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net