Oracle Updated 1z0-071 Exam Questions and Answers by yasmine

In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.

A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.

The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.

In the case of SQL commands TRUNCATE and DELETE:

• A. the result of a delete can be undone by issuing a rollback: DELETE is a DML operation that affects rows individually and can be rolled back if it is performed within a transaction.
• B. delete can use a where clause to determine which row(s) should be removed: DELETE operation allows the use of a WHERE clause to specify which rows should be deleted based on certain conditions.

Incorrect options are:

• C: TRUNCATE does not support the use of a WHERE clause. It is designed to remove all rows from a table swiftly and cannot be conditional.
• D: TRUNCATE does not leave indexes in an unusable state; it simply removes all rows.
• E: TRUNCATE is a DDL command and its operation typically cannot be rolled back in many SQL database systems, including Oracle.

C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.

D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).

F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.

G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.

B: True. The second ROLLBACK command would not do anything because the first ROLLBACK TO SAVEPOINT a; already undid the delete operation, and there was no other DML operation between the first and the second ROLLBACK.

E: True. The second ROLLBACK command undoes the insert because after the first ROLLBACK TO SAVEPOINT a; there are no savepoints defined, so a subsequent ROLLBACK would undo all transactions to the beginning of the current session or last COMMIT.

The ROLLBACK command is used to undo transactions that have not yet been committed. Rolling back to a savepoint only undoes transactions up to that savepoint, and a subsequent ROLLBACK without a savepoint name will undo all uncommitted changes since the last COMMIT.

References:Oracle SQL documentation on the ROLLBACK statement provides details on how it interacts with savepoints and the effects it has on the transactions within a session.