1Z0-071 Data Manipulation Language (DML) 2 — Questions and Answers
Question 1: What is the purpose of a SAVEPOINT in Oracle transactions?
- To commit changes up to a specific point
- To mark an intermediate point in a transaction so a partial rollback is possible (Correct answer)
- To lock a table at a specific moment
- To create a backup of the current data
Correct answer: To mark an intermediate point in a transaction so a partial rollback is possible
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?
- INSERT INTO target_table SELECT * FROM source_table (Correct answer)
- INSERT INTO target_table FROM source_table
- INSERT SELECT * FROM source_table INTO target_table
- COPY target_table FROM source_table
Correct answer: INSERT INTO target_table SELECT * FROM source_table
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?
- Every DML statement
- After a ROLLBACK
- After DDL statements like CREATE and DROP (Correct answer)
- After every SELECT statement
Correct answer: After DDL statements like CREATE and DROP
Oracle automatically issues an implicit COMMIT before and after every DDL statement.
Question 4: Which statement about DELETE vs. TRUNCATE is TRUE in Oracle?
- Both can be rolled back
- DELETE is DDL; TRUNCATE is DML
- TRUNCATE can be rolled back; DELETE cannot
- DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot be rolled back (Correct answer)
Correct answer: DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot be rolled back
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?
- Updates existing rows in the target
- Deletes rows that don't match
- Inserts source rows that have no matching row in the target (Correct answer)
- Rolls back the merge operation
Correct answer: Inserts source rows that have no matching row in the target
WHEN NOT MATCHED triggers when the source row has no corresponding row in the target, allowing an INSERT action.
Question 6: Which clause is mandatory in an UPDATE statement to avoid updating every row?
- SET
- HAVING
- WHERE (Correct answer)
- RETURNING
Correct answer: WHERE
Without a WHERE clause, UPDATE modifies every row in the table; WHERE restricts which rows are changed.
1Z0-071 Data Manipulation Language (DML) 2
What is the purpose of a SAVEPOINT in Oracle transactions?