PLSQL Semester 1 Mid Term Exam - Part I

 Section 1
     
  1.  The fact that PL/SQL is portable is a good thing because:  (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
 
 2.  Which of the following statements about exception handling in PL/SQL is false?  (1) Points
     
    You can prepare for database exceptions by creating exception handlers.
    You can prepare for application exceptions by creating exception handlers.
    Exception handling code tells your program what to do when an error is encountered.
    Exception handling code can be grouped together in a PL/SQL block.
    None of the above (*)

3.  PL/SQL can be used not only with an Oracle database, but also with any kind of relational database. True or False?  (1) Points
     
    True
    False (*)
 
 4.  What kind of block is defined by the following PL/SQL code?
BEGIN
    DBMS_OUTPUT.PUT_LINE('My first quiz');
END;
 (1) Points
     
    procedure
    subroutine
    function
    anonymous (*)

 5.  In which part of the PL/SQL block are declarations of variables defined?  (1) Points
     
    Executable
    Exception
    Declarative (*)
    Definition
 
 6.  Which lines of code will correctly display the message "The cat sat on the mat"? (Choose two.)  (1) Points (Choose all correct answers)
     
    DBMS_OUTPUT.PUT_LINE('The cat sat on the mat'); (*)
    DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
    DBMS_OUTPUT.PUT_LINE('The cat' || 'sat on the mat');
    DBMS_OUTPUT.PUT_LINE('The cat sat ' || 'on the mat'); (*)
 
 7.  Which component of Oracle Application Express is used to enter and run SQL statements and PL/SQL blocks?  (1) Points
     
    Application Builder
    SQL Workshop (*)
    Utilities
    Object Browser

8.  Which keywords must be included in every PL/SQL block? (Choose two.)  (1) Points (Choose all correct answers)
     
    DECLARE
    END; (*)
    EXCEPTION
    BEGIN (*)
    DBMS_OUTPUT.PUT_LINE
 
9.  Which statements are optional in a PL/SQL block? (Choose two.)  (1) Points (Choose all correct answers)
     
    DECLARE (*)
    BEGIN
    EXCEPTION (*)
    END;
 
10.  PL/SQL extends SQL by including all of the following except:  (1) Points
     
    variables
    conditional statements
    reusable program units
    constants
    nonprocedural constructs (*)

11.  SQL is a common access language for many types of databases, including Oracle. True or False?  (1) Points
     
    True (*)
    False

12.  A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:  (1) Points
     
    declarative
    nondeclarative
    procedural (*)
    low level

Section 2
     
13.  Valid identifiers begin with a  (1) Points
     
    Number
    Letter (*)
    Special character
     
 14.  Delimiters are _____ that have special meaning to the Oracle database.  (1) Points
     
    identifiers
    variables
    symbols (*)
 
15.  Which of the following are valid identifiers? (Choose two.)  (1) Points (Choose all correct answers)
     
    yesterday (*)
    yesterday's date
    number_of_students_in_the_class
    v$testresult (*)
    #students
 
16.  What will be displayed when the following code is executed?
DECLARE
    x VARCHAR2(6) := 'Chang';
BEGIN
    DECLARE
       x VARCHAR2(12) := 'Susan';
    BEGIN
       x := x || x;
    END;
    DBMS_OUTPUT.PUT_LINE(x);
END;
(1) Points
     
    Susan
    Chang (*)
    ChangChang
    SusanChang
    The code will fail with an error
 
17.  When nested blocks are used, which blocks can or must be labeled?  (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. (*)
 
18.  When an exception occurs within a PL/SQL block, the remaining statements in the executable section of the block are skipped. True or False?  (1) Points
     
    True (*)
    False
 
19.  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;
(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
     
20.  Examine the following code. Line A causes an exception. What will be displayed when the block is executed?
DECLARE
    var_a NUMBER := 6;
    var_b DATE;
BEGIN
    var_a := var_a * 2;
    var_b := '28 December 2006'; -- Line A
    var_a := var_a * 2;
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(var_a);
END;
 (1) Points
     
    12 (*)
    24
    6
    Nothing will be displayed

Section 2
     
21.  Which of the following declarations is invalid?  (1) Points
     
    v_count PLS_INTEGER:=0;
    college_name VARCHAR2(20):='Harvard';
    v_pages CONSTANT NUMBER; (*)
    v_start_date DATE := sysdate+1;
 
22.  If you are using the %TYPE attribute, you can avoid hard coding the:  (1) Points
     
    Data type (*)
    Table name
    Column name
    Constraint

23.  A variable must have a value if NOT NULL is specified. True or False?  (1) Points
     
    True (*)
    False
 
24.  Which of the following will help to make code easier to read?  (1) Points
     
    Naming variables.
    Using %Type.
    Including comments in the code. (*)
     
25.  Which of these are examples of good programming practice? (Choose three.)  (1) Points
(Choose all correct answers)
     
    Declare two variables on each line of code.
    Use a NOT NULL constraint when a variable must have a value (*)
    Use a clear and consistent naming convention for variables. (*)
    Declare variables with the same names as table columns.
    Do not rely on implicit data type conversions. (*)

26.  To comment a single line of code, use two dashes after the comment. True or False?  (1) Points
     
    True
    False (*)
 
27.  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; ...
 (1) Points
     
    Scalar
    Composite (*)
    LOB
 
28.  A collection is a composite data type. True or False?  (1) Points
     
    True (*)
    False
 
 29.  ______ are meant to store large amounts of data.  (1) Points
     
    Variables
    Scalar data types
    LOBs (*)

30.  Variables can be used in the following ways in a PL/SQL block. (Choose two.)  (1) Points (Choose all correct answers)
     
    To store data values. (*)
    To rename tables and columns.
    To refer to a single data value several times. (*)
    To comment code.
 
Section 2
     
31.  Assignment statements can continue over several lines in PL/SQL. True or False?  (1) Points
     
    True (*)
    False

32.  Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;
 (1) Points
     
    Correct.
    Not correct. (*)
 
33.  Is the following variable declaration correct or not ?
DECLARE
display_qty CONSTANT NUMBER;
 (1) Points
     
    Correct.
    Not correct. (*)
 
34.  PL/SQL can convert a VARCHAR2 value containing alphabetic characters to a NUMBER value. True or False?  (1) Points
     
    True
    False (*)
 
35.  What is wrong with this assignment statement?
myvar :=  'To be or not to be';
'That is the question';
(1) Points
     
    An assignment statement must be a single line of code
    Nothing is wrong, the statement is fine
    An assignment statement must have a single semicolon at the end (*)
    "myvar" is not a valid name for a variable
    Character literals should not be enclosed in quotes
 
36.  Which of the following are disadvantages of implicit data type conversions? (Choose two.)  (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
 
37.  Which of the following are valid assignment statements? (Choose two.)  (1) Points (Choose all correct answers)
     
    v_string = 'Hello';
    v_string := Hello;
    v_number := 17 + 34; (*)
    v_string := 'Hello'; (*)
    v_date := 28-DEC-06;
 
38.  Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= '300';
5 END;
After line 4, what is the value of x?
 (1) Points
     
    '300'
    300 (*)
    NULL

39.  TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:  (1) Points
     
    Implicit conversion functions
    Explicit conversion functions (*)
    Character functions
    Operators
 
40.  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;
(1) Points
     
    81
    49
    14 (*)
    18

Section 2
     
41.  When you use a function to convert data types in a PL/SQL program, it is called ______ conversion.  (1) Points
     
    Explicit (*)
    Implicit
    TO_CHAR

Section 3
     
42.  Which of the following best describes a database transaction?  (1) Points
     
    All the DML statements in a single PL/SQL block
    A related set of SQL DML statements which must be executed either completely or not at all (*)
    A single SQL statement that updates multiple rows of a table
    A SELECT statement based on a join of two or more database tables
 
43.  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?
(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. (*)
 
44.  Which one of these SQL statements can be directly included in a PL/SQL executable block?  (1) Points
     
    SELECT last_name FROM employees
WHERE employee_id=100;

    DESCRIBE employees;

    UPDATE employees
SET last_name='Smith'; (*)

    DROP TABLE employees;

45.  A variable is declared as:
DECLARE
    v_salary employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
 (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;

46.  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;
(1) Points
     
    True
    False (*)
 
47.  Which of the following is NOT a good guideline for retrieving data in PL/SQL?  (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.
 
48.  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?
 (1) Points
     
    True
    False (*)
    Null
    Error. That attribute does not apply for implicit cursors.
 
49.  There are no employees in Department 77. What will happen when the following block is executed?
BEGIN
DELETE FROM employees
WHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
(1) Points
     
    A NO_DATA_FOUND exception is raised.
    A NULL is displayed.
    A zero (0) is displayed. (*)
    An exception is raised because the block does not contain a COMMIT statement.
 
 50.  Which SQL statement can NOT use an implicit cursor?  (1) Points
     
    A DELETE statement
    An UPDATE statement
    A SELECT statement that returns multiple rows (*)
    A SELECT statement that returns one row