SQL Transactions and SQL Wildcard Operators

Ratings:
(4)
Views:766
Banner-Img
  • Share this blog:

SQL Transactions

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.

Properties of  SQL Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:

  • Atomicity: ensures that  all  operations  within  the  work  unit  are  completed  successfully;  otherwise,  the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  • Consistency: ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation: enables transactions to operate independently of and transparent to each other.
  • Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

SQL Transaction Control

There are following commands used to control transactions:

  • COMMIT: to save the changes.
  • ROLLBACK: to rollback the changes.
  • SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
  • SET TRANSACTION: Places a name on a transaction.

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 in SQL

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 | +----+----------+-----+-----------+----------+

SQL ROLLBACK Command

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 | +----+----------+-----+-----------+----------+

The SAVEPOINT Command in SQL

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.

SQL RELEASE SAVEPOINT Command

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.

SQL SET TRANSACTION Command

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

About Author
Authorlogo
Name
TekSlate
Author Bio

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.


Stay Updated


Get stories of change makers and innovators from the startup ecosystem in your inbox