PLSQL Semester 1 Mid Term Exam - Part I

1.  PL/SQL is an Oracle proprietary, procedural, 4GL programming language. True or False?    
    True
    False (*)
     
  2.  PL/SQL extends SQL by including all of the following except:
    variables
    conditional statements
    reusable program units
    constants
    nonprocedural constructs (*)
     
  3.  Which of the following statements about PL/SQL and SQL is true?
    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. (*)
     
  4.  Using Oracle Application Express, you can create Web applications that include PL/SQL. True or False?    
    True (*)
    False
     
  5.  The fact that PL/SQL is portable is a good thing because:
    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
     
  6.  Which of the following can you use PL/SQL to do?
    Update data (DML)
    Develop Web applications using the Web Application Toolkit
    Manage database security
    Create customized reports
    All of the above (*)
     
  7.  Which of the following tools can NOT be used to develop and test PL/SQL code?
    Oracle Jdeveloper
    Oracle Application Express
    Oracle JSQL (*)
    Oracle iSQL*Plus
     
  8.  What kind of block is defined by the following PL/SQL code?
BEGIN
    DBMS_OUTPUT.PUT_LINE('My first quiz');
END;      
    procedure
    subroutine
    function
    anonymous (*)
     
  9.  Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.
   
    2,1,4,3
    3,4,2,1 (*)
    3,2,4,1
    4,3,2,1
   
  10.  Which lines of code will correctly display the message "The cat sat on the mat"? (Choose two.)
   (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'); (*)
   
  11.  What is the purpose of using DBMS_OUTPUT.PUT_LINE in a PL/SQL block?
    To perform conditional tests
    To allow a set of statements to be executed repeatedly
    To display results to check if our code is working correctly (*)
    To store new rows in the database
     
  12.  Errors are handled in the Exception part of the PL/SQL block. True or False?
    True (*)
    False
     
  13.  Type of a variable determines the range of values the variable can have and the set of operations that are defined for values of the type.    
    True (*)
    False
     
  14.  Which of these are PL/SQL data types? (Choose three.) (Choose all correct answers)
    Scalar (*)
    Identifier
    Delimiter
    Composite (*)
    LOB (*)
     
  15.  A movie is an example of which category of data type?    
    Scalar
    Composite
    Reference
    LOB (*)
     
  16.  Which of the following are valid identifiers? (Choose two.) (Choose all correct answers)
    yesterday (*)
    yesterday's date
    number_of_students_in_the_class
    v$testresult (*)
    #students
     
  17.  Which of the following are PL/SQL lexical units? (Choose two.) (Choose all correct answers)
    Identifiers (*)
    Table Columns
    Reserved Words (*)
    Anonymous Blocks
    SQL Workshop
     
  18.  Delimiters are _____ that have special meaning to the Oracle database.    
    identifiers
    variables
    symbols (*)

  19.  When nested blocks are used, which blocks can or must be labeled?    
    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. (*)
     
  20.  What will be displayed when the following block is executed?
<<outer>>
DECLARE
   v_myvar VARCHAR2(10) := 'Hello' ;
BEGIN
   <<inner>>    DECLARE
     v_myvar VARCHAR2(10) := 'World';
     BEGIN
     v_myvar := v_myvar || ' ' || outer.v_myvar;
   END;
   DBMS_OUTPUT.PUT_LINE(inner.v_myvar);
END;
 
    HelloWorld
    Hello World
    World
    The code will fail since the inner variable is not within the scope of the outer block. (*)
     
  21.  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;
   
    My
    My name (*)
    My name is
    My name is Zeynep

  22.  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;
   
    Susan
    Chang (*)
    ChangChang
    SusanChang
    The code will fail with an error
     
  23.  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;
   
    12 (*)
    24
    6
    Nothing will be displayed
     
  24.  To comment a single line of code, use two dashes after the comment. True or False?    
    True
    False (*)
   
  25.  Which of the following will help to make code easier to read?    
    Naming variables.
    Using %Type.
    Including comments in the code. (*)
     
  26.  Using standards for naming conventions is recommended. True or False?    
    True (*)
    False

  27.  If today's date is 14th June 2007, which statement will correctly convert today's date to the value: June 14, 2007 ?    
    TO_CHAR(sysdate)
    TO_DATE(sysdate)
    TO_DATE(sysdate,'Month DD, YYYY')
    TO_CHAR(sysdate, 'Month DD, YYYY') (*)
     
  28.  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;
   
    True (*)
    False
     
  29.  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;
   
    81
    49
    14 (*)
    18
     
  30.  Single row character functions are valid SQL functions in PL/SQL. True or False?    
    True (*)
     False
     
  31.  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;
   
    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

  32.  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?
   
    '300'
    300 (*)
    NULL

  33.  When you use a function to convert data types in a PL/SQL program, it is called ______ conversion.      
    Explicit (*)
    Implicit
    TO_CHAR
     
  34.  TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:    
    Implicit conversion functions
    Explicit conversion functions (*)
    Character functions
    Operators
     
  35.  Assignment statements can continue over several lines in PL/SQL. True or False?    
    True (*)
    False
     
  36.  When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?    
    True (*)
    False
     
  37.  Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;
   
    Correct.
    Not correct. (*)
     
  38.  Variables can be used in the following ways in a PL/SQL block. (Choose two.) (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.
     
  39.  A variable must have a value if NOT NULL is specified. True or False?    
    True (*)
    False
     
  40.  You need to declare a variable to hold a value which has been read from the SALARY column of the EMPLOYEES table. Which of the following is an advantage of declaring the variable as: employees.salary%TYPE ?    
    It is shorter than coding NUMBER(8,2)
    If the SALARY column is ALTERed later, the PL/SQL code need not be changed. (*)
    It executes much faster than using NUMBER(8,2)
    It allows the software to perform implicit data type conversions.
     
  41.  Which of the following declarations is invalid?    
    v_count PLS_INTEGER:=0;
    college_name VARCHAR2(20):='Harvard';
    v_pages CONSTANT NUMBER; (*)
    v_start_date DATE := sysdate+1;
   
  42.  Given this first section of code:
DECLARE
    v_result employees.salary%TYPE;
BEGIN
Which statement will always return exactly one value?
     
    SELECT salary
INTO v_result
FROM employees;
 
    SELECT salary
INTO v_result
FROM employees
WHERE last_name ='Smith';
 
    SELECT salary
INTO v_result
FROM employees
WHERE department_id = 80;
 
    SELECT SUM(salary)
INTO v_result
FROM employees; (*)

     
  43.  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;
   
    True
    False (*)
     
  44.  Which one of these SQL statements can be directly included in a PL/SQL executable block?
     
    SELECT last_name FROM employees
WHERE employee_id=100;
 
    DESCRIBE employees;
 
    UPDATE employees
SET last_name='Smith'; (*)

     DROP TABLE employees;
   
  45.  Which of the following is NOT a good guideline for retrieving data in PL/SQL?    
    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.
     
  46.  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?
     
    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. (*)
     
  47.  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?
     
    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.
     
  48.  Which SQL statement can NOT use an implicit cursor?    
    A DELETE statement
    An UPDATE statement
    A SELECT statement that returns multiple rows (*)
    A SELECT statement that returns one row
     
  49.  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?
   
    True
    False (*)
    Null
    Error. That attribute does not apply for implicit cursors.
     
  50.  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;
   
    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.