PLSQL Semester 1 Mid Term Exam - Part II

Section 4
   
  1.  Examine the following code:
DECLARE
v_bool BOOLEAN := FALSE;
v_counter NUMBER(4) := 0;
BEGIN
... Line A
...
END;
Which of the following is NOT valid at line A?
 (1) Points
   
    WHILE NOT v_boolean LOOP
    WHILE v_boolean AND v_counter < 6 LOOP
    WHILE v_counter > 8 LOOP
    WHILE v_counter IN 1..5 LOOP (*)

2.  In a WHILE loop, the statements inside the loop must execute at least once. True or False?  (1) Points
   
    True
    False (*)

3.  Which of the following blocks produces the same output as this block?
BEGIN
    FOR i in 1 .. 3 LOOP
       DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;
(1) Points
   
    DECLARE
    i PLS_INTEGER := 0;
BEGIN
    WHILE i<3 LOOP
       DBMS_OUTPUT.PUT_LINE(i);
       i := i + 1;
    END LOOP;
END;

    DECLARE
    i PLS_INTEGER := 0;
BEGIN
    WHILE i<3 LOOP
       i := i + 1;
       DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END; (*)
 
    DECLARE
    i PLS_INTEGER := 0;
BEGIN
    WHILE i<3 LOOP
       DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
    i := i+ 1;
END;

4.  When using a counter to control a FOR loop, which of the following is true ?  (1) Points
   
    You must have exactly one counter but it is implicitly declared. (*)
    You must have exactly one counter and you must explicitly declare it.
    You can have multiple counters, but you need at least one.
    You don't need a counter; you can test for anything (for example, whether a BOOLEAN is TRUE or FALSE).

5.  In a FOR loop, an implicitly declared counter automatically increases or decreases with each iteration. True or False?  (1) Points
   
    True (*)
    False

6.  You want to display a message which depends on the value of v_grade: if v_grade = 'A' display 'Very Good', if v_grade = 'B' then display 'Good', and so on.
DECLARE
    v_grade CHAR(1);
BEGIN
    CASE v_grade
The next line should be
 (1) Points
   
    WHEN 'A' THEN (*)
    WHEN v_grade = 'A' THEN
    WHEN 'A' THEN;
    IF 'A' THEN

7.  You want to assign a value to v_result which depends on the value of v_grade: if v_grade = 'A' set v_result to 'Very Good' and so on.
DECLARE
    v_grade CHAR(1);
    v_result VARCHAR2(10);
BEGIN
    v_result :=
       CASE v_grade

The next line should be
 (1) Points
   
    WHEN v_grade = 'A' THEN 'Very Good'
    WHEN 'A' THEN 'Very Good';
    WHEN 'A' THEN v_result := 'Very Good';
    WHEN 'A' THEN 'Very Good' (*)

8.  What will be the value of v_result after the following code is executed?
DECLARE
    v_grade CHAR(1) := NULL;
    v_result VARCHAR2(10);
BEGIN
    CASE v_grade
       WHEN ('A' OR 'B') THEN v_result := 'Very Good';
       WHEN ('E' OR 'F') THEN v_result := 'Poor';
       ELSE v_result := 'In Between';
    END CASE;
END;
(1) Points
   
    Poor
    In Between (*)
    Null
    Very Good

 9.  What will be the value of variable c after the following code is executed?
DECLARE
    a BOOLEAN := TRUE;
    b BOOLEAN := FALSE;
    c NUMBER;
BEGIN
    c :=
        CASE
            WHEN a AND b THEN 10
            WHEN NOT a THEN 20
            WHEN a OR b THEN 30
            ELSE 40
        END CASE;
END;
(1) Points
   
    30 (*)
    20
    40
    10

10.  Which of the following is NOT a characteristic of a CASE statement?  (1) Points
   
    It ends with END CASE;
    It can be a complete PL/SQL block
    It returns a value (*)
    It evaluates a condition and performs an action

11.  Examine the following code:
DECLARE
    v_salary NUMBER(6);
    v_constant NUMBER(6) := 15000;
    v_result VARCHAR(6) := 'MIDDLE';
BEGIN
    IF v_salary != v_constant THEN
        v_result := 'HIGH';
    ELSE
        v_result := 'LOW';
    END IF;
END;
What is the final value of v_result?
 (1) Points
   
    HIGH
    LOW (*)
    MIDDLE
    Null

12.  What is the correct form of a compound IF statement?  (1) Points
   
    IF condition
THEN statement1
ELSE statement 2;

    IF condition
THEN statement1
ELSE statement 2;
END IF;

    IF condition;
THEN statement1;
ELSE statement2;
END IF;

    IF condition THEN statement1;
ELSE statement2;
END IF; (*)

 13.  What is the correct form of a simple IF statement?  (1) Points
   
    IF condition THEN statement;
 
    IF condition THEN statement;
END IF; (*)

    IF condition;
THEN statement;
END IF;

    IF condition
THEN statement
ENDIF;

14.  Examine the following code:
DECLARE
    a VARCHAR2(6) := NULL;
    b VARCHAR2(6) := NULL;
BEGIN
    IF a = b THEN
       DBMS_OUTPUT.PUT_LINE('EQUAL');
    ELSIF a != b THEN
       DBMS_OUTPUT.PUT_LINE('UNEQUAL');
    ELSE
       DBMS_OUTPUT.PUT_LINE('OTHER');
    END IF;
END;
Which word will be displayed?
 (1) Points
   
    UNEQUAL
    EQUAL
    Nothing will be displayed
    OTHER (*)

 15.  How many ELSIF statements are you allowed to have in a compound IF statement?  (1) Points
   
    Only one
    As many as you want (*)
    They must match the same number as the number of ELSE statements.
    None; the command is ELSE IF;

 16.  You need to execute a set of statements 10 times, increasing a counter by 1 each time. Which of the following PL/SQL constructs can do this? (Choose three)  (1) Points (Choose all correct answers)
   
    IF ... THEN ... ELSE
    A WHILE loop (*)
    CASE ... WHEN ... THEN
    A FOR loop (*)
    A basic loop (*)

 17.  Examine the following code:
DECLARE
    a BOOLEAN := TRUE;
    b BOOLEAN := FALSE;
    c BOOLEAN := TRUE;
    d BOOLEAN := FALSE;
    game char(4) := 'lost';
BEGIN
    IF ((a AND b) AND (c OR d))
    THEN game := 'won';
    END IF;
What is the value of GAME at the end of this block?
 (1) Points
   
    NULL
     'won'
     'lost' (*)
     False

 18.  Which one of these is NOT a kind of loop?  (1) Points
   
    ASCENDING loop (*)
    FOR loop
    Basic loop
    WHILE loop

 19.  Examine the following block:
DECLARE
    v_counter PLS_INTEGER := 1;
BEGIN
    LOOP
       DBMS_OUTPUT.PUT_LINE(v_counter);
       v_counter := v_counter + 1;
    EXIT WHEN v_counter = 5;
    END LOOP;
END;

What is the last value of V_COUNTER that is displayed?
 (1) Points
   
    5
    6
    4 (*)
    This is an infinite loop; the loop will never finish.

 20.  What kind of statement is best suited for displaying the multiplication table for "sixes": 6x1=6, 6x2=12 ... 6x12=72?  (1) Points
   
    CASE expression
    IF statement
    CASE statement
    LOOP statement (*)

 Section 4
   
 21.  A PL/SQL block contains the following code:
v_counter := 1;
LOOP
EXIT WHEN v_counter = 5;
    v_counter := v_counter + 1;
END LOOP;

What is the value of V_COUNTER after the loop is finished?
 (1) Points
   
    5 (*)
    6
    1
    This is an infinite loop; the loop will never finish.

 22.  Which kind of loop is this?
v_count := 1;
LOOP
    v_count := v_count + 1;
    EXIT WHEN i > 20;
END LOOP;
(1) Points
   
    FOR loop
    IF-THEN loop
    Basic loop (*)
    WHILE loop
    CASE loop

23.  Examine the following code:
BEGIN
   FOR i IN 1..5 LOOP
     FOR j IN 1..8 LOOP
       EXIT WHEN j = 7;
       DBMS_OUTPUT.PUT_LINE(i || j);
     END LOOP;
   END LOOP;
END;
How many lines of output will be displayed when this code is executed?
(1) Points
   
    35
    6
    30 (*)
    40

24.  Examine the following code:
DECLARE
    v_outer_count NUMBER := 1;
    v_inner_count NUMBER := 1;
BEGIN
    LOOP
       LOOP
          v_inner_count := v_inner_count + 1;
          EXIT WHEN v_inner_count > 5; -- Line A
       END LOOP;
       v_outer_count := v_outer_count + 1;
       EXIT WHEN v_outer_count > 3;
    END LOOP;
END;
What happens at Line A when the value of V_INNER_COUNT equals 6?
(1) Points
   
    Both loops are exited and the block's execution is terminated.
    The inner loop is exited but the outer loop continues execution. (*)
    The outer loop is exited but the inner loop continues execution.
    An error condition is returned.

25.  What clause will leave the outer loop at Point A?
DECLARE
  i INTEGER := 0;
BEGIN
   << i_loop >>
  WHILE i <= 10 LOOP
    i := i+1;
     << j_loop >>
    FOR j IN 1..5 LOOP
      _______ WHEN i = j*2; -- Point A
      DBMS_OUTPUT.PUT_LINE(i || j);
    END LOOP;
  END LOOP;
END;
(1) Points
   
    EXIT <<outerloop>>
    EXIT outerloop
    EXIT i_loop (*)
    EXIT j_loop

26.  When coding two nested loops, both loops must be of the same type. For example, you cannot code a FOR loop inside a WHILE loop. True or False?  (1) Points
   
    True
    False (*)

Section 5
   
27.  You want to declare a cursor which locks each row fetched by the cursor. Examine the following code:
DECLARE
  CURSOR emp_curs IS
    SELECT * FROM employees
    FOR -- Point A
Which of the following can NOT be coded at Point A?
 (1) Points
   
    UPDATE;
    UPDATE OF salary;
    UPDATE OF employees; (*)
    UPDATE NOWAIT;

28.  A cursor is declared as:
CURSOR c IS SELECT * FROM departments FOR UPDATE;
After opening the cursor and fetching some rows, you want to delete the most recently fetched row. Which of the following will do this successfully?
 (1) Points
   
    DELETE FROM c WHERE CURRENT OF c;
    DELETE FROM departments WHERE CURRENT OF c; (*)
    DELETE FROM c WHERE CURRENT OF departments;
    DELETE FROM departments WHERE c%ROWCOUNT = 1;
    None of the above.

29.  User TOM has locked a row in the WORKERS table. Now, user DICK wants to open the following cursor:
CURSOR c IS
  SELECT * FROM workers FOR UPDATE NOWAIT;
What will happen when DICK opens the cursor and tries to fetch rows?
 (1) Points
   
    TOM's session is rolled back. DICK's session successfully fetches rows from the cursor.
    DICK's session waits indefinitely.
    Both sessions wait for a few seconds; then the system breaks all locks and both sessions raise an exception.
    DICK's session immediately raises an exception. (*)
    The c%NOWAIT attribute is set to TRUE.

30.  For which type of SQL statement must you use an explicit cursor?  (1) Points
   
    DML statements that process more than one row.
    Queries that return more than one row. (*)
    Data Definition Language (DDL) statements.
    Queries that return a single row.

31.  What will happen when the following code is executed?
DECLARE CURSOR emp_curs IS
    SELECT salary FROM employees;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN emp_curs;
    FETCH emp_curs INTO v_salary;
    CLOSE emp_curs;
    FETCH emp_curs INTO v_salary;
END;
(1) Points
   
    The block will fail and an INVALID_CURSOR exception will be raised. (*)
    The first employee row will be fetched twice.
    The first two employee rows will be fetched.
    The block will fail and a TOO_MANY_ROWS exception will be raised.

32.  Place the following statements in the correct sequence:

OPEN my_curs;
CLOSE my_curs;
CURSOR my_curs IS SELECT my_column FROM my_table;
FETCH my_curs INTO my_variable;
 (1) Points
   
    C,D,A,B
    C,A,D,B (*)
    A,C,D,B
    C,A,B,D

33.  Which of these constructs can be used to fetch multiple rows from a cursor's active set?  (1) Points
   
    A CASE statement
    An IF .... ELSE statement
    A basic loop which includes FETCH and EXIT WHEN statements (*)
    A basic loop which includes OPEN, FETCH and CLOSE statements
   
34.  Examine the following code:
DECLARE
    CURSOR emp_curs IS
       SELECT last_name, salary
       FROM employees
       ORDER BY salary;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
...
Which of the following statements successfully opens the cursor and fetches the first row of the active set?
 (1) Points
   
    OPEN emp_curs;
FETCH emp_curs INTO v_last_name, v_salary; (*)

    OPEN emp_curs;
FETCH emp_curs INTO v_salary, v_last_name;

    OPEN emp_curs;
FETCH FIRST emp_curs INTO v_last_name, v_salary;

    OPEN emp_curs;
FETCH emp_curs;

35.  What will happen when the following code is executed?
DECLARE
    CURSOR emp_curs IS
       SELECT salary FROM employees;
    v_salary employees.salary%TYPE;
BEGIN
    FETCH emp_curs INTO v_salary;
    DBMS_OUTPUT.PUT_LINE(v_salary);
    CLOSE emp_curs;
END;
(1) Points
   
    The first employee's salary will be fetched and displayed.
    All employees' salaries will be fetched and displayed.
    The execution will fail and an error message will be displayed. (*)
    The lowest salary value will be fetched and displayed.

36.  Which of these statements about implicit cursors is NOT true?  (1) Points
   
    They are declared automatically by Oracle for single-row SELECT statements.
    They are declared automatically by Oracle for all DML statements.
    They are declared by the PL/SQL programmer. (*)
    They are opened and closed automatically by Oracle.

37.  The employees table contains 20 rows. What will happen when the following code is executed?
DECLARE
    CURSOR emp_curs IS
       SELECT job_id FROM employees;
    v_job_id employees.job_id%TYPE;
BEGIN
    OPEN emp_curs;
    LOOP
       FETCH emp_curs INTO v_job_id;
       DBMS_OUTPUT.PUT_LINE(v_job_id);
       EXIT WHEN emp_curs%NOTFOUND;
    END LOOP;
    CLOSE emp_curs;
END;
(1) Points
   
    20 job_ids will be displayed.
    The block will fail and an error message will be displayed.
    21 rows of output will be displayed; the first job_id will be displayed twice.
    21 rows of output will be displayed; the last job_id will be displayed twice. (*)

38.  An implicit cursor can be used for a multiple-row SELECT statement. True or False?  (1) Points
   
    True
    False (*)

39.  A cursor has been declared as:
CURSOR c_curs (p_param VARCHAR2) IS
  SELECT * FROM mytable
  WHERE mycolumn = p_param;
Which of the following will open the cursor successfully?
(1) Points
   
    OPEN c_curs(p_param = ABC);
    OPEN c_curs('ABC'); (*)
    OPEN c_curs USING ("ABC");
    p_param := 'ABC';
    OPEN c_curs(p_param);
   
40.  What is one of the advantages of using parameters with a cursor?  (1) Points
   
    You can use a cursor FOR loop.
    You can declare the cursor FOR UPDATE.
    You do not need to DECLARE the cursor at all.
    You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)
    It will execute much faster than a cursor without parameters.

41.  Which of the following statements about the %ISOPEN cursor attribute is true?  (1) Points
   
    You can issue the %ISOPEN cursor attribute only when a cursor is open.
    You can issue the %ISOPEN cursor attribute only when more than one record is returned.
    You can issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)
    If a cursor is open, then the value of %ISOPEN is false.

42.  The DEPARTMENTS table contains four columns. Examine the following code:
DECLARE
    CURSOR dept_curs IS
       SELECT * FROM departments;
    v_dept_rec dept_curs%ROWTYPE;
BEGIN
    OPEN dept_curs;
    FETCH dept_curs INTO v_dept_rec;
    ...
Which one of the following statements is true?
(1) Points
   
    v_dept_rec contains the first four rows of the departments table.
    The FETCH will fail because the structure of v_dept_rec does not match the structure of the cursor.
    v_dept_rec contains the first row of the departments table. (*)
    The block will fail because the declaration of v_dept_rec is invalid.

43.  Which of the following cursor attributes evaluates to TRUE if the cursor is open?  (1) Points
   
    %ISOPEN (*)
    %NOTFOUND
    %FOUND
    %ROWCOUNT
   
44.  Which of the following cursor attributes is set to the total number of rows returned so far?  (1) Points
   
    %ISOPEN
    %NOTFOUND
    %FOUND
    %ROWCOUNT (*)

45.  The following code fragment shows a cursor FOR loop:
FOR emp_record IN emp_cursor LOOP ......

Which of the following do NOT need to be coded explicitly? (Choose three.)
 (1) Points (Choose all correct answers)
   
    OPEN emp_cursor; (*)
     DECLARE CURSOR emp_cursor IS ...
     emp_record emp_cursor%ROWTYPE; (*)
     FETCH emp_cursor INTO emp_record; (*)
     END LOOP;

46.  Examine the following code. To display the salary of an employee, what must be coded at Point A?
DECLARE
    CURSOR emp_curs IS SELECT * FROM employees;
BEGIN
    FOR emp_rec IN emp_curs LOOP
       DBMS_OUTPUT.PUT_LINE( -- what goes here ? );
    END LOOP;
END;
(1) Points
   
    salary
    emp_curs.salary
    emp_rec.salary (*)
    employees.salary
    emp_rec.salary IN emp_curs

47.  What is wrong with the following code?
BEGIN
    FOR emp_rec IN
       (SELECT * FROM employees WHERE ROWNUM < 10
          FOR UPDATE NOWAIT) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec%ROWCOUNT || emp_rec.last_name):
    END LOOP;
END;
(1) Points
   
    You cannot use FOR UPDATE NOWAIT with a cursor FOR loop using a subquery.
    You cannot reference %ROWCOUNT with a cursor FOR loop using a subquery. (*)
    The field EMP_REC.LAST_NAME does not exist.
    You cannot use ROWNUM with a cursor FOR loop.
    The cursor has not been opened.
   
48.  When using multiple nested cursors, what kinds of loops can you use?  (1) Points
   
    Cursor FOR loops only.
    Basic loops only.
    WHILE loops only.
    None of the above.
    All of the above. (*)

49.  How many explicit cursors can be declared and used in a single PL/SQL block?  (1) Points
   
    One or two.
    Only one.
    As many as needed. (*)
    Up to eight cursors.
    None of the above.

50.  You want to display all locations, and the departments in each location. Examine the following code:
DECLARE
  CURSOR loc_curs IS SELECT * FROM locations;
  CURSOR dept_curs(p_loc_id NUMBER) IS
    SELECT * FROM departments WHERE location_id = p_loc_id;
BEGIN
  FOR loc_rec IN loc_curs LOOP
    DBMS_OUTPUT.PUT_LINE(loc_rec.city);
    FOR dept_rec IN dept_curs(-- Point A --) LOOP
      DBMS_OUTPUT.PUT_LINE(dept_rec.department_name);
    END LOOP;
  END LOOP;
END;
What should you code at Point A?
 (1) Points
   
    p_loc_id
    location_id
    null
    LOOP ... END LOOP;
    loc_rec.location_id (*)