SQL Transaction Control Language (TCL) 2 — Questions and Answers
Question 1: Which TCL command permanently saves all changes made during the current transaction?
- COMMIT (Correct answer)
- SAVE
- PERSIST
- FLUSH
Correct answer: COMMIT
COMMIT makes all changes in the current transaction permanent and visible to other sessions.
Question 2: What does the ROLLBACK command do?
- Undoes changes since the last COMMIT or SAVEPOINT (Correct answer)
- Deletes a table
- Locks all rows
- Saves changes permanently
Correct answer: Undoes changes since the last COMMIT or SAVEPOINT
ROLLBACK reverses uncommitted changes back to the last commit or a specified savepoint.
Question 3: Which statement creates a point within a transaction you can later roll back to?
- SAVEPOINT (Correct answer)
- CHECKPOINT
- MARK
- BOOKMARK
Correct answer: SAVEPOINT
SAVEPOINT defines a named marker within a transaction for partial rollback.
Question 4: After ROLLBACK TO SAVEPOINT sp1, what happens to changes made before sp1?
- They remain part of the transaction (Correct answer)
- They are committed
- They are discarded
- They are locked
Correct answer: They remain part of the transaction
Only changes made after the savepoint are undone; earlier changes stay until commit or rollback.
Question 5: Which TCL command is used to release a savepoint without rolling back?
- RELEASE SAVEPOINT (Correct answer)
- DROP SAVEPOINT
- DELETE SAVEPOINT
- REMOVE SAVEPOINT
Correct answer: RELEASE SAVEPOINT
RELEASE SAVEPOINT removes a savepoint so it can no longer be used as a rollback target.
Question 6: What is the default transaction behavior in many databases like Oracle for DML statements?
- Changes are uncommitted until COMMIT is issued (Correct answer)
- Every statement auto-commits
- Changes are read-only
- Changes roll back automatically
Correct answer: Changes are uncommitted until COMMIT is issued
In Oracle, DML changes are held in the transaction until an explicit COMMIT (or implicit DDL commit).
Question 7: Which of these is NOT a TCL command?
- TRUNCATE (Correct answer)
- COMMIT
- ROLLBACK
- SAVEPOINT
Correct answer: TRUNCATE
TRUNCATE is a DDL command; COMMIT, ROLLBACK, and SAVEPOINT are TCL.
SQL Transaction Control Language (TCL) 2
Which TCL command permanently saves all changes made during the current transaction?