• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

SQL Transactions and SQL Wildcard Operators

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 |

+—-+———-+—–+———–+———-

Summary
Review Date
Reviewed Item
SQL Transactions and SQL Wildcard Operators
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SQL Transactions and SQL Wildcard Operators"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.