SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

TCL Commands in SQL

In Structured Query Language, TCL is an abbreviation for Transaction Control Language. A single unit of work in a database is formed after the consecutive execution of commands is known as a transaction.

There are some commands in the Structured Query Language, and Transaction Control Language command is one of the SQL commands. TCL commands handle the transactions in a database

In Structured Query Language, Commit, Rollback, and Savepoint are the Transaction Control Language commands widely used in SQL.

Now let’s understand and learn how the Transaction Control Language commands work in the Structured Query language with the help of a few examples. Transaction Control Language commands are:

  1. COMMIT Command
  2. ROLLBACK Command
  3. SAVEPOINT Command     

COMMIT Command

Commit Command in SQL is used to save all the transactions which change permanently to the disk. Whenever we perform the operation on Data Definition Language commands (DDL) such as INSERT command, UPDATE command, and DELETE command, whatever modifications made by these commands are permanent but before closing the current session or till the end of the session. We can easily undo the operation we performed using the Data Definition Language before the session gets shut down. Commit command is used to save the modification permanently to the storage without session ends.

Syntax:

COMMIT;

If you have an existing database in the system, use that existing database or create a new database. We will use the existing database Company.

USE Company

Consider the already existing employees' tables which have the certain data:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4

In SQL, the START TRANSACTION query is used to begin the transaction.

START TRANSACTION;           
TCL Commands In SQL

Now, we will insert two records into the existing Employees table. To insert multiple records, we will use the below statement:

INSERT INTO EMPLOYEES (EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, DEPARTMENT, MANAGERID) VALUES (4002, 'ASHWINI', 'BAGHAT', 54500, 'JAIPUR', 'JAVA', 3), (4003, 'PURVA', 'DHANDEKAR', 57000, 'PUNE', 'TESTING', 4);

We will now use the SELECT query to verify whether the records are inserted successfully or not in the table.

SELECT * FROM EMPLOYEES;

Output on the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500JAIPURJAVA3
4003PURVA DHANDEKAR57000PUNETESTING4
TCL Commands In SQL

The output shows all the records are inserted successfully.

To save the INSERT operation on the Employees table, we will execute the COMMIT command.

COMMIT;
TCL Commands In SQL

In SQL, by default, auto-commit is on. To turn off the auto-commit in the SQL, set the auto-commit value to 0.

We will update the employee id 4002 and set the city as 'Noida. Use the below query to update the employee record.

UPDATE EMPLOYEES SET CITY = 'NOIDA' WHERE EMPLOYEEID = 4002;

We will now use the SELECT query to verify whether the record is updated successfully or not in the table.

SELECT * FROM EMPLOYEES;

Output on the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003PURVA DHANDEKAR57000PUNETESTING4
TCL Commands In SQL

As we can see in the output, employee Id 4002 city was modified.

To save the changes on the employees’ table, we will write a COMMIT command.

COMMIT;
TCL Commands In SQL

Now, we will delete the employee id 4003 from the Employees table. Use the below query to delete the employee record.

DELETE FROM EMPLOYEES WHERE EMPLOYEEID = 4003;

We will now use the SELECT query to verify whether the record is deleted successfully or not in the table.

SELECT * FROM EMPLOYEES;

Output on the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
TCL Commands In SQL

The output confirms we deleted the employee id 4003 details.

To save the changes on the employees’ table, we will write a COMMIT command.

COMMIT;
TCL Commands In SQL

SAVEPOINT command

Database operations performed can be divided into two parts. For example, consider all the delete statements we performed as one segment of the transaction and the update statement as one segment of the transaction. To save insert queries into the insert part and delete queries into the delete part of the same transaction, we use the SAVEPOINT command in SQL. Suppose we want to save all the delete-related queries with the savepoint named delete. To save all the delete-related operations in one savepoint, we have to perform the SAVEPOINT command followed by the savepoint name after performing the delete query.

Syntax:

SAVEPOINT savepoint name; 

ROLLBACK command

While carrying out a transaction, we built the savepoints to save the different segments of the transaction. As the User's requirements changes, the User can undo the transaction to different savepoints. Consider a scenario: We have initiated a transaction followed by the table creation and record insertion into the table. We built the INS named savepoint after adding records to the table. After Insertion, we performed the delete operation, and later we remembered mistakenly deleted the wrong records. We will undo our transaction to reach the savepoint INS. To undo the transaction, use the ROLLBACK command.

We can roll back the operation we performed or roll back the entire savepoint transaction.

Syntax to Rollback the operation:

ROLLBACK;

Syntax to Rollback the transaction:

ROLLBACK TO savepoint;

Examples of the SAVEPOINT and ROLLBACK commands:

If you have an existing database in the system, use that existing database or create a new database. We will select the already existing database named Company.

USE Company

Consider the existing employee's tables which have the following records:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3

In SQL, the START TRANSACTION query is used to begin the transaction.

START TRANSACTION;           
TCL Commands In SQL

Now, we will insert two records in the existing Employees table. To insert multiple records we will use below statement:

INSERT INTO EMPLOYEES (EMPLOYEEID, FIRST_NAME, LAST_NAME, SALARY, CITY, DEPARTMENT, MANAGERID) VALUES (4003, 'PURVA', 'DHANDEKAR', 57000, 'PUNE', 'TESTING', 4), (5001, 'ASHWIN', 'PATEL', 54500, 'JAIPUR', 'JAVA', 3);

We will now use the SELECT query to verify whether the records are inserted successfully or not in the table.

SELECT * FROM EMPLOYEES;

Output on the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003PURVADHANDEKAR57000PUNETESTING4
5001ASHWINPATEL54500JAIPURJAVA3
TCL Commands In SQL

As we all know, the SAVEPOINT command in SQL is used to save different parts of the same transaction. Assume, Insertion is one segment of our transaction. Use savepoint to save this insertion segment as Insertion.

SAVEPOINT Insertion;
TCL Commands In SQL

Now, we will perform the UPDATE operation on the Employee tables, and we will update employee salary by 1.2 for the employee id 2003.

UPDATE EMPLOYEES SET SALARY = SALARY * 1.2 WHERE EMPLOYEEID = 2003;

We will cross-check the update operation by executing the SELECT query on the Employees table:

SELECT * FROM EMPLOYEES WHERE EMPLOYEEID = 2003;
EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
2003RUCHIKAJAIN60000MUMBAITESTING4
TCL Commands In SQL

The above query results show that the employee id 2003 record is updated successfully.

Consider the updation query as one segment of our transaction. We will save the update operation part using a SAVEPOINT named an Updation.

SAVEPOINT Updation;
TCL Commands In SQL

Now, we will perform a delete operation on the Employees table. We will perform delete operation on employees table where employee salary is greater than 60000.

DELETE FROM EMPLOYEES WHERE SALARY > 60000;

We will cross-check the delete operation by executing the SELECT query on the Employees table:

SELECT * FROM EMPLOYEES;

Output on the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2003RUCHIKAJAIN60000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003PURVADHANDEKAR57000PUNETESTING4
5001ASHWINPATEL54500JAIPURJAVA3
TCL Commands In SQL

The above query shows that deleting the employee records was successful.

Consider the delete queries as one segment of our transaction. We will save the update operation part using a SAVEPOINT named a Deletion.

SAVEPOINT Deletion;
TCL Commands In SQL

Suddenly User realized the query we wanted to perform on the deletion operation was not supposed to be that query. In such a situation, users will roll back to the entire transaction to the SAVEPOINT; the User can roll back to any SAVEPOINT he wants to roll back like Insertion, Updation SAVEPOINT.

ROLLBACK TO Insertion;

If the user don' want to remove the records. Hence, the User will be rollbacked to the Insertion SAVEPOINT.

To cross-check that we have got the same employee table we had before performing the updation and deletion operations. To retrieve the employees' details perform the SELECT operation on the employees' table.

SELECT * FROM EMPLOYEES;
EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65000PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAORACLE1
1003NIKHILVANI50000JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003PURVADHANDEKAR57000PUNETESTING4
5001ASHWINPATEL54500JAIPURJAVA3
TCL Commands In SQL



ADVERTISEMENT
ADVERTISEMENT