SQL - Structured Query Language Transaction Control Language (TCL) Questions and Answers

Question 1

A database administrator begins a transaction, deletes a batch of old records, creates a savepoint named 'PreUpdate', and then updates the salaries for all employees in the 'Sales' department.
After reviewing the changes, they realize the salary update was incorrect and needs to be undone, but the deletion of old records should be kept.
Which TCL command should they use?

Accepted Answer

ROLLBACK TO SAVEPOINT PreUpdate;

Answer

ROLLBACK;

Answer

COMMIT;

Answer

DELETE FROM Employees WHERE Department = 'Sales';

Question 2

What is the primary purpose of the `COMMIT` command in SQL?

Accepted Answer

To permanently save all changes made in the current transaction, making them visible to other users.

Answer

To undo all changes made since the beginning of the transaction.

Answer

To create a temporary marker within a transaction to which one can later return.

Answer

To start a new transaction and set its isolation level.

Question 3

User A starts a transaction and updates a customer's address but does not issue a `COMMIT`. Simultaneously, User B runs a `SELECT` query on that same customer's address. Assuming the database's default transaction isolation level is `READ COMMITTED`, what will User B see?

Accepted Answer

User B will see the original customer address as it existed before User A's transaction began.

Answer

User B will see the new, updated address from User A's uncommitted transaction.

Answer

User B's query will be blocked and will wait until User A either commits or rolls back.

Answer

User B's query will fail with a 'resource locked' error.

Question 4

Which of the following statements best describes the concept of Atomicity in the context of database transactions (ACID)?

Accepted Answer

A transaction is an 'all or nothing' proposition; either all of its operations succeed, or none of them are applied.

Answer

A transaction's changes, once committed, will persist even in the event of a system failure.

Answer

Concurrent transactions are isolated from each other, as if they were running serially.

Answer

A transaction brings the database from one valid state to another, preserving data integrity.

Question 5

In many relational database management systems, which of the following actions will typically cause an implicit `COMMIT`, automatically ending any active transaction?

Accepted Answer

Running a Data Definition Language (DDL) statement, such as `ALTER TABLE`.

Answer

Executing a `SELECT` statement that queries millions of rows.

Answer

Issuing a `SAVEPOINT` command.

Answer

Performing a series of `INSERT` statements within a single batch.

Question 6

A developer executes the following sequence of commands:

1. `START TRANSACTION;`
2. `INSERT INTO Logs (Message) VALUES ('Step 1');`
3. `SAVEPOINT A;`
4. `INSERT INTO Logs (Message) VALUES ('Step 2');`
5. `COMMIT;`

What is the outcome of this sequence?

Accepted Answer

Both 'Step 1' and 'Step 2' are permanently saved to the Logs table.

Answer

Only 'Step 1' is saved because the savepoint was never used for a rollback.

Answer

An error occurs because a `SAVEPOINT` must be followed by a `ROLLBACK TO SAVEPOINT`.

Answer

The entire transaction is rolled back because the `COMMIT` invalidates the savepoint.