PLSQL Semester 1 Mid Term Exam - Part I

Section 1
     
  1.  SQL is a common access language for many types of databases, including Oracle. True or False?  (1) Points
     
    True (*)
    False
 
  2.  The P in PL/SQL stands for:  (1) Points
     
    Processing
    Procedural (*)
    Primary
    Proprietary
     
 3.  Which of the following statements about PL/SQL and SQL is true?  (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. (*)
     
 4.  Every PL/SQL anonymous block must start with the keyword DECLARE. True or False?  (1) Points
     
    True
    False (*)

5.  Which of the following tools can NOT be used to develop and test PL/SQL code?  (1) Points
     
    Oracle Jdeveloper
    Oracle Application Express
    Oracle JSQL (*)
    Oracle iSQL*Plus

6.  Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.
 (1) Points
     
    2,1,4,3
    3,4,2,1 (*)
    3,2,4,1
    4,3,2,1

7.  Errors are handled in the Exception part of the PL/SQL block. True or False?  (1) Points
     
    True (*)
    False

8.  In which part of the PL/SQL block are declarations of variables defined?  (1) Points
     
    Executable
    Exception
    Declarative (*)
    Definition

9.  What is the purpose of using DBMS_OUTPUT.PUT_LINE in a PL/SQL block?  (1) Points
     
    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
 
10.  Using Oracle Application Express, you can create Web applications that include PL/SQL. True or False?  (1) Points
     
    True (*)
    False
       
11.  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

12.  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 (*)
 
Section 2
     
13.  A collection is a composite data type. True or False?  (1) Points
     
    True (*)
    False
 
14.  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.  (1) Points
     
    True (*)
    False

15.  Which of these are PL/SQL data types? (Choose three.)  (1) Points (Choose all correct answers)
     
    Scalar (*)
    Identifier
    Delimiter
    Composite (*)
    LOB (*)
 
16.  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
    Incorrect. Refer to Section 2 Lesson 5.
 
17.  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;
(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
 
18.  TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:  (1) Points
     
    Implicit conversion functions
    Explicit conversion functions (*)
    Character functions
    Operators
 
19.  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
 
20.  PL/SQL can convert a VARCHAR2 value containing alphabetic characters to a NUMBER value. True or False?  (1) Points
     
    True
    False (*)
 
21.  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
 
22.  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;
 
23.  If today's date is 14th June 2007, which statement will correctly convert today's date to the value: June 14, 2007 ?  (1) Points
     
    TO_CHAR(sysdate)
    TO_DATE(sysdate)
    TO_DATE(sysdate,'Month DD, YYYY')
    TO_CHAR(sysdate, 'Month DD, YYYY') (*)
     
24.  Which of the following is an example of using a case convention for good programming practice?  (1) Points
     
    Assign variables by using functions.
    Declare variables in the DECLARE section.
    Declare data types in uppercase. (*)
    Include an exception handler in every PL/SQL block.
    Incorrect. Refer to Section 2 Lesson 7.
 
 25.  To comment a single line of code, use two dashes after the comment. True or False?  (1) Points
     
    True
    False (*)
 
 26.  Using standards for naming conventions is recommended. True or False?  (1) Points
     
    True (*)
    False
 
 27.  Which statements about lexical units are true? (Choose two.)  (1) Points (Choose all correct answers)
     
    They are named objects stored in the database
    They are the building blocks of every PL/SQL program (*)
    They are optional but can make a PL/SQL block execute faster
    They are sequences of characters including letters, digits, tabs, returns and symbols (*)
 
 28.  Which of the following are valid identifiers? (Choose two.)  (1) Points (Choose all correct answers)
     
    Full Name
    students_street_address (*)
    v_code (*)
     #hours
    completion_%
 
 29.  Which of the following are PL/SQL lexical units? (Choose two.)  (1) Points (Choose all correct answers)
     
    Identifiers (*)
    Table Columns
    Reserved Words (*)
    Anonymous Blocks
    SQL Workshop
 
30.  Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;
 (1) Points
     
    Correct.
    Not correct. (*)
 
 31.  Assignment statements can continue over several lines in PL/SQL. True or False?  (1) Points
     
    True (*)
    False
 
 32.  When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?  (1) Points
     
    True (*)
    False
 
 33.  Variables can be assigned a value in both the Executable and Declaration sections of a PL/SQL program. True or False?  (1) Points
     
    True (*)
    False
 
34.  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
 
 35.  An exception occurs within the inner block of two nested blocks. The inner block does not have an EXCEPTION section. What always happens?  (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
 
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;
(1) Points
     
    8
    12
    Nothing, the block will fail with an error (*)
    20
    VarB
 
37.  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
 
38.  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;
(1) Points
     
    HelloWorld
    Hello World
    World
   The code will fail since the inner variable is not within the scope of the outer block. (*)
 
39.  Which of the following can be assigned to a Boolean variable?
1. Null
2. False
3. True
4. 0
(1) Points
     
    2 and 3
    2, 3 and 4
    1, 2 and 3 (*)
    1, 2, 3 and 4
 
40.  If you are using the %TYPE attribute, you can avoid hard coding the:  (1) Points
     
    Data type (*)
    Table name
    Column name
    Constraint
 
41.  A variable must have a value if NOT NULL is specified. True or False?  (1) Points
     
    True (*)
    False

Section 3
     
42.  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?
 (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.
 
 43.  How many DML statements can be included in a single transaction?  (1) Points
     
    Only one
    None. A transaction cannot include DML statements.
     A maximum of four DML statements
     As many as needed (*)
     
 44.  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?
 (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;

45.  Which one of these SQL statements can be directly included in a PL/SQL executable block?  (1) Points
     
    DELETE FROM employees
WHERE department_id=60; (*)

     SELECT salary FROM employees
WHERE department_id=60;
 
    CREATE TABLE new_emps (last_name VARCHAR2(10), first_name VARCHAR2(10));
 
    DROP TABLE locations;

46.  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;

47.  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;
(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.
 
48.  Which is the correct way to erase one row from a table?  (1) Points
     
    REMOVE employee_id=100
FROM employees;

    DROP TABLE employees
WHERE employee_id=100;

    TRUNCATE employees
WHERE employee_id=100;

    DELETE FROM employees
WHERE employee_id=100; (*)

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?
 (1) Points
     
    True
    False (*)
    Null
    Error. That attribute does not apply for implicit cursors.
 
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