Quizzuri Oracle

PL/SQL Quizz Section#4

Posted by Feroviarul on

Section 4: Program structures to control execution workflow

4.01. Conditional control if statements

1.  Which one of the following is correct syntax for an IF statement?
♦IF condition THEN DO statement1; statement2; END IF;
♦IF condition THEN statement1; statement2; END IF; (*)
♦IF condition THEN statement1; statement2; ENDIF;
♦IF condition THEN statement1; AND statement2; END IF;
2.  What will be displayed when this block is executed? DECLARE v_bool1 BOOLEAN := TRUE; v_bool2 BOOLEAN; v_char VARCHAR(4) := ‘up’; BEGIN IF (v_bool1 AND v_bool2) THEN v_char:=’down’; ELSE v_char:=’left’; END IF; DBMS_OUTPUT.PUT_LINE(v_char); END;
♦up
♦down
♦left (*)
♦null
3.  We want to execute one of three statements depending on whether the value in V_VAR is 10, 20 or some other value. What should be coded at Line A? IF v_var = 10 THEN statement1; — Line A statement2; ELSE statement3; END IF;♦ELSE IF v_var = 20 THEN
♦ELSIF v_var = 20
♦ELSIF v_var = 20 THEN (*)
♦IF v_var = 20 THEN

4.  What will be displayed when this block is executed? DECLARE v_bool1 BOOLEAN := NULL; v_bool2 BOOLEAN := NULL; v_char VARCHAR(10) := ‘Start’; BEGIN IF (v_bool1 = v_bool2) THEN v_char:=’Equal’; ELSE v_char:=’Not equal’; END IF; DBMS_OUTPUT.PUT_LINE(v_char); END;
♦Equal
♦Not equal (*)
♦Start
♦Nothing will be displayed. The block will fail because you cannot compare two null values.

5.  You want to repeat a set of statements 100 times, incrementing a counter each time. What kind of PL/SQL control structure would you use?
♦IF…THEN…ELSE
♦IF…THEN…ELSIF…ELSE
♦CASE…WHEN…THEN
♦A loop. (*)

6.  Which of the following statements are true about PL/SQL conditional control structures such as IF … , CASE … and loops?
♦They allow the programmer to use logical tests to determine which statements are executed and which are not.
♦They allow a set of statements to be executed repeatedly (i.e. more than once).
♦They determine a course of action based on conditions.
♦All of the above. (*)

7.  Look at the following (badly written) code:
age := 5; IF age<30 THEN mature := ‘adult’;
ELSIF age<22 THEN mature := ‘teenager’;
ELSIF age<13 THEN mature := ‘child’;
END IF;
DBMS_OUTPUT.PUT_LINE(mature);

What will be displayed when this code is executed?

♦child
♦teenager
♦adult (*)
♦adultteenagerchild

8.  What is wrong with the following trivial IF statement:
IF (v_job=’President’)
THEN v_salary := 10000;

♦IF and THEN must be on the same line: IF (v_job=’President’) THEN …
♦The condition should be coded: IF (v_job := ‘President’)
♦END IF; is missing (*)
♦ELSE is missing

4.02. Conditional control case statements

1.  What is wrong with the following trivial IF statement:
IF (v_job=’President’)
THEN v_salary := 10000;
♦IF and THEN must be on the same line: IF (v_job=’President’) THEN …
♦The condition should be coded: IF (v_job := ‘President’)
♦END IF; is missing (*)
♦ELSE is missing
2.  What will be displayed when the following block is executed?
DECLARE
v_age1 NUMBER(3);
v_age2 NUMBER(3);
v_message VARCHAR2(20);
BEGIN
CASE
WHEN v_age1 = v_age2 THEN v_message := ‘Equal’;
WHEN v_age1 <> v_age2 THEN v_message := ‘Unequal’;
ELSE v_message := ‘Undefined’;
END CASE;
DBMS_OUTPUT.PUT_LINE(v_message);
END;
♦Equal
♦Undefined (*)
♦Unequal
♦Nothing will be displayed because V_MESSAGE is set to NULL.
3.  What will be displayed when the following block is executed?
DECLARE
v_age NUMBER(3);
v_gender VARCHAR2(6) := ‘Female’;
v_status VARCHAR2(20);
BEGIN
CASE
WHEN v_age >= 18 AND v_gender = ‘Male’ THEN v_status := ‘Adult Male’;
WHEN v_age >= 18 AND v_gender = ‘Female’ THEN v_status := ‘Adult Female’;
WHEN v_age < 18 AND v_gender = ‘Male’ THEN v_status := ‘Junior Male’;
WHEN v_age < 18 AND v_gender = ‘Female’ THEN v_status := ‘Junior Female’;
ELSE v_status := ‘Other Value’;
END CASE;
DBMS_OUTPUT.PUT_LINE(v_status);
END;
♦Adult Male
♦Junior Female
♦Other Value (*)
♦Nothing will be displayed because V_STATUS is set to NULL.
4.  Examine the following code:
DECLARE
v_score NUMBER(3);
v_grade CHAR(1);
BEGIN
v_grade := CASE v_score
— Line A
….
The CASE expression must convert a numeric score to a letter grade: 90 -> A, 80 -> B, 70 -> C and so on. What should be coded at Line A?
♦WHEN 90 THEN grade := ‘A’
♦WHEN 90 THEN v_grade := ‘A’;
♦WHEN 90 THEN ‘A’ (*)
♦WHEN 90 THEN ‘A’;
5.  How must you end a CASE expression?
♦END; (*)
♦ENDIF;
♦END CASE;
♦ENDCASE;

6.  How must you end a CASE statement?

♦END;
♦END CASE; (*)
♦END IF;
♦ENDCASE;

7.  Examine the following code:
DECLARE
v_a BOOLEAN;
v_b BOOLEAN := FALSE;
v_c BOOLEAN ;
BEGIN
v_c := (v_a AND v_b);
— Line A
….
END;

What is the value of V_C at Line A?
♦True
♦False (*)
♦NULL
♦Undefined
8.  Look at the following code:
DECLARE
x BOOLEAN := FALSE;
y BOOLEAN := FALSE;
z BOOLEAN ;
BEGIN
z := (x OR NOT y);
— Line A
….
END;
What is the value of Z at Line A?
♦True (*)
♦False
♦NULL
♦An error will occur because you cannot combine two Boolean variables using “NOT”.

4.03. Iterative control basic loops

1.  How many EXIT statements can be coded inside a basic loop?
♦None.
♦One only.
♦Two.
♦As many as you need, there is no limit. (*)

2.  Examine the following code:
DECLARE
v_count NUMBER := 0;
v_string VARCHAR2(20);
BEGIN
LOOP
v_string := v_string || ‘x’;
IF LENGTH(v_string) > 10 THEN
EXIT;
END IF;
v_count := v_count + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_count);
END;

What will be displayed when this block is executed?
♦9
♦10 (*)
♦11
♦xxxxxxxxxxx
3.  Which kind of loop is this?
i := 10;
LOOP
i := i + 1;
EXIT WHEN i > 30;
END LOOP;
♦A FOR loop.
♦A WHILE loop.
♦A basic loop. (*)
♦An infinite loop.
♦A nested loop.
4.  What will be displayed when this block is executed?
DECLARE
v_count NUMBER := 10;
v_result NUMBER;
BEGIN
LOOP
v_count := v_count – 1;
EXIT WHEN v_count < 5;
v_result := v_count * 2;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_result);
END;
♦8
♦10 (*)
♦12
♦NULL
5.  Look at this code:
DECLARE
v_bool BOOLEAN := TRUE;
v_date DATE;
BEGIN
LOOP
EXIT WHEN v_bool;
SELECT SYSDATE INTO v_date FROM dual;
END LOOP;
END;
How many times will the SELECT statement execute?
♦Once.
♦Twice.
♦Never (the SELECT will not execute at all) (*)
♦An infinite number of times because the EXIT condition will never be true
6.  You want to calculate and display the multiplication table for “sevens”: 7×1=7, 7×2=14, 7×3=21 and so on. Which kind of PL/SQL construct is best for this?
♦A loop (*)
♦A CASE statement
♦IF … END IF;
♦A Boolean variable.
7.  What are the three kinds of loops in PL/SQL?
♦ascending, descending, unordered
♦infinite, finite, recursive
♦IF, CASE, LOOP
♦FOR, WHILE, basic (*)

8.  For which one of these tasks should you use a PL/SQL loop?
♦Updating the salary of one employee.
♦Executing the same set of statements repeatedly until a condition becomes true. (*)
♦Deciding whether a value is within a range of numbers.
♦Making a decision based on whether a condition is true or not.

4.04. Iterative control while and for loops

1.  Which statement best describes when a FOR loop should be used?
♦When an EXIT WHEN statement must be coded.
♦When an implicitly declared counter must increase by 1 in each iteration of the loop. (*)
♦When we want to exit from the loop when a Boolean variable becomes FALSE.
♦When the statements inside the loop must execute at least once.
2.  Look at the following block:
DECLARE
v_date DATE := SYSDATE;
BEGIN
WHILE v_date < LAST_DAY(v_date) LOOP
v_date := v_date + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_date);
END;
If today’s date is 17th April 2007, what will be displayed when this block executes?
♦01-MAY-07
♦31-DEC-07
♦4/30/2007 (*)
♦4/17/2007
3.  Look at this code fragment:
FOR i IN 1 .. 3 LOOP
i := 4;
DBMS_OUTPUT.PUT_LINE(‘The counter is: ‘ || i);
END LOOP;
How many lines of output will be displayed?
♦One
♦Three
♦Four
♦The block will fail because you cannot change the value of i inside the loop. (*)
4.  Look at the following code fragment:
i := 2;
WHILE i < 3 LOOP
i := 4;
DBMS_OUTPUT.PUT_LINE(‘The counter is: ‘ || i);
END LOOP;
How many lines of output will be displayed?
♦No lines
♦One line (*)
♦Two lines
♦The block will fail because you cannot use DBMS_OUTPUT.PUT_LINE inside a loop.
5.  You want a loop that counts backwards from 10 through 1. How do you code that?
♦FOR i IN 10 .. 1 LOOP
♦FOR i IN 1 .. 10 BY -1 LOOP
♦FOR i IN REVERSE 1 .. 10 LOOP (*)
♦FOR i IN REVERSE 10 .. 1 LOOP

6.  You should use a WHILE loop when the number of iterations of the loop is known in advance. True or False?
♦True
♦False (*)

7.  In a WHILE loop, the controlling condition is checked at the start of each iteration. True or False?
♦True (*)
♦False

4.05. Iterative control nested loops

1.  Look at the following code:
DECLARE
v_blue NUMBER(3) := 0;
v_red NUMBER(3) := 0;
BEGIN
<<blue>> LOOP
v_blue := v_blue + 1;
EXIT WHEN v_blue > 10;
<<red>> LOOP
v_red := v_red + 1;
EXIT WHEN v_red > 10;
— Line A
END LOOP red;
END LOOP blue;
END;
What should you code at Line A to exit from the outer loop?
♦EXIT;
♦EXIT red;
♦EXIT <<blue>>;
♦EXIT blue; (*)
2.  When the following code is executed, how many lines of output will be displayed?
BEGIN
FOR i IN 1..5 LOOP
FOR j IN 1..8 LOOP
DBMS_OUTPUT.PUT_LINE(i || ‘,’ || j);
END LOOP;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
♦80
♦45 (*)
♦14
♦41
3.  What will be displayed when the following block is executed?:
DECLARE
x NUMBER(6) := 0 ;
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..5 LOOP
x := x+1 ;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE(x);
END;
♦5
♦10
♦15
♦50 (*)
4.  Which one of these statements about using nested loops is true?
♦All the loops must be labelled
♦The outer loop must be labelled, but the inner loop need not be labelled
♦The outer loop must be labelled if you want to exit the outer loop from within the inner loop (*)
♦Both loops can have the same label
5.  What statement allows you to exit the outer loop at Point A in the following block?
DECLARE
v_outer_done CHAR(3) := ‘NO’;
v_inner_done CHAR(3) := ‘NO’;
BEGIN
LOOP — outer loop
…
LOOP — inner loop
…
… — Point A
EXIT WHEN v_inner_done = ‘YES’;
…
END LOOP;
…
EXIT WHEN v_outer_done = ‘YES’;
…
END LOOP;
END;
♦EXIT AT v_outer_done = ‘YES’;
♦EXIT WHEN v_outer_done = ‘YES’; (*)
♦WHEN v_outer_done = YES EXIT;
♦EXIT <<outer loop>>;
6.  What type of loop statement would you write for Point A?
BEGIN
FOR v_outerloop IN 1..3 LOOP
— Point A
DBMS_OUTPUT.PUT_LINE(‘Outer loop is:’||v_outerloop||
‘ and inner loop is: ‘||v_innerloop);
END LOOP;
END LOOP;
END;
♦WHILE v_innerloop <=5 LOOP
♦FOR v_innerloop 1..5 LOOP (*)
♦LOOP
♦WHILE v_outerloop<v_innerloop LOOP

Leave a comment