COMMIT and ROLLBACK behavior in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to save changes permanently in the database.

SQL

INSERT INTO employees (name, position) VALUES ('Alice', 'Manager'); [1];

Drag options to blanks, or click blank then click option'

AROLLBACK

BBEGIN

CSAVEPOINT

DCOMMIT

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLBACK instead of COMMIT will undo changes.

Forgetting to use COMMIT leaves changes unconfirmed.

The COMMIT command saves all changes made in the current transaction permanently to the database.

2fill in blank

medium

Complete the code to undo changes made in the current transaction.

SQL

UPDATE accounts SET balance = balance - 100 WHERE id = 5; [1];

Drag options to blanks, or click blank then click option'

ACOMMIT

BROLLBACK

CBEGIN TRANSACTION

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT will save changes instead of undoing them.

Not using ROLLBACK leaves unwanted changes in place.

The ROLLBACK command undoes all changes made in the current transaction, reverting the database to its previous state.

3fill in blank

hard

Fix the error in the transaction control by choosing the correct command.

SQL

BEGIN TRANSACTION; DELETE FROM orders WHERE order_id = 10; [1];

Drag options to blanks, or click blank then click option'

AROLLBACK

BSTART

CCOMMIT

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'START' instead of COMMIT causes syntax errors.

Forgetting to commit leaves changes unconfirmed.

After making changes in a transaction, use COMMIT to save them permanently. 'START' is not a valid command here.

4fill in blank

hard

Fill both blanks to create a savepoint and then rollback to it.

SQL

BEGIN TRANSACTION; INSERT INTO products VALUES (101, 'Pen'); [1] save1; UPDATE products SET price = 1.5 WHERE id = 101; [2] save1;

Drag options to blanks, or click blank then click option'

ASAVEPOINT

BCOMMIT

CROLLBACK

DBEGIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT instead of ROLLBACK to revert changes.

Not creating a savepoint before rollback.

SAVEPOINT creates a point to which you can rollback. ROLLBACK save1 undoes changes after that savepoint.

5fill in blank

hard

Fill all three blanks to start a transaction, create a savepoint, and commit changes.

SQL

[1]; INSERT INTO customers VALUES (201, 'John'); [2] savepoint1; UPDATE customers SET city = 'NY' WHERE id = 201; [3];

Drag options to blanks, or click blank then click option'

ABEGIN TRANSACTION

BSAVEPOINT

CCOMMIT

DROLLBACK

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLBACK instead of COMMIT at the end.

Forgetting to begin the transaction.

Start with BEGIN TRANSACTION, create a SAVEPOINT, and finally COMMIT to save all changes.

Practice

1. What does the COMMIT command do in a database transaction?

easy

A. It locks the database to prevent changes.

B. It undoes all changes made during the transaction.

C. It starts a new transaction.

D. It saves all changes made during the transaction permanently.

Solution

1. Step 1: Understand the purpose of COMMIT

   The COMMIT command finalizes all changes made in the current transaction and saves them permanently in the database.

2. Step 2: Compare with other options

   ROLLBACK undoes changes, starting a transaction is done by BEGIN or START TRANSACTION, and locking is a different operation.

3. Final Answer:

   It saves all changes made during the transaction permanently. -> Option D

4. Quick Check:

   COMMIT = Save changes permanently [OK]

Hint: COMMIT means save changes permanently [OK]

Common Mistakes:

• Confusing COMMIT with ROLLBACK
• Thinking COMMIT starts a transaction
• Assuming COMMIT locks the database

2. Which of the following is the correct syntax to undo changes made in the current transaction?

easy

A. COMMIT;

B. ROLLBACK;

C. UNDO;

D. SAVE;

Solution

1. Step 1: Identify the command to undo changes

   The ROLLBACK command is used to undo all changes made since the last COMMIT.

2. Step 2: Check syntax correctness

   ROLLBACK; is the correct syntax. COMMIT; saves changes, UNDO; and SAVE; are not valid SQL commands for this purpose.

3. Final Answer:

   ROLLBACK; -> Option B

4. Quick Check:

   ROLLBACK = Undo changes [OK]

Hint: ROLLBACK undoes changes, COMMIT saves them [OK]

Common Mistakes:

• Using COMMIT to undo changes
• Assuming UNDO is a valid SQL command
• Confusing SAVE with COMMIT

3. Consider this sequence of SQL commands:

BEGIN TRANSACTION;
INSERT INTO users (id, name) VALUES (1, 'Alice');
ROLLBACK;
SELECT * FROM users WHERE id = 1;

What will the SELECT query return?

medium

A. No rows

B. A row with id=1 and name='Alice'

C. Syntax error

D. All rows in the users table

Solution

1. Step 1: Analyze the transaction commands

   The INSERT adds a row with id=1 and name='Alice', but then ROLLBACK undoes this change.

2. Step 2: Understand the effect of ROLLBACK on SELECT

   Since the insert was rolled back, the row does not exist. The SELECT query will find no rows with id=1.

3. Final Answer:

   No rows -> Option A

4. Quick Check:

   ROLLBACK removes inserted rows [OK]

Hint: ROLLBACK removes changes before SELECT runs [OK]

Common Mistakes:

• Assuming inserted row remains after ROLLBACK
• Thinking ROLLBACK affects only future queries
• Confusing ROLLBACK with COMMIT

4. You run this SQL code:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 5;
COMMIT;
ROLLBACK;

What is the effect of the final ROLLBACK?

medium

A. It undoes the UPDATE and restores the original balance.

B. It causes a syntax error because ROLLBACK cannot follow COMMIT.

C. It has no effect because changes were already committed.

D. It commits the changes again.

Solution

1. Step 1: Understand COMMIT effect

   COMMIT saves all changes permanently, so the UPDATE is finalized.

2. Step 2: Analyze ROLLBACK after COMMIT

   ROLLBACK after COMMIT does nothing because there is no active transaction to undo.

3. Final Answer:

   It has no effect because changes were already committed. -> Option C

4. Quick Check:

   ROLLBACK after COMMIT = no effect [OK]

Hint: ROLLBACK only undoes uncommitted changes [OK]

Common Mistakes:

• Thinking ROLLBACK undoes committed changes
• Assuming ROLLBACK causes error after COMMIT
• Confusing order of commands

5. You want to insert multiple rows into a table but only save them if all inserts succeed. Which sequence correctly uses COMMIT and ROLLBACK to achieve this?

1. BEGIN TRANSACTION;
2. INSERT INTO orders VALUES (101, 'Book');
3. INSERT INTO orders VALUES (102, 'Pen');
4. -- If both inserts succeed, then ___;
5. -- If any insert fails, then ___;

hard

A. 4: COMMIT; 5: ROLLBACK;

B. 4: ROLLBACK; 5: COMMIT;

C. 4: COMMIT; 5: COMMIT;

D. 4: ROLLBACK; 5: ROLLBACK;

Solution

1. Step 1: Understand transaction control

   To save changes only if all inserts succeed, you COMMIT after success and ROLLBACK if any fail.

2. Step 2: Match commands to conditions

   Line 4 should be COMMIT to save changes; line 5 should be ROLLBACK to undo partial changes.

3. Final Answer:

   4: COMMIT; 5: ROLLBACK; -> Option A

4. Quick Check:

   COMMIT on success, ROLLBACK on failure [OK]

Hint: COMMIT saves all; ROLLBACK undoes all [OK]

Common Mistakes:

• Swapping COMMIT and ROLLBACK roles
• Committing even if inserts fail
• Rolling back after successful inserts