1Z0-071 Exam Questions & Answers

B . True. DDL (Data Definition Language) statements in Oracle Database are auto-commit statements. This means that if a DDL statement is issued, any uncommitted transactions in the same session will automatically be committed.

D . False. This is a common misconception. DML (Data Manipulation Language) statements do not automatically start new transactions. In Oracle, a transaction begins when the first DML statement (INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE) is encountered after a previous transaction has been committed or rolled back.

A is incorrect because DDL statements automatically commit the entire transaction, not just the data dictionary updates. C is incorrect because an uncommitted transaction is not automatically committed when a user exits SQL*Plus; instead, it is rolled back. E is incorrect because a session cannot see uncommitted updates made by other sessions.

For the provided DELETE statement with an EXISTS clause:

Option B: The subquery is executed before the DELETE statement is executed.

Subqueries with EXISTS are typically executed before the outer DELETE statement to determine which rows of the outer query satisfy the condition.

Option D: The DELETE statement executes successfully even if the subquery selects multiple rows.

The EXISTS condition is used to check for the existence of rows returned by the subquery, regardless of how many rows there are. It returns TRUE if the subquery returns at least one row.

Options A, C, and E are incorrect because:

Option A: This statement is incorrect; the subquery is indeed a correlated subquery because it references the employee_id from the outer query (employees).

Option C: This is incorrect because not all existing rows in the EMPLOYEES table will be deleted, only those for which an associated record exists in the emp_history table.

Option E: While technically the subquery may be evaluated multiple times, it is only executed for those rows in EMPLOYEES that satisfy the condition of the EXISTS clause.

A . This statement is false. In NVL2, the first expression is returned if it is not null.

B . This statement is false. NVL2 takes exactly three arguments, not any number of expressions.

C . This statement is false. COALESCE stops evaluating its list of expressions as soon as it finds the first non-null value.

D . This is true. COALESCE returns the first non-null expression in its list.

E . This statement is true. NVL requires the first and second expressions to be of the same or compatible data types because it returns the first expression if it is not null, otherwise it returns the second.

F . This statement is false. NVL takes exactly two arguments, not any number.

Oracle Documentation on COALESCE: https://docs.oracle.com/database/121/SQLRF/functions040.htm#SQLRF00625

Oracle Documentation on NVL: https://docs.oracle.com/database/121/SQLRF/functions130.htm#SQLRF00683

Oracle Documentation on NVL2: https://docs.oracle.com/database/121/SQLRF/functions123.htm#SQLRF00682

In Oracle SQL, adding hours to a date can be done using the INTERVAL keyword with appropriate clauses:

Option C: INTERVAL '12' HOUR

Adds exactly 12 hours to a date.

Option D: INTERVAL '720' MINUTE

Since 720 minutes equal 12 hours, this correctly adds 12 hours to a date.

Option F: INTERVAL '11:60' HOUR TO MINUTE

This interval represents 11 hours and 60 minutes, which effectively adds up to 12 hours.

Options A, B, and E contain incorrect syntax or do not match the required addition of 22 hours as specified in the question.