Comments

Saturday, June 16, 2012

SQL Tutorial on Data Manipulation Language (DML)

Posted by on Saturday, June 16, 2012 Read our previous post
After writing a tutorial on SQL Baiscs, long back, here I am writing again on SQL, but this time on DML i.e. Data Manipulation Language.

By the full form of DML we understand that with the help of this we will be able to manipulate data. Now what kind of manipulation can we do might be the next question in your mind, so SQL supports manipulation like INSERT, UPDATE, DELETE and MERGE


INSERT 



The INSERT command in SQL is used to add records into an existing table.

The INSERT command has two types of syntax, i.e. it can be written in two ways, here are the two ways :

SYNTAX

First way is :

INSERT INTO table_name 
VALUES (value1, value2, value3,...)

Second way :

INSERT INTO table_name(column1, column2, column3,...) 
VALUES (value1, value2, value3,...)

INSERT Example


We have the following "Persons" table:

P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai


Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

INSERT INTO Persons 
VALUES (4,'J', 'R', 'ANDHERI W', 'MUMBAI')

The "Persons" table will now look like this:


P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai
4JRAndheri WMumbai

Now using the 2nd way, i.e. inserting values into specified columns. 

The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the "FirstName" columns:

INSERT INTO Persons (P_Id, LastName, FirstName) 
VALUES (5, 'Mark', 'Patel')


P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai
4JRAndheri WMumbai
5MarkPatel


UPDATE 



The UPDATE command can be used to modify the records in the existing table, either in bulk or individually.

SYNTAX

UPDATE table_name

SET column1=value, column2=value2,...

WHERE some_column=some_value

NOTE : As I mention above that update can modify either a single record or a bulk record. So the where  clause in the update statement helps us to do that. If we use a where clause then whenever the condition in true the record will get updates and if don't use the where clause then all the records are appended.


UPDATE EXAMPLE


 We have the following "Persons" table:

P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai
4JRAndheri WMumbai
5MarkPatel




Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

UPDATE Persons 
SET Address='Lower Parel', City='Mumbai'
WHERE LastName='Patel' AND FirstName='Mark'

The "Persons" table will now look like this:


P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai
4JRAndheri WMumbai
5MarkPatelLower ParelMumbai


Important Note :

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

P_IdFirstNameLastNameAddressCity
1RajKumarLower Parel Mumbai
2SahilShahLower Parel Mumbai
3HarshGuptaLower Parel Mumbai
4JRLower Parel Mumbai
5MarkPatelLower ParelMumbai

DELETE


The DELETE command is used to remove records from an existing table. It can only delete row(s) and not column(s).

SYNTAX

DELETE FROM table_name
WHERE some_column=some_value

Note : The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

DELETE Example


The "Persons" table:


P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai
4JRAndheri WMumbai
5MarkPatelLower ParelMumbai


Now we want to delete the person "Patel, Mark" in the "Persons" table.

We use the following SQL statement:


DELETE FROM Persons

WHERE LastName='Patel' AND FirstName='Mark'


The "Persons" table will now look like this:

P_IdFirstNameLastNameAddressCity
1RajKumarVile ParleMumbai
2SahilShahBabar RoadNew Delhi
3HarshGuptaS.V. RoadMumbai
4JRAndheri WMumbai

MERGE


MERGE statements was introduced in Oracle 9i . This command was beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow and for the performance improvement. Oracle claim it will improve the performance.

SYNTAX

MERGE INTO <table_name>
USING      <table_or_view>
ON         (<condition>)
WHEN MATCHED THEN 
           <UPDATE SET table_name.col_name =  table_or_view.col_name> 
WHEN NOT MATCHED THEN 
           <INSERT (clo_list) VALUES (value_list)>





NOTE : Don't forget to COMMIT after every insertion, updation or deletion of records otherwise the action won't become permanent and the table will get locked.

© 2010 Code 2 Learn