1Z0-071 Data Manipulation Language (DML) 2

Question 1

What is the purpose of a SAVEPOINT in Oracle transactions?

Accepted Answer

To mark an intermediate point in a transaction so a partial rollback is possible

Answer

SAVEPOINT creates a named marker within a transaction, allowing ROLLBACK TO SAVEPOINT to undo only part of the transaction.

Question 2

Which INSERT syntax copies rows from one table to another without using VALUES?

Accepted Answer

INSERT INTO target_table SELECT * FROM source_table

Answer

INSERT ... SELECT inserts rows returned by a SELECT statement directly into the target table.

Question 3

What does an implicit COMMIT occur after in Oracle?

Accepted Answer

After DDL statements like CREATE and DROP

Answer

Oracle automatically issues an implicit COMMIT before and after every DDL statement.

Question 4

Which statement about DELETE vs. TRUNCATE is TRUE in Oracle?

Accepted Answer

DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot be rolled back

Answer

DELETE is a DML statement that can be rolled back; TRUNCATE is DDL that implicitly commits and cannot be rolled back.

Question 5

In a MERGE statement, what does the WHEN NOT MATCHED clause do?

Accepted Answer

Inserts source rows that have no matching row in the target

Answer

WHEN NOT MATCHED triggers when the source row has no corresponding row in the target, allowing an INSERT action.

1Z0-071 - Oracle Database SQL Certified Associate Practice Test