Labels

SECTION 5 QUIZ ANSWERS

 

HERE ARE THE 20 QUIZ QUESTIONS 

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

1.The DEPARTMENTS cursor must be declared with a parameter.

2.You cannot use a cursor with a subquery in nested loops.

3.Nothing is wrong. The block will execute successfully and display all departments and the employees in those departments. (*)

4.EMP_CURS should not be DECLAREd explicitly; it should be coded as a subquery in a cursor FOR loop.

5.You cannot use two different kinds of loops in a single PL/SQL block.


2. Which of the following is a good reason to use two cursors in a single PL/SQL block?

1.To allow rows to be locked as they are FETCHed.

2.When two tables are related to each other (often by a foreign key) and we want to produce a multilevel report using data from both tables. (*)

3.To speed up the execution of the PL/SQL block.

4.To allow one cursor to be opened twice at the same time.

5.It is the only way to declare a cursor with a parameter.


3. The following cursor has been declared:

CURSOR emp_curs

  (p_dept_id employees.department_id%TYPE,

  p_job_id employees.job_id%TYPE) IS

  SELECT * FROM employees

  WHERE department_id = p_dept_id

  AND job_id = p_job_id;


Which of the following will correctly open the cursor?


1.FOR emp_rec IN emp_curs(p_dept_id p_job_id) LOOP ...

2.OPEN emp_curs(20);

3.OPEN emp_curs('IT_PROG', 20);

4.FOR emp_rec IN emp_curs(20,'IT_PROG') LOOP ... (*)

5.FOR emp_rec IN emp_curs(20) LOOP ...


4. You want to use explicit cursors to fetch and display all the countries in a specific region. There are 19 rows in the WF_WORLD_REGIONS table. You want to use a different region each time the cursor is opened. How many cursors should you declare.

  1. 19 cursors in 19 PL/SQL blocks (one in each block).
  2. 20 cursors, in case an extra row is inserted into WF_WORLD_REGIONS later.
  3. 19 cursors, all in the same PL/SQL block.
  4. None of the these.
  5. One cursor with a parameter in the WHERE clause. (*)

5. Which of the following is a benefit of using a cursor FOR loop?

  1. The OPEN, CLOSE, FETCH and EXIT from the loop are done automatically. (*)
  2. You can OPEN the same cursor twice at the same time.
  3. The exception handling is done automatically. .
  4. Because there is less code, the loop executes faster.
  5. %ROWCOUNT increments automatically each time a row is FETCHed.

1. Which one of the following is a valid cursor FOR loop with a subquery?


  1. FOR emp_rec IN (SELECT last_name |and first_name FROM employees) LOOP ...
  2. FOR emp_rec IN (SELECT last_name, salary*12 "ANNSAL" FROM employees) LOOP ... (*)
  3. FOR emp_rec IN SELECT last_name, salary*12 "ANNSAL" FROM employees LOOP ...
  4. FOR emp_rec IN (SELECT UPPERCASE(last_name) FROM employees) LOOP ...

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

  1. emp_rec.salary IN emp_curs
  2. emp_curs.salary
  3. emp_rec.salary (*)
  4. salary
  5. employees.salary


3. Look at the following code:

DECLARE

  CURSOR emp_curs (p_dept_id employees.department_id%TYPE) IS

   SELECT * FROM employees

   WHERE department_id = p_dept_id;

  v_emp_rec emp_curs%ROWTYPE;

  v_deptid NUMBER(4) := 50;

BEGIN

  OPEN emp_curs( -- Point A --);

  ....

You want to open the cursor, passing value 50 to the parameter. Which of the following are correct at Point A?



  1. 100 / 2
  2. 50
  3. All of these. (*)
  4. v_deptid

4. What is one of the advantages of using parameters with a cursor?



  1. It will execute much faster than a cursor without parameters.
  2. You do not need to DECLARE the cursor at all.
  3. You can use a cursor FOR loop.
  4. You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)
  5. You can declare the cursor FOR UPDATE.

5. One (and only one) employee has LAST_NAME = 'Grant'. You need to code:

SELECT ... FROM employees WHERE last_name = 'Grant';

  1. Which type of cursor should you use, and why
  2. An explicit cursor, because there could be more than one 'Grant' in the future. (*)
  3. An implicit cursor, because SELECT is a SQL statement and implicit cursors are always called "SQL".
  4. An implicit cursor, because there is only one 'Grant'.
  5. An explicit cursor, because you can use an implicit cursor only for DML statements.


6. What will happen when the following code is executed?

DECLARE CURSOR emp_curs IS

    SELECT salary FROM employees;

    v_salary employees.salary%TYPE;

BEGIN

    OPEN emp_curs;

    FETCH emp_curs INTO v_salary;

    CLOSE emp_curs;

    FETCH emp_curs INTO v_salary;

END;


  1. The first employee row will be fetched twice.
  2. The block will fail and a TOO_MANY_ROWS exception will be raised.
  3. The block will fail and an INVALID_CURSOR exception will be raised. (*)
  4. The first two employee rows will be fetched.


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


  1. C,D,A,B
  2. A,C,D,B
  3. C,A,B,D
  4. C,A,D,B (*)

8. Which one of the following explicit cursor declarations is NOT valID?

  1. CURSOR country_curs IS SELECT country_name INTO v_country_name FROM wf_countries; (*)
  2. CURSOR country_curs IS SELECT country_name, region_name FROM wf_countries c, wf_world_regions r WHERE c.region_id = r.region_id;
  3. CURSOR country_curs IS SELECT country_name FROM wf_countries ORDER BY population DESC;
  4. CURSOR country_curs IS SELECT country_name FROM wf_countries WHERE region_id IN (SELECT region_id FROM wf_world_regions WHERE LOWER(region_name) LIKE '%asia%');

9. You can reference explicit cursor attributes directly in a SQL statement. True or False?

True

False(*)

10. 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 COUSOR?

  1. emp_rec emp_rec%ROWTYPE;
  2. emp_rec cursor%ROWTYPE;
  3. emp_rec emp_curs%ROWTYPE; (*)
  4. emp_rec emp_curs%TYPE;

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

1.You cannot use two different kinds of loops in a single PL/SQL block.

2.The DEPARTMENTS cursor must be declared with a parameter.

3. cannot use a cursor with a subquery in nested loops.

4.Nothing is wrong. The block will execute successfully and display all departments and the employees in those departments. (*)

5.EMP_CURS should not be DECLAREd explicitly; it should be coded as a subquery in a cursor FOR loop.


12. Which of the following is a good reason to declare and use multiple cursors in a single PL/SQL block?

  1. Multiple cursors are the only way to use cursors with parameters.
  2. Multiple cursors improve performance. They are faster than using a single cursor.
  3. Multiple cursors use less memory than a single cursor.
  4. Multiple cursors allow us to fetch rows from two or more related tables without using a JOIN. (*)
  5. Multiple cursors can be opened many times, while a single cursor can be opened only once.


13. 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?

  1. UPDATE OF employees; (*)
  2. UPDATE OF salary;
  3. UPDATE NOWAIT;
  4. UPDATE;

14. You have declared a cursor as SELECT .... FOR UPDATE; You have OPENed the cursor and locked the FETCHed rows. When are these row locks released

  1. When you explicitly COMMIT or ROLLBACK your transaction (*)
  2. When your block finishes executing
  3. When you CLOSE the cursor
  4. When an UPDATE ... WHERE CURRENT OF cursor_name; is executed
  5. When another user tries to SELECT the rows


15. What is the difference between the following two blocks of code?

--Block A

DECLARE

    CURSOR emp_cursor IS

    SELECT employee_id, last_name

    FROM employees

    WHERE department_id = 80

    FOR UPDATE OF salary;


--Block B

DECLARE

    CURSOR emp_cursor IS

    SELECT employee_id, last_name

    FROM employees

    WHERE department_id = 80

    FOR UPDATE OF salary

    NOWAIT;


  1. There is no difference; the programs behave exactly the same way.
  2. In Block A, the program waits indefinitely until locked rows are available. In Block B, control is returned to your program after 5 seconds so that it can do other work.
  3. In Block A, the program waits indefinitely until locked rows are available. In Block B, the program returns control immediately so that it can do other .
  4. In Block A, the program waits indefinitely until locked rows are available. In Block B, the program returns control immediately so that it can do other work. (*)

Labels:

Comments

Post a Comment

Popular Posts