Question

Referencing SQL Databases

Elaborate on a scenario where you can apply data manipulation commands and transaction controls.

Answer 1

Data Manipulation Language:

DML is a subset of SQL which is used to retrieve and update the data into the database.

The command INSERT, DELETE, UPDATE are used to manipulate data.

Transaction Control Language:

TCL is a subset of SQL which is used to control the mechanism of a transaction in the database.

The command COMMIT, ROLLBACK is used to control the transaction.

Scenario to use data manipulation commands:

Let us suppose we have a database for college and want to enter the record of a new student.

Student table has three fields RollNo, Name, and Age.

INSERT INTO Student(RollNo, Name, Age)

VALUES(150, "ABC", 22)

Scenario to use transaction control commands:

Now, suppose we receive a list of 50 students and we need to enter all details into the database.

Now we will write the same statement as given above for the 50 students.

If we are not using the transaction control mechanism then in case of failure or power off there are some chances that some record are updated into the database but some records are not updated. So, we need to check for the database again to correct the database entry.

If we are using the transaction mechanism then we can use TCL command to commit a transaction as given below:

INSERT INTO Student(RollNo, Name, Age)

VALUES(150, "ABC", 22)

.

.

.

COMMIT

Now, if a failure will occur during the processing then no record will be inserted into the database. Commit means to permanently save all the changes into the database which you have made in the current transaction.