PL/SQL Control Structures - Quiz Explanation

The correct answers are indicated below, along with the text that explains the correct answers.
 
1. Evaluate the value of amount in the PL/SQL block below:
/* Start main block */
DECLARE
order_id NUMBER := 101;
product_id VARCHAR2(4) := 'DOGGY';
amount NUMBER(9,2);
BEGIN
IF order_id > 100 AND dept_code = 'DOG' THEN
amount := 100;
ELSE 
amount := 50;
END IF;
END;
Please select the best answer.
  A. 100
  B. 50
  C. 0
  D. Cannot be deduced
  Answer B is correct.
In the PL/SQL block example, the value of order_id is 101 and dept_code is not equal to ‘DOG’.
This will cause the control to pass to the else part and the amount variable to be assigned the value of 50. Thus, answers A, C, and D are incorrect.


2. What would be assigned to pet_commission if pet_sales is set to 500 in the PL/SQL block below?

IF pet_sales < 100 THEN
pet_commission := (pet_sales * 10 / 100);
ELSIF pet_sales < 200 THEN
 pet_commission := (pet_sales * 20 / 100);
ELSIF pet_sales < 300 THEN
 pet_commission := (pet_sales * 30 / 100);
ELSE
 pet_commission := (pet_sales * 50 / 100); 
END IF

Please select the best answer.
  A. 50
  B. 100
  C. 150
  D. 250
  Answer D is correct.
In the PL/SQL block example, because the value of pet_sales is greater than 300, the control will pass to the final ELSE construct where pet_commission is calculated as 50% of the pet_sales value. Thus, answers A, B, and C are incorrect.

3. How many nested IF clauses can be included within an IF clause?
Please select the best answer.
  A. 0
  B. 1
  C. 15
  D. Any number
  Answer D is correct.
You can have any number of IF clauses nested within an IF clause. Thus, answers A, B, and C are incorrect.

4. What is the maximum number of ELSE clauses that can be included in an IF clause that is not nested?
Please select the best answer.
  A. 0
  B. 1
  C. 15
  D. Any number
  Answer B is correct.
You can only have one ELSE clause for every IF clause. If the IF clause is not nested, there is only one. Thus, answers A, C, and D are incorrect.

5. In the PL/SQL block below, how many rows will be inserted in the messages table?
DECLARE 
 v_start_sales NUMBER := 2;
 v_end_sales  NUMBER := 100;
BEGIN
 FOR i IN v_start_sales..v_end_sales LOOP
  INSERT INTO messages(msgid)
  VALUES v_start_sales;
 END LOOP;
END;

Please select the best answer.
  A. 0
  B. 1
  C. 99
  D. 100
  Answer C is correct.
In the PL/SQL block example, FOR ... LOOP will be executed 99 times starting with 2 and ending with 100. This will result in 99 rows being added to the message table. Thus, answers A, B, and D are incorrect.

Oracle PL/SQL Programming