Data Manipulating Language

This is the 2^nd sub language in SQL, which is used to manipulate the data within the database. This Language contains 4 commands

1. Inser
2. Update 
3.  Delete 
4.  Select

1}Insert:

Using this command we can Insert the records into the existing table

We can insert the records into the table in two methods

1.     Explicit method

2.     Implicit method

Explicit method:

In this method user has to enter all the values into all the columns without anything omitting (or) left any column data

Sy: Insert Into <Table Name> Values <Val1, Val2, ….Valn>;

EX: insert into employee values(1,'om')—inserting a record into the Table(Explicit)

insert into Employee values(2,'sampath')—inserting one more record(Explicit)

Implicit method:

In this method we can enter the values into the required columns in the table, so that user can omit some columns data while he enters the records into the table

If the user omit any column data in the table then it automatically takes NULL

Sy: Insert Into <Table Name> (Col1, Col2….Coln) Values (Val1, Val2,… Valn);

Ex: insert into employee(EmpId) values(20)—insert using(Implicit)

adding new column salary  into employee table

alter table Employee add Salary money

2}Update:

This command is used to modify the data in the existing table

By using this command we can modify all the records in the table & also specific records in the table (Using ‘where’ clause)

Sy: Update <Table Name> Set Col=Value;

Ex: update Employee set salary=10000—this Update will make effect on all the records

Syntax changes for more than one data simultaneously 

Sy: Update <Table Name> Set Col1=Value, Col2=Value , …. Coln=Value;

Ex: update employee set salary=50000,EmpId=5

3}Delete

• This command is used to delete the records from existing table
• Using this command we can delete all the records and also to delete specific record (by using ‘where’ clause)

Sy: Delete From <Table Name>

Ex: delete from Employee –deletes all records from the table

here again iam inserting the records like

insert into Employee values(1,'sampath',10000)

insert into Employee values(2,'kumar',15000)

insert into Employee values(3,'vaddepally',40000)

To Delete a particular column value we use

delete from employee where empid=2 -—deletes only one record with empid 

 Differences between Truncate & Delete

      

SNO	TRUNCATE	DELETE
01	It is a DDL command	It is a DML command
02	It is a permanent deletion(Truncate Releases Memory)	It is temporary deletion(Delete will not releases memory)
03	Specific record deletion is not possible	We can delete the specific record or Particular Record
04	In Truncate Restoring is Not Possible	In Delete, Restoring is possible
05	Truncate will Reset Identity	Delete Will not Reset Identity

4}Select:

This command is used to retrieve the data from existing table.

Using this command we can retrieve all the records & also specific records from existing table (by using ‘where’ clause)

Using this command we can retrieve the data from the table in 3 ways

Projection

Selection

Joins

Sy: SELECT * FROM <TABLE NAME>

Ex: select * from Employee –Displaying all records from the Table.

* represents all columns

Projection:

Retrieving the data from specific columns is known as Projection

Sy: Select Col1,Col2……..Coln From  <Table Name>

Ex: selectEmpId,EName from Employee where 

Selection:

Retrieving the data based on some condition is called selection

In SQL, whenever we need to check a condition, we need to use a special clause called ‘where’

Where clause:

This clause is used to check the condition based on the condition, we can retrieve, update, delete specific records in the table

So we can apply the where clause only in select, update & delete

Select- where clause:

Sy: Select * From <Table Name> Where <Condition>

Ex:Select * from Employee Where EmpId=1;