[Solved] Consider this transaction:BEGIN TRANSACTION; INSERT INTO orders VALUES (1, 'Book'); SAVEPOINT sp1; INSERT INTO orders VALUES (2, 'Pen');... COMMIT;What rows will be in the orders table after commit? | SQL

SQL - Transactions and Data Integrity

Consider this transaction:

BEGIN TRANSACTION;
INSERT INTO orders VALUES (1, 'Book');
SAVEPOINT sp1;
INSERT INTO orders VALUES (2, 'Pen');
ROLLBACK TO sp1;
COMMIT;

What rows will be in the orders table after commit?

AOnly the row with (2, 'Pen')

BOnly the row with (1, 'Book')

CNo rows, transaction rolled back completely

DBoth rows (1, 'Book') and (2, 'Pen')

Step-by-Step Solution

Solution:

Step 1: Analyze the transaction steps
The first insert adds (1, 'Book'), then a savepoint sp1 is created. The second insert adds (2, 'Pen').
Step 2: Understand rollback to savepoint effect
ROLLBACK TO sp1 undoes changes after sp1, so the second insert is undone.
Step 3: Commit finalizes remaining changes
Only the first insert remains and is committed.
Final Answer:
Only the row with (1, 'Book') -> Option B
Quick Check:
Rollback to savepoint undoes later inserts [OK]

Quick Trick: Rollback to savepoint undoes changes after it, commit saves earlier ones [OK]

Common Mistakes:

Master "Transactions and Data Integrity" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Consider this transaction: