BEGIN TRANSACTION syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after running this transaction block?

Consider the following SQL commands executed in order:

BEGIN TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 100);
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
ROLLBACK;

What is the balance of the account with id = 1 after these commands?

SQL

BEGIN TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 100);
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
ROLLBACK;

A No row with id = 1 exists

B Balance is 150

C Balance is 100

D Balance is 50

💡 Hint

ROLLBACK undoes all changes made in the transaction.

The ROLLBACK command cancels all changes made since BEGIN TRANSACTION, so the insert and update are undone. Therefore, no row with id = 1 exists after rollback.

📝 Syntax

intermediate

Which option correctly starts a transaction in standard SQL?

Choose the correct syntax to begin a transaction in standard SQL.

A START TRANSACTION;

B OPEN TRANSACTION;

C BEGIN;

D BEGIN TRANSACTION;

💡 Hint

Standard SQL uses a specific phrase to start transactions.

The standard SQL command to start a transaction is START TRANSACTION; other options are either vendor-specific or invalid.

🧠 Conceptual

advanced

What happens if you execute multiple BEGIN TRANSACTION statements without COMMIT or ROLLBACK?

In a database session, what is the effect of running multiple BEGIN TRANSACTION commands consecutively without committing or rolling back the previous transaction?

A The second BEGIN TRANSACTION is ignored until commit or rollback

B Each BEGIN TRANSACTION starts a new nested transaction

C The previous transaction is automatically committed before starting a new one

D The second BEGIN TRANSACTION causes an error

💡 Hint

Most databases do not support nested transactions by default.

Standard SQL and many databases do not allow starting a new transaction before ending the previous one, so the second BEGIN TRANSACTION causes an error.

❓ optimization

advanced

How to optimize transaction usage for multiple related updates?

You need to update multiple related tables in a database. Which approach optimizes performance and data integrity?

A Run each update in its own transaction

B Run updates without any transaction

C Run all updates inside a single transaction

D Run updates with autocommit enabled

💡 Hint

Grouping related changes in one transaction helps consistency.

Using a single transaction for related updates ensures all changes succeed or fail together, improving integrity and often performance.

🔧 Debug

expert

Why does this transaction block cause an error?

Examine the following SQL code:

BEGIN TRANSACTION;
INSERT INTO orders (id, amount) VALUES (1, 100);
BEGIN TRANSACTION;
UPDATE orders SET amount = 150 WHERE id = 1;
COMMIT;

Why does this code cause an error?

SQL

BEGIN TRANSACTION;
INSERT INTO orders (id, amount) VALUES (1, 100);
BEGIN TRANSACTION;
UPDATE orders SET amount = 150 WHERE id = 1;
COMMIT;

A COMMIT is missing after the first BEGIN TRANSACTION

B Nested BEGIN TRANSACTION is not allowed without commit or rollback

C INSERT statement syntax is incorrect

D UPDATE statement causes a type error

💡 Hint

Check if nested transactions are supported.

Most databases do not support nested transactions by default, so starting a second BEGIN TRANSACTION before committing or rolling back the first causes an error.