Data Manipulation Language In SQL

Welcome Back to Software Engineers, In this article we will learn about Data Manipulation Language(DML) in SQL. By the end of this Article you will learn the fallowing things:

1. Describe each data manipulation language(DML) statement
2. insert rows into a table
3. update rows into a table
4. Delete rows from a table.

In This Article You will learn how to use DML statement in SQL , how to insert new rows in an existing Table,updating or modifying rows in existing table and how to delete rows from a table.

DML:  It stands for Data Manipulation Language. It is a core part of SQL.When you want add,update or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of work is called as Transaction

When DML Statement is executed:

• when you add new rows to a table
• when you modify existing rows in a table
• when you remove existing rows from a table

 Example:

                  Consider a banking database, when a bank customer transform money from a saving account to a checking account, by that time the transaction might consist of three operations:

• Decreasing the saving account
• Increasing the saving account
• Recording the Transaction

The database server must guarantee that all the three SQL statements are performed to maintain the proper balance.

Now we will learn DML statements one by one with examples,

a. Insert new rows into a table
b. Update rows into a table
c. Delete rows from a table

Insert new row(or)adding new row into a table:

This INSERT statement is used to insert (or) add new row(or) rows into a table.

Syntax:  insert into table[column [,column.....])] 
                                     values(value[,value......]);

From the above Syntax

table       :  name of the table
column  :  name of the column in the table
value      : corresponding value for the column

The fallowing is the employee table:

Suppose if we want  add new row by using insert statement  as fallows:

insert into employee(emp_id,ename,Dept_id,mgr_id,salary,job_id)
                          values(104,'rajesh',40,105,35000,'EX-DIR');

NOTE: you have to enclose character and date values within single quotation marks.

Inserting Special values:

You can use functions to enter special values in your table.

If you want insert Date,username,comments in the Report Table as below:

Then you need to write insert statement as fallow:

insert into Report(Date, user name, comments)
                          values(SYSDATE,'LUCKY','VERY GOOD');

NOTE:  if a date must be entered in a format other than the default format.
for example, with another century(or) specific time you must use the TO_DATE function.

 insert into Report(Date,username,comments)
                          values(to_date('15 OCT,2016'),'LUCKY','VERY GOOD');

 Result :  

Update rows in a table:

Update is used to modify the existing values in a table.

Syntax: update table column=value[,column=value,....]
                               [WHERE condition]

NOTE: you can update more than one row at a time(if required)

From the above syntax: 
table    :  name of the table
column: name of the column in the table
value: corresponding  value (or) sub query for the column
condition: identifies the rows to be updated and is composed of column names,expressions,constants,sub queries and comparison operators.

Examples:

1.  UPDATE employee SET Dept_id=50 WHERE emp_id=104;
2. UPDATE employee SET salary=35000 WHERE emp_id=104;

In general, use the primary key column in the WHERE clause to identify a single row for update. If you want update all rows in a table WHERE condition is not required.


Delete rows From a table:
It is used to remove existing rows from a table.

Syntax: Delete[from]<table name>
                            [Where condition];

Ex:  Delete from Employee WHERE emp_id in(101,102);

Note: if you omit where clause all rows in the table are deleted.