Section 3 Lesson 2: Retrieving Data in PL/SQL

Retrieving Data in PL/SQL

 Section 1
   
  1.  Look at this PL/SQL block:
DECLARE
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count
   FROM employees WHERE salary > 50000;
END;

No employees earn more than $50,000. Which of the following statements are true? (1) Points
    (Choose all correct answers)
   
    The SELECT will return value 0 into V_COUNT. (*)
    The SELECT will fail because it does NOT return exactly one row.
    The block will fail because variable V_SALARY was not declared.
    The SELECT returns exactly one row. (*)
    The block will fail because no results are displayed to the user.

  2.  Which one of these SQL statements can be directly included in a PL/SQL executable block?  (1) Points
   
    IF... THEN...;
    INSERT INTO...; (*)
    SELECT * FROM DUAL;
    SHOW USER;

  3.  It is good programming practice to create identifiers having the same name as column names. True or False? (1) Points
   
    True
    False (*)
   
  4.  Which of the following is NOT a valid guideline for retrieving data in PL/SQL?  (1) Points
   
    Terminate the SQL statement with a semicolon (;)
    Do NOT use a WHERE clause in SELECT statements. (*)
    Where possible, declare variables using the %TYPE attribute.
    Specify the same number of variables in the INTO clause as database columns in the SELECT clause.
   
  5.  Which SQL statements can be used directly in a PL/SQL block? (Choose two.)  (1) Points
  (Choose all correct answers)
   
    GRANT EXECUTE ON ...
    SELECT * INTO ... (*)
    REVOKE SELECT ON ...
    UPDATE employees SET... (*)
    ALTER TABLE employees ...
   
  6.  Does PL/SQL allow you to have a variable with the same name as a database column? (1) Points
   
    No
    Yes (*)
   
  7.  What will happen when the following block is executed?
DECLARE
    v_last employees.last_name%TYPE;
    v_first employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    SELECT first_name, last_name
    INTO v_first, v_last, v_salary
    FROM employees WHERE employee_id=100;
END;
(1) Points
   
    The block will fail because the SELECT statement returns more than one row.
    The block will fail because the SELECT is trying to read two columns into three PL/SQL variables. (*)
    The block will fail because V_LAST was declared before V_FIRST.
    The block will execute successfully, and the V_SALARY variable will be set to NULL.
   
  8.  When used in a PL/SQL block, which SQL statement must return exactly one row? (1) Points
   
    INSERT
    UPDATE
    SELECT (*)
    MERGE
    DELETE