A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
Practically you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.
Transactions have the following four standard properties, usually referred to by the acronym ACID:
There are following commands used to control transactions:
Transactional control commands are only used with the DML commands INSERT, UPDATE, and DELETE only. They can not be used while creating tables or dropping them because these operations are automatically commited in the database.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command. The syntax for COMMIT command is as follows: COMMIT; Example: Consider CUSTOMERS table is having following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Following is the example which would delete records from the table having age = 25, and then COMMIT the changes in the database. SQL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> COMMIT; As a result, two rows from the table would be deleted and SELECT statement would produce following result: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued. The syntax for ROLLBACK command is as follows: ROLLBACK; Example: Consider CUSTOMERS table is having following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Following is the example which would delete records from the table having age = 25, and then ROLLBACK the changes in the database. SQL> DELETE FROM CUSTOMERS WHERE AGE = 25; SQL> ROLLBACK; As a result, delete operation would not impact the table and SELECT statement would produce following result: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction. The syntax for SAVEPOINT command is as follows: SAVEPOINT SAVEPOINT_NAME; This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions. The syntax for rolling back to a SAVEPOINT is as follows: ROLLBACK TO SAVEPOINT_NAME; Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state: Example: Consider CUSTOMERS table is having following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Now here is the series of operations: SQL> SAVEPOINT SP1; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=1; 1 row deleted. SQL> SAVEPOINT SP2; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=2; 1 row deleted. SQL> SAVEPOINT SP3; Savepoint created. SQL> DELETE FROM CUSTOMERS WHERE ID=3; 1 row deleted. Now that the three deletions have taken place, say you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone: SQL> ROLLBACK TO SP2; Rollback complete. Notice that only the first deletion took place since you rolled back to SP2: SQL> SELECT * FROM CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ 6 rows selected.
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created. The syntax for RELEASE SAVEPOINT is as follows: RELEASE SAVEPOINT SAVEPOINT_NAME; Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the SAVEPOINT.
The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only, or read write. The syntax for SET TRANSACTION is as follows: SET TRANSACTION [ READ WRITE | READ ONLY ]; SQL Wildcard Operators We already have discussed SQL LIKE operator which is used to compare a value to similar values using wildcard operators. SQL supports following two wildcard operators in conjunction with the LIKE operator:
| Wildcards |
Description |
| The percent sign(%) | Matches one or more characters. Note that MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character. |
|
The underscore (_) |
Matches one character. Note that MS Access uses a question mark (?) instead of the underscore (_) to match any one character. |
The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations. Syntax: The basic syntax of % and _ is as follows: SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX_' or SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE '_XXXX_' You can combine N number of conditions using AND or OR operators. Here XXXX could be any numberic or string value. Example: Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:
| Statement |
Description |
| WHERE SALARY LIKE '200%' |
Finds any values that start with 200 |
| WHERE SALARY LIKE '%200%' |
Finds any values that have a 200 in any position |
| WHERE SALARY LIKE '_00%' |
Finds any values that have 00 in the second and third positions |
| WHERE SALARY LIKE '2_%_%' | Finds any values that start with 2 and are at least 3 characters in length |
| WHERE SALARY LIKE '%2' |
Finds any values that end with 2 |
| WHERE SALARY LIKE '_2%3' |
Finds any values that have a 2 in the second position and end with a 3 |
| WHERE SALARY LIKE '2_3' |
Finds any values in a five-digit number that start with 2 and end with 3 |
Let us take a real example, consider CUSTOMERS table is having following records: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ Following is an example which would display all the records from CUSTOMERS table where SALARY starts with 200: SQL> SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%'; This would produce following result: +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 3 | kaushik | 23 | Kota | 2000.00 | +----+----------+-----+-----------+----------
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
