1z0-071 Exam Dumps : Oracle Database 12c SQL

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.

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.

The query uses two functions: ROUND and TRUNC. The ROUND function will round the number 156.00 to the nearest hundred because of the -2 which specifies the number of decimal places to round to. This will result in 200. Then the TRUNC function truncates this number to the nearest 10, due to the -1 argument, which will give us 200 as the result since truncation does not change the rounded value in this case.

A. 16 (Incorrect)

B. 160 (Incorrect)

C. 150 (Incorrect)

D. 200 (Incorrect)

E. 100 (Incorrect)