Test: Mid Term Exam Semester 1 - Part I 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 The Mid Term Exam for Semester 1 is presented to you as two exams. This is Part I of the Mid Term Exam for Semester 1.  


 Section 1 
      
  1.  Which of the following statements about PL/SQL and SQL is true?  Mark for Review 
(1) Points 
      
    PL/SQL and SQL are both ANSI-compliant. 
  
    PL/SQL and SQL can be used with many types of databases, including Oracle. 
  
    PL/SQL and SQL are both Oracle proprietary programming languages. 
  
    PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*) 
  
      
      Correct  
  
      
  2.  A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:  Mark for Review 
(1) Points 
      
    declarative 
  
    nondeclarative 
  
    procedural (*) 
  
    low level 
  
      
      Correct  
  
      
  3.  The P in PL/SQL stands for:  Mark for Review 
(1) Points 
      
    Processing 
  
    Procedural (*) 
  
    Primary 
  
    Proprietary 
  
      
      Correct  
  
      
  4.  In which part of the PL/SQL block are declarations of variables defined?  Mark for Review 
(1) Points 
      
    Executable 
  
    Exception 
  
    Declarative (*) 
  
    Definition 
  
      
      Correct  
  
      
  5.  Every PL/SQL anonymous block must start with the keyword DECLARE. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
  
      
  6.  Which component of Oracle Application Express is used to enter and run SQL statements and PL/SQL blocks?  Mark for Review 
(1) Points 
      
    Application Builder 
  
    SQL Workshop (*) 
  
    Utilities 
  
    Object Browser 
  
      
      Correct  
  
      
  7.  Which of the following tools can NOT be used to develop and test PL/SQL code?  Mark for Review 
(1) Points 
      
    Oracle Jdeveloper 
  
    Oracle Application Express 
  
    Oracle JSQL (*) 
  
    Oracle iSQL*Plus 
  
      
      Correct  
  
      
  8.  Errors are handled in the Exception part of the PL/SQL block. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  9.  Which statements are optional in a PL/SQL block? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    DECLARE (*) 
  
    BEGIN 
  
    EXCEPTION (*) 
  
    END; 
  
      
      Correct  
  
      
  10.  What kind of block is defined by the following PL/SQL code? 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('My first quiz'); 
END; 
 Mark for Review 
(1) Points 
      
    procedure 
  
    subroutine 
  
    function 
  
    anonymous (*) 
  
      
      Correct  
  
     
Section 1 
      
  11.  PL/SQL can be used not only with an Oracle database, but also with any kind of relational database. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
  
      
  12.  The fact that PL/SQL is portable is a good thing because:  Mark for Review 
(1) Points 
      
    Exceptions can be ported to different operating systems 
  
    Blocks can be sent to the operating system. 
  
    PL/SQL code can be developed on one platform and deployed on another (*) 
  
    PL/SQL code can be run on any operating system without a database 
  
      
      Incorrect. Refer to Section 1.  
  
      
  13.  Which of the following can you use PL/SQL to do?  Mark for Review 
(1) Points 
      
    Update data (DML) 
  
    Develop Web applications using the Web Application Toolkit 
  
    Manage database security 
  
    Create customized reports 
  
    All of the above (*) 
  
      
      Correct  
  
      
 
      
 Section 2 
      
  14.  What is the data type of the variable V_DEPT_TABLE in the following declaration? 
DECLARE 
TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER; v_dept_table dept_table_type; ... 
 Mark for Review 
(1) Points 
      
    Scalar 
  
    Composite (*) 
  
    LOB 
  
      
      Correct  
  
      
  15.  A movie is an example of which category of data type?  Mark for Review 
(1) Points 
      
    Scalar 
  
    Composite 
  
    Reference 
  
    LOB (*) 
  
      
      Correct  
  
      
  16.  A collection is a composite data type. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  17.  Which of the following are PL/SQL lexical units? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    Identifiers (*) 
  
    Table Columns 
  
    Reserved Words (*) 
  
    Anonymous Blocks 
  
    SQL Workshop 
  
      
      Correct  
  
      
  18.  Which of the following are valid identifiers? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    yesterday (*) 
  
    yesterday's date 
  
    number_of_students_in_the_class 
  
    v$testresult (*) 
  
    #students 
  
      
      Correct  
  
      
  19.  Reserved words can be used as identifiers. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
  
      
  20.  A variable must have a value if NOT NULL is specified. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
 
Section 2 
      
  21.  If you are using the %TYPE attribute, you can avoid hard coding the:  Mark for Review 
(1) Points 
      
    Data type (*) 
  
    Table name 
  
    Column name 
  
    Constraint 
  
      
      Correct  
  
      
  22.  Which of the following declarations is invalid?  Mark for Review 
(1) Points 
      
    v_count PLS_INTEGER:=0; 
  
    college_name VARCHAR2(20):='Harvard'; 
  
    v_pages CONSTANT NUMBER; (*) 
  
    v_start_date DATE := sysdate+1; 
  
      
      Correct  
  
      
  23.  Examine the following code. What is the final value of V_MYVAR ? 
DECLARE
    v_myvar NUMBER; 
BEGIN 
    v_myvar := 1 + 2 * 3; 
    v_myvar := v_myvar * 2; 
END; 

 Mark for Review 
(1) Points 
      
    81 
  
    49 
  
    14 (*) 
  
    18 
  
      
      Correct  
  
      
  24.  Examine the following code. What is the final value of V_MYBOOL ? 
DECLARE 
    v_mynumber NUMBER; 
    v_mybool BOOLEAN ; 
BEGIN 
    v_mynumber := 6; 
    v_mybool := (v_mynumber BETWEEN 10 AND 20); 
    v_mybool := NOT (v_mybool); 
END; 

 Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  25.  Which of the following are disadvantages of implicit data type conversions? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    The code is harder to read and understand (*) 
  
    You cannot store alphabetic characters in a variable of data type NUMBER 
  
    If Oracle changes the conversion rules in the future, your code may not work any more (*) 
  
    Oracle cannot implicitly convert a number value to a character string 
  
      
      Correct  
  
      
  26.  TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:  Mark for Review 
(1) Points 
      
    Implicit conversion functions 
  
    Explicit conversion functions (*) 
  
    Character functions 
  
    Operators 
  
      
      Correct  
  
      
  27.  PL/SQL can convert a VARCHAR2 value containing alphabetic characters to a NUMBER value. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
  
      
  28.  If today's date is 14th June 2007, which statement will correctly convert today's date to the value: June 14, 2007 ?  Mark for Review 
(1) Points 
      
    TO_CHAR(sysdate) 
  
    TO_DATE(sysdate) 
  
    TO_DATE(sysdate,'Month DD, YYYY') 
  
    TO_CHAR(sysdate, 'Month DD, YYYY') (*) 
  
      
      Correct  
  
      
  29.  The implicit data type conversion at Point A may not work correctly. Why not?
DECLARE 
    v_mydate DATE; 
BEGIN 
    V_MYDATE := '29-Feb-04'; -- Point A 
END; 

 Mark for Review 
(1) Points 
      
    There are only 28 days in February 
  
    Oracle cannot implicitly convert a character string to a date, even if the string contains a valid date value 
  
    If the database language is not English, 'Feb' has no meaning. (*) 
  
    V_MYDATE has been entered in uppercase 
  
      
      Correct  
  
      
  30.  Which of the following are valid assignment statements? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    v_string = 'Hello'; 
  
    v_string := Hello; 
  
    v_number := 17 + 34; (*) 
  
    v_string := 'Hello'; (*) 
  
    v_date := 28-DEC-06; 
  
      
      Correct  
 
 Section 2 
      
  31.  Is the following variable declaration correct or not ? 
DECLARE 
display_qty CONSTANT NUMBER; 
 Mark for Review 
(1) Points 
      
    Correct. 
  
    Not correct. (*) 
  
      
      Correct  
  
      
  32.  Variables can be assigned a value in both the Executable and Declaration sections of a PL/SQL program. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  33.  When a variable is defined using the CONSTANT keyword, the value of the variable cannot change. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  34.  Identify which of the following assignment statements are valid. (Choose three.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    v_last_name := Chandra; 
  
    v_blackout_date := '31-DEC-2006'; (*) 
  
    v_population := 333444; (*) 
  
    v_music_type := 'ROCK'; (*) 
  
      
      Correct  
  
      
  35.  When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  36.  What will be displayed when the following code is executed? 
DECLARE 
    varA NUMBER := 12; 
BEGIN 
    DECLARE 
       varB NUMBER := 8; 
    BEGIN 
       varA := varA + varB; 
    END; 
    DBMS_OUTPUT.PUT_LINE(varB); 
END; 

 Mark for Review 
(1) Points 
      
    8 
  
    12 
  
    Nothing, the block will fail with an error (*) 
  
    20 
  
    VarB 
  
      
      Correct  
  
      
  37.  An exception occurs within the inner block of two nested blocks. The inner block does not have an EXCEPTION section. What always happens?  Mark for Review 
(1) Points 
      
    Both blocks fail and an error message is displayed by the calling environment 
  
    The exception is propagated to the outer block (*) 
  
    Oracle automatically tries to re-execute the inner block 
  
    The user's database session is automatically disconnected 
  
      
      Correct  
  
      
  38.  When nested blocks are used, which blocks can or must be labeled?  Mark for Review 
(1) Points 
      
    The inner block must be labeled, the outer block can be labeled. 
  
    Both blocks must be labeled 
  
    Nested blocks cannot be labeled 
  
    The outer block must be labeled if it is to be referred to in the inner block. (*) 
  
      
      Correct  
  
      
  39.  Examine the following code. At Line A, we want to assign a value of 22 to the outer block's variable v_myvar. What code should we write at Line A? 
<<outer_block>> 
DECLARE 
    v_myvar NUMBER; 
BEGIN 
    <<inner_block>> 
    DECLARE 
       v_myvar NUMBER := 15; 
    BEGIN 
       -- Line A 
    END; 
END; 

 Mark for Review 
(1) Points 
      
    outer_block.v_myvar := 22; (*) 
  
    v_myvar := 22; 
  
    <<outer_block>>.v_myvar := 22; 
  
    v_myvar(outer_block) := 22; 
  
    We cannot reference the outer block's variable because both variables have the same name 
  
      
      Correct  
  
      
  40.  In the following code, Line A causes an exception. What value will be displayed when the code is executed? 
DECLARE 
    outer_var VARCHAR2(50) := 'My'; 
BEGIN 
    outer_var := outer_var || ' name'; 
    DECLARE 
       inner_var NUMBER; 
    BEGIN 
       inner_var := 'Mehmet'; -- Line A 
       outer_var := outer_var || ' is'; 
    END; 
    outer_var := outer_var || ' Zeynep'; 
EXCEPTION 
    WHEN OTHERS THEN 
       DBMS_OUTPUT.PUT_LINE(outer_var); 
END; 
 Mark for Review 
(1) Points 
      
    My 
  
    My name (*) 
  
    My name is 
  
    My name is Zeynep 
  
      
      Correct  
 
41.  Assume there are 5 employees in Department 10. What happens when the following statement is executed? 
UPDATE employees 
SET salary=salary*1.1; 

 Mark for Review 
(1) Points 
      
    All employees get a 10% salary increase. (*) 
  
    No rows are modified because you did not specify "WHERE department_id=10" 
  
    A TOO_MANY_ROWS exception is raised. 
  
    An error message is displayed because you must use the INTO clause to hold the new salary. 
  
      
      Correct  
  
      
  42.  A PL/SQL block includes the following statement: 
SELECT last_name INTO v_last_name 
FROM employees 
WHERE employee_id=100; 

What is the value of SQL%ISOPEN immediately after the SELECT statement is executed? 
 Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
    Null 
  
    Error. That attribute does not apply for implicit cursors. 
  
      
      Correct  
  
      
  43.  Which SQL statement can NOT use an implicit cursor?  Mark for Review 
(1) Points 
      
    A DELETE statement 
  
    An UPDATE statement 
  
    A SELECT statement that returns multiple rows (*) 
  
    A SELECT statement that returns one row 
  
      
      Correct  
  
      
  44.  Which rows will be deleted from the EMPLOYEES table when the following code is executed? 
DECLARE 
    salary employees.salary%TYPE := 12000; 
BEGIN 
    DELETE FROM employees 
    WHERE salary > salary; 
END; 

 Mark for Review 
(1) Points 
      
    All rows whose SALARY column value is greater than 12000. 
  
    All rows in the table. 
  
    No rows. (*) 
  
    All rows whose SALARY column value is equal to 12000. 
  
      
      Correct  
  
      
  45.  The following code will return the last name of the employee whose employee id is equal to 100: True or False?
DECLARE 
    v_last_name employees.last_name%TYPE; 
    employee_id employees.employee_id%TYPE := 100; 
BEGIN 
    SELECT last_name INTO v_last_name 
    FROM employees 
    WHERE employee_id = employee_id; 
END; 

 Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
  
      
  46.  A variable is declared as: 
DECLARE 
    v_holdit employees.last_name%TYPE; 
BEGIN ... 

Which of the following is a correct use of the INTO clause? 
 Mark for Review 
(1) Points 
      
    SELECT * 
INTO v_holdit 
FROM employees; 
 
  
    SELECT last_name 
INTO v_holdit 
FROM employees; 
 
  
    SELECT last_name 
INTO v_holdit 
FROM employees 
WHERE employee_id=100; 
(*)
 
  
    SELECT salary 
INTO v_holdit 
FROM employees 
WHERE employee_id=100; 
 
  
      
      Incorrect. Refer to Section 3.  
  
      
  47.  A variable is declared as:
DECLARE 
    v_salary employees.salary%TYPE; 
BEGIN 

Which of the following is a correct use of the INTO clause? 
 Mark for Review 
(1) Points 
      
    SELECT salary 
INTO v_salary 
FROM employees 
WHERE employee_id=100; 
(*)
 
  
    SELECT v_salary 
INTO salary 
FROM employees 
WHERE employee_id=100; 
 
  
    SELECT salary 
FROM employees 
INTO v_salary; 
 
  
    SELECT salary 
FROM employees 
WHERE employee_id=100 
INTO v_salary;
 
  
      
      Correct  
  
      
  48.  Which of the following is NOT a good guideline for retrieving data in PL/SQL?  Mark for Review 
(1) Points 
      
    Declare the receiving variables using %TYPE 
  
    The WHERE clause is optional in nearly all cases. (*) 
  
    Specify the same number of variables in the INTO clause as database columns in the SELECT clause. 
  
    THE SELECT statement should fetch exactly one row. 
  
      
      Correct  
  
      
  49.  The following anonymous block of code is run: 
BEGIN 
    INSERT INTO countries (id, name) 
    VALUES ('XA', 'Xanadu'); 
    INSERT INTO countries (id, name) 
    VALUES ('NV','Neverland'); 
    COMMIT; 
    COMMIT; 
    ROLLBACK; 
END; 

What happens when the block of code finishes? 
 Mark for Review 
(1) Points 
      
    You have nothing new; the last ROLLBACK undid the INSERTs. 
  
    You have the rows added twice; there are four new rows. 
  
    You have the two new rows added. (*) 
  
    You get an error; you cannot COMMIT twice in a row. 
  
      
      Correct  
  
      
  50.  The following anonymous block of code is run: 
BEGIN 
    INSERT INTO countries (id, name) 
    VALUES ('XA', 'Xanadu'); 
    SAVEPOINT XA; 
    INSERT INTO countries (id, name) 
    VALUES ('NV','Neverland'); 
    COMMIT; 
    ROLLBACK TO XA; 
END; 

What happens when the block of code finishes? 
 Mark for Review 
(1) Points 
      
    No data is inserted and no errors occur. 
  
    No data is inserted and an error occurs 
  
    Two rows are inserted and no errors occur. 
  
    Two rows are inserted and an error occurs. (*) 
  
      
      Correct  
 
Test: Mid Term Exam Semester 1 - Part II 
 
 

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 The Mid Term Exam for Semester 1 is presented to you as two exams. This is Part II of the Mid Term Exam for Semester 1.  


 Section 4 
      
  1.  What kind of statement is best suited for displaying the multiplication table for "sixes": 6x1=6, 6x2=12 ... 6x12=72?  Mark for Review 
(1) Points 
      
    CASE expression 
  
    IF statement 
  
    CASE statement 
  
    LOOP statement (*) 
  
      
      Correct  
  
      
  2.  Which kind of loop is this? 
v_count := 1; 
LOOP 
    v_count := v_count + 1; 
    EXIT WHEN i > 20; 
END LOOP; 

 Mark for Review 
(1) Points 
      
    FOR loop 
  
    IF-THEN loop 
  
    Basic loop (*) 
  
    WHILE loop 
  
    CASE loop 
  
      
      Correct  
  
      
  3.  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? 
 Mark for Review 
(1) Points 
      
    5 (*) 
  
    6 
  
    1 
  
    This is an infinite loop; the loop will never finish. 
  
      
      Correct  
  
      
  4.  The EXIT statement can be located anywhere inside a basic loop. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  5.  A PL/SQL block contains the following code: 
v_counter := 1; 
LOOP 
    EXIT WHEN v_counter=5; 
END LOOP; 
v_counter := v_counter + 1; 
What is the value of V_COUNTER after the loop is finished? 
 Mark for Review 
(1) Points 
      
    5 
  
    6 
  
    1 
  
    This is an infinite loop; the loop will never finish. (*) 
  
      
      Correct  
  
      
  6.  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?
 Mark for Review 
(1) Points 
      
    NULL 
  
    won' 
  
    lost' (*) 
  
    False 
  
      
      Correct  
  
      
  7.  What is the correct form of a compound IF statement?  Mark for Review 
(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; 
(*)
 
  
      
      Correct  
  
      
  8.  What is the correct name for CASE, LOOP, WHILE, and IF-THEN-ELSE structures ?  Mark for Review 
(1) Points 
      
    Control structures (*) 
  
    Array structures 
  
    Memory structures 
  
    Cursor structures 
  
      
      Correct  
  
      
  9.  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)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    IF ... THEN ... ELSE 
  
    A WHILE loop (*) 
  
    CASE ... WHEN ... THEN 
  
    A FOR loop (*) 
  
    A basic loop (*) 
  
      
      Correct  
  
      
  10.  What is the correct form of a simple IF statement?  Mark for Review 
(1) Points 
      
    IF condition THEN statement; 
 
  
    IF condition THEN statement; 
END IF; (*) 
  
    IF condition; 
THEN statement; 
END IF; 
 
  
    IF condition 
THEN statement 
ENDIF; 
 
  
      
      Correct  
  
     
Section 4 
      
  11.  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?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
  
      
  12.  You want to display multiplication tables for numbers up to 12. The display should look like this: 
1 x 1 = 1 
1 x 2 = 2 
..... 
1 x 12 = 12 
2 x 1 = 2 
2 x 2 = 4 
..... 
2 x 12 = 24 
3 x 1 = 3 
..... 
..... 
12 x 12 = 144 
Which of the following is an efficient way to do this in PL/SQL?  Mark for Review 
(1) Points 
      
    Use two nested FOR loops. (*) 
  
    Store all the numbers from 1 to 144 in a table, then fetch and display them using a cursor. 
  
    Create a function which accepts two numbers as IN parameters and returns their product. Invoke the function 144 times. 
  
    Write an anonymous block which contains 144 calls to DBMS_OUTPUT, each looking like: DBMS_OUTPUT.PUT_LINE('7 x 9 = 63'); 
  
      
      Correct  
  
      
  13.  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?  Mark for Review 
(1) Points 
      
    35 
  
    6 
  
    30 (*) 
  
    40 
  
      
      Incorrect. Refer to Section 4.  
  
      
  14.  In the following code fragment, you want to exit from the outer loop at Line A if v_number = 6. Which statement would you write on Line A? 
<<big_loop>> 
WHILE condition_1 LOOP 
    <<small_loop>> 
    FOR i IN 1..10 LOOP 
       DBMS_OUTPUT.PUT_LINE(i); 
       -- Line A 
    END LOOP; 
END LOOP; 

 Mark for Review 
(1) Points 
      
    IF v_number = 6 THEN EXIT; 
  
    EXIT outer_loop WHEN v_number = 6; 
  
    EXIT big_loop WHEN v_number = 6; (*) 
  
    EXIT small_loop WHEN v_number = 6; 
  
      
      Correct  
  
      
  15.  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' THEN v_result := 'Very Good'; 
       WHEN 'F' THEN v_result := 'Poor'; 
       ELSE v_result := 'In Between'; 
    END;
END; 

 Mark for Review 
(1) Points 
      
    Poor 
  
    In Between (*) 
  
    Null 
  
    Very Good 
  
      
      Correct  
  
      
  16.  What will be the value of variable c after the following code is executed? 
DECLARE 
    a BOOLEAN := TRUE; 
    b BOOLEAN := NULL; 
    c NUMBER; 
BEGIN 
    IF a AND b THEN c := 2; 
       ELSIF a OR b THEN c := 0; 
       ELSE c := 1; 
    END IF; 
END; 

 Mark for Review 
(1) Points 
      
    1 
  
    Null 
  
    0 (*) 
  
    2 
  
      
      Correct  
  
      
  17.  Which of the following is NOT a characteristic of a CASE statement?  Mark for Review 
(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 
  
      
      Correct  
  
      
  18.  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 
 Mark for Review 
(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' (*) 
  
      
      Correct  
  
      
  19.  What will be the value of v_sal_desc after the following code is executed? 
DECLARE 
    v_salary NUMBER(6,2) := NULL; 
    v_sal_desc VARCHAR2(10); 
BEGIN 
    CASE 
       WHEN v_salary < 10000 THEN v_sal_desc := 'Low Paid'; 
       WHEN v_salary >= 10000 THEN v_sal_desc := 'High Paid'; 
    END CASE; 
END; 

 Mark for Review 
(1) Points 
      
    High Paid 
  
    Low Paid 
  
    Null 
  
    The code will fail and return an exception (*) 
  
      
      Correct  
  
      
  20.  What will happen when the following code is executed? 
BEGIN 
FOR i in 1 ..3 LOOP 
    DBMS_OUTPUT.PUT_LINE (i); 
    i := i + 1; 
END LOOP; 
END; 

 Mark for Review 
(1) Points 
      
    It will display 1, 2, 3. 
  
    It will display 2, 3, 4. 
  
    It will result in an error because you cannot modify the counter in a FOR loop. (*) 
  
    It will result in an error because the counter was not explicitly declared. 
  
      
      Correct  
 
Section 4 
      
  21.  Which statement best describes when a WHILE loop shouild be used?  Mark for Review 
(1) Points 
      
    When the number of iterations is known 
  
    When repeating a sequence of statements until the controlling condition is no longer true (*) 
  
    When assigning a value to a Boolean variable 
  
    When testing whether a variable is null 
  
      
      Correct  
  
      
  22.  In a FOR loop, an implicitly declared counter automatically increases or decreases with each iteration. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  23.  Which statement best describes when a FOR loop should be used?  Mark for Review 
(1) Points 
      
    When the number of iterations is known (*) 
  
    When testing the value in a Boolean variable 
  
    When the controlling condition must be evaluated at the start of each iteration 
  
      
      Correct  
  
      
  24.  When using a counter to control a FOR loop, which of the following is true ?  Mark for Review 
(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). 
  
      
      Correct  
  
      
  25.  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; 

 Mark for Review 
(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; 
 
  
      
      Correct  
  
      
 
      
 Section 5 
      
  26.  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; 

 Mark for Review 
(1) Points 
      
    salary 
  
    emp_curs.salary 
  
    emp_rec.salary (*) 
  
    employees.salary 
  
    emp_rec.salary IN emp_curs 
  
      
      Correct  
  
      
  27.  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.) 
 Mark for Review 
(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; 
  
      
      Correct  
  
      
  28.  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; 

 Mark for Review 
(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. 
  
      
      Correct  
  
      
  29.  Which of the following cursor attributes is set to the total number of rows returned so far?  Mark for Review 
(1) Points 
      
    %ISOPEN 
  
    %NOTFOUND 
  
    %FOUND 
  
    %ROWCOUNT (*) 
  
      
      Correct  
  
      
  30.  Examine the following code fragment: 
DECLARE 
    CURSOR emp_curs IS 
       SELECT first_name, last_name FROM employees; 
    v_emp_rec emp_curs%ROWTYPE; 
BEGIN 
    ... 
    FETCH emp_curs INTO v_emp_rec; 
    DBMS_OUTPUT.PUT_LINE(... Point A ...); 
   ... 

To display the fetched last name, what should you code at Point A? 
 Mark for Review 
(1) Points 
      
    v_emp_rec.last_name (*) 
  
    v_emp_rec(last_name) 
  
    v_emp_rec 
  
    last_name 
  
    None of the above 
  
      
      Correct  
 
Section 5 
      
  31.  The following cursor has been declared: 
    CURSOR emp_curs IS 
       SELECT first_name, last_name, job_id, salary 
       FROM employees; 

Which of the following correctly declares a composite record with the same structure as the cursor? 
 Mark for Review 
(1) Points 
      
    emp_rec emp_rec%ROWTYPE; 
  
    emp_rec emp_curs%TYPE; 
  
    emp_rec emp_curs%ROWTYPE; (*) 
  
    emp_rec cursor%ROWTYPE; 
  
      
      Correct  
  
      
  32.  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? 
 Mark for Review 
(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. 
  
      
      Correct  
  
      
  33.  Which of the following cursor attributes evaluates to TRUE if the cursor is open?  Mark for Review 
(1) Points 
      
    %ISOPEN (*) 
  
    %NOTFOUND 
  
    %FOUND 
  
    %ROWCOUNT 
  
      
      Correct  
  
      
  34.  Assume that you have declared a cursor called C_EMP. Which of the following statements about C_EMP is correct? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    You can use c_emp%NOTFOUND to exit a loop. (*) 
  
    You can fetch rows when c_emp%ISOPEN evaluates to FALSE. 
  
    You can use c_emp%ROWCOUNT to return the number of rows returned by the cursor so far. (*) 
  
    You can use c_emp%FOUND after the cursor is closed. 
  
      
      Correct  
  
      
  35.  You want to display each row from the DEPARTMENTS table, and immediately underneath it, a list of all EMPLOYEES in that department. Which of the following is a good way to do this?  Mark for Review 
(1) Points 
      
    Use a single cursor, declared as SELECT * FROM employees GROUP BY department_id; 
  
    Use two cursors, one for each of the two tables. Declare the EMPLOYEES cursor with a parameter for the DEPARTMENT_ID. (*) 
  
    Write a SELECT statement which JOINs the two tables, and use CONNECT BY PRIOR and LEVEL to display the rows in the correct order. 
  
    Use a single cursor with a cursor FOR loop. 
  
    Change the physical model so that all employee and department data is in a single table. 
  
      
      Correct  
  
      
  36.  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?  Mark for Review 
(1) Points 
      
    p_loc_id 
  
    location_id 
  
    null 
  
    LOOP ... END LOOP; 
  
    loc_rec.location_id (*) 
  
      
      Correct  
  
      
  37.  What is wrong with the following code? 
DECLARE 
CURSOR emp_curs(p_dept_id NUMBER) IS 
SELECT * FROM employees WHERE department_id = p_dept_id; 
BEGIN 
FOR dept_rec IN (SELECT * FROM departments) LOOP 
DBMS_OUTPUT.PUT_LINE(dept_rec.department_name); 
FOR emp_rec IN emp_curs(dept_rec.department_id) LOOP 
DBMS_OUTPUT.PUT_LINE(emp_rec.last_name); 
END LOOP; 
END LOOP; 
END; 
 Mark for Review 
(1) Points 
      
    The DEPARTMENTS cursor must be declared with a parameter. 
  
    You cannot use a cursor with a subquery in nested loops. 
  
    You cannot use two different kinds of loop in a single PL/SQL block. 
  
    EMP_CURS should not be DECLAREd explicitly; it should be coded as a subquery in a cursor FOR loop. 
  
    Nothing is wrong. The block will execute successfully and display all departments and the employees in those departments. (*) 
  
      
      Correct  
  
      
  38.  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?  Mark for Review 
(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); 
  
      
      Correct  
  
      
  39.  Examine the following declaration of a cursor with a parameter. What should be coded at Point A? DECLARE 
CURSOR emp_curs(-- Point A --) IS 
SELECT * FROM employees 
WHERE job_id = p_job_id; 
 Mark for Review 
(1) Points 
      
    p_job_id 
  
    ST_CLERK' 
  
    p_job_id VARCHAR2(25) 
  
    p_job_id VARCHAR2 (*) 
  
    job_id VARCHAR2 
  
      
      Correct  
  
      
  40.  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?  Mark for Review 
(1) Points 
      
    UPDATE; 
  
    UPDATE OF salary; 
  
    UPDATE OF employees; (*) 
  
    UPDATE NOWAIT; 
  
      
      Correct  
 
Section 5 
      
  41.  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?  Mark for Review 
(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. 
  
      
      Correct  
  
      
  42.  User MARY has locked a row of the EMPLOYEES table. Now, user SAEED tries to open the following cursor: 
CURSOR c IS 
SELECT * FROM employees 
FOR UPDATE WAIT 5; 
What will happen when SAEED's session tries to fetch the row that MARY has locked?  Mark for Review 
(1) Points 
      
    SAEED's session successfully fetches the first 5 rows and then waits indefinitely to fetch the 6th row. 
  
    SAEED's session waits for 5 seconds, and then raises an exception if MARY has not unlocked the row. (*) 
  
    SAEED's session waits for 5 seconds, then SAEED is disconnected from the database. 
  
    SAEED's session waits for 5 seconds, then MARY's session is rolled back. 
  
    SAEED's session waits for 5 minutes, and then raises an exception if MARY has not unlocked the row. 
  
      
      Correct  
  
      
  43.  An explicit cursor must always be declared, opened and closed by the PL/SQL programmer. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Incorrect. Refer to Section 5.  
  
      
  44.  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; 

 Mark for Review 
(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. (*) 
  
      
      Correct  
  
      
  45.  For which type of SQL statement must you use an explicit cursor?  Mark for Review 
(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. 
  
      
      Correct  
  
      
  46.  After a cursor has been closed, it can be opened again in the same PL/SQL block. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
      
  47.  Which of these constructs can be used to fetch multiple rows from a cursor's active set?  Mark for Review 
(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 
  
      
      Correct  
  
      
  48.  Which of these statements about implicit cursors is NOT true?  Mark for Review 
(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. 
  
      
      Correct  
  
      
  49.  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; 
 Mark for Review 
(1) Points 
      
    C,D,A,B 
  
    C,A,D,B (*) 
  
    A,C,D,B 
  
    C,A,B,D 
  
      
      Correct  
  
      
  50.  An implicit cursor can be used for a multiple-row SELECT statement. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
 
