Oracle SQL Test 2

Question 1

When using explicit cursors, list the correct command sequence to process a batch of records.

Accepted Answer

OPEN, FETCH, CLOSE

Answer

When working with explicit cursors in PL/SQL to process a batch of records, the correct command sequence after declaring the cursor is `OPEN`, `FETCH`, and then `CLOSE`. The `OPEN` statement executes the query and identifies the active set of rows, `FETCH` retrieves rows one by one from this active set, and `CLOSE` releases the resources associated with the cursor.

Question 2

Choose variables that aren't valid.

Accepted Answer

VARCHAR1

Answer

In Oracle SQL, `NUMBER`, `CHAR`, and `INTEGER` are all valid data types used for storing numeric, fixed-length character, and integer values, respectively. However, `VARCHAR1` is not a valid data type. The correct variable-length character data type in Oracle SQL is `VARCHAR2`, which is used to store character strings of varying lengths.

Question 3

Which of the following functions does not work as a grouping function?

Accepted Answer

DISTINCT

Answer

Grouping functions, also known as aggregate functions, perform a calculation on a set of rows and return a single summary value. Examples include `MIN` (minimum value), `SUM` (total sum), and `COUNT` (number of rows). `DISTINCT`, however, is a keyword used to eliminate duplicate values from a result set or within an aggregate function, but it is not an aggregate function itself.

Question 4

In the PL/SQL language, where do you declare an explicit cursor?

Accepted Answer

In the PL/SQL declaration section.

Answer

In PL/SQL, an explicit cursor must be declared in the `DECLARE` section of a PL/SQL block. This section is where all variables, constants, and cursors are defined before they can be used within the executable `BEGIN...END` block. Declaring it here makes the cursor known to the program for subsequent `OPEN`, `FETCH`, and `CLOSE` operations.

Question 5

Select the PL/SQL looping construct that isn't valid.

Accepted Answer

LOOP ... UNTIL ;END LOOP;

Answer

PL/SQL supports several looping constructs, including the basic `LOOP...EXIT WHEN...END LOOP;`, `WHILE LOOP...END LOOP;`, and `FOR` loops (both numeric and cursor-based). The `LOOP...UNTIL...END LOOP;` construct is not a valid looping syntax in PL/SQL. While `UNTIL` is used in some programming languages, PL/SQL uses `EXIT WHEN` for conditional termination within a basic loop.

Oracle SQL Practice Test

Oracle SQL Practice Test

Oracle SQL Test 2