Introduction to Structured Query Language (SQL) – Part – 4

In What Manner to Update Information

The INSERT statement is castoff to insert new data in a table or relations, then again what if an individual want to perform a modification to a specific data? In this circumstance the Structured Query Language (SQL) statement completes the modification with the help of UPDATE statement as described below:

UPDATE My_Table_Name SET My_Column_Name = Data [ , My_Column_Name = Data ] [ FROM My_Table_Name ] [ WHERE Clause ]

For an instance, let’s assume that an individual need to update the address column or attribute of a customer who is having a CustomerID of C#25001. The subsequent command alters the Address column or attribute of the CustomerID – C#25001:

UPDATE [ XYZBank ] . [ Customer ] . [ Details ] SET Address = ‘ My New Address Line 1 , New Address Line 2 ’ WHERE CustomerID = ‘ C#25001 ’ )

Note that here in the above mentioned example the CustomerID column or attribute has been used to set the conditions for the reason that it has only unique values. In place of it, if any other column or attributes have been used like Name, then it may be possible that two (2) or more (N) individual might have same name and unintentionally the records of those individuals too will get updated.

The UPDATE statement can be castoff for more than just altering a sole column or attribute at a time. The SET key word can be castoff to set new data for a number of dissimilar columns or attributes. As a result an individual can update more than one column or attribute at a single point of time. For an instance if an individual wants to update more than two (2) columns or attributes like Address and Phone number of a single customer than he or she can perform that over a single UPDATE statement as given below:

UPDATE [ XYZBank ] . [ Customer ] . [ Details ] SET Address = ‘ My New Address Line 1 , New Address Line 2 ’ , Phone = ‘ 5746174125 ’ WHERE CustomerID = ‘ C#25001 ’

An individual can practice the SET key word to implement mathematical otherwise logical actions on the data. For an instance if an individual has a table or relation of InterestRate and wants to increase the ROI (rate of interest) by 2 % then an individual can make use of the following statement:

UPDATE [ XYZBank ] . [ Customer ] . [InterestRate] SET ROI = ROI + ( .2 )

In What Way to Delete Information

At the present as we are having data in the specific tables or relations and we have already seen in what manner to insert and update data in the tables or relations now we are left with the remaining part which is the deleting data. The Structured Query Language (SQL) offers a simple statement to remove the data from the table or relation. The code of the DELETE command is as follows:

DELETE [ FROM ] My_Table_Name [ FROM Condition_Tables ] [ WHERE Clause]

For an instance, let’s assume that an individual need to delete the record of a customer whose CustomerID is C#25001. The subsequent command deletes the specific record from the table or relation:

DELETE [ XYZBank ] . [ Customer ] . [ Details ] WHERE CustomerID = ‘ C#25001 ’

In practical situations the delete actions are not controlled by manually inputting it in Structured Query Language (SQL) queries, however are to be expected to be created from a front end application which will manage warnings as well as improve safeguards against any unintentional removal of data.

It should be noted that the DELETE statement will remove a full data or else a set of data. If an individual wants to remove a particular column or attribute or else a set of columns or attributes without destroying that record in full then he or she can use UPDATE statement plus can set the columns or attributes to NULL to over write the information which needs removing. Moreover, the DELETE statement does not do anything to the arrangement of the table or relation itself, it removes information merely. For deleting a full table or relation, or a part of a table or relation, an individual need to use the DROP statement of an ALTER TABLE query.

Transaction Control Language (TCL) in Structured Query Language (SQL)

The Structured Query Language (SQL) Data Control Language (DCL) offers safety of the database. The Data Control Language (DCL) is comprised of GRANT, REVOKE, COMMIT, and ROLLBACK commands. GRANT and REVOKE commands allow an individual to control whether an end user can view, alter, insert, and otherwise remove information of database.

Operating with Transaction Control Language (TCL)

Different software applications implement a Structured Query Language (SQL) commands as a single query or as a group of related Structured Query Language (SQL) commands to implement a database transaction. The Structured Query Language (SQL) commands INSERT, DELETE, ALTER change information in the database.

Transactions are atomic as well as robust. In the direction of begin atomic, a transaction must positively complete every commands in it; or else not a single command should be executed. And for begin robust, a transaction’s modifications to the database should be stable.

Complete a transaction by means of the COMMIT or ROLLBACK commands. COMMIT command make stable the modifications to the database formed by a transaction. On other hand the ROLLBACK reinstates the database to the state it was in before the transaction was done.

This article has a number of Structured Query Language (SQL) Transaction Control Language (TCL) statements which may be beneficial for the readers. Every single statement’s explanation has been given in brief.

In the upcoming part we will be discussing the commands like commit and rollback along with their requirements to perform. Apart from this we will see how to form database users, database administrators (DBA) practically in what manner to create a database user and finally we brief out the grant command.