OCP SQL & PL/SQL Programming 2

Question 1

Which PL/SQL collection type can be indexed by a VARCHAR2 key?

Accepted Answer

Associative array

Answer

Nested table

Answer

VARRAY

Answer

INDEX BY BINARY_INTEGER only

Question 2

What does the BULK COLLECT clause do in PL/SQL?

Accepted Answer

Fetches multiple rows into a collection in a single context switch

Answer

Inserts multiple rows in one DML statement

Answer

Creates a bulk index on a collection

Answer

Aggregates data from multiple tables

Question 3

Which SQL function returns the number of months between two dates?

Accepted Answer

MONTHS_BETWEEN

Answer

DATE_DIFF

Answer

DATEDIFF

Answer

INTERVAL_MONTHS

Question 4

In a PL/SQL exception handler, what does SQLCODE return when no exception has occurred?

Accepted Answer

0

Answer

-1

Answer

1

Answer

NULL

Question 5

Which clause in a SELECT statement is evaluated AFTER the GROUP BY clause?

Accepted Answer

HAVING

Answer

WHERE

Answer

FROM

Answer

ORDER BY

Question 6

What is the effect of the NOCOPY hint on a PL/SQL OUT parameter?

Accepted Answer

Passes the parameter by reference instead of by value

Answer

Prevents the parameter from being modified

Answer

Creates a read-only copy of the parameter

Answer

Disables constraint checking on the parameter

Question 7

Which Oracle SQL clause would you use to pivot rows into columns without using the PIVOT operator?

Accepted Answer

CASE with GROUP BY and aggregate functions

Answer

CONNECT BY

Answer

ROLLUP

Answer

GROUPING SETS