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

The `ROLLBACK TO SAVEPOINT PreUpdate;` command will undo all changes made after the savepoint was created (the salary updates), while preserving the changes made before it (the record deletions). A full `ROLLBACK;` would undo the entire transaction, including the valid deletions.

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

The `COMMIT` command concludes the current transaction and makes all of its changes permanent and visible to other user sessions. `ROLLBACK` is used to undo changes, and `SAVEPOINT` is used to create an intermediate marker.

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

The `READ COMMITTED` isolation level ensures that a transaction will only see data that has been committed. This prevents 'dirty reads', where one transaction sees the uncommitted, in-progress work of another. User B will read the last committed version of the data.

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

Atomicity is the 'A' in the ACID properties and guarantees that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails, the entire transaction is rolled back, leaving the database unchanged.

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

Data Definition Language (DDL) statements (like CREATE, ALTER, DROP) modify the database's structure. In most RDBMS (like Oracle and MySQL), executing a DDL statement will cause an implicit COMMIT of the preceding transaction before the DDL statement runs.

SQL Practice Test

SQL Practice Test

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