Data Manipulation Language

DML( Data Manipulation Language): -

We have discussed about DDL Commands in SQL  in earlier post Data Definition Language and now lets checkout about DML.

DML having following commands.

1.2 Data Manipulation Language

1) Insert 2) Update 3) Delete

1) INSERT

This will be used to insert the records into table.
 We have two methods to insert.
 a) By value method
 b)  By address method

 a) USING VALUE METHOD

 Syntax:
 insert into <table_name) values (value1, value2, value3 …. Valuen);
 Ex:
 SQL> insert into student values (1, ’Sree’, 100);
 SQL> insert into student values (2, ’Ram’, 200);
 To insert a new record again you have to type entire insert command, if there is  lot of records this will be difficult.
 This will be avoided by using address method.

b) USING ADDRESS METHOD

Syntax:
 insert into <table_name) values (&col1, &col2, &col3 …. &coln);
 This will prompt you for the values but for every insert you have to use forward
 slash.

Ex: SQL> insert into student values (&no, '&name', &marks);

Enter value for no: 1
 Enter value for name: Ramana
 Enter value for marks: 300
 old 1: insert into student values(&no, '&name', &marks)
 new 1: insert into student values(1, 'Ramana', 300)

SQL> /
 Enter value for no: 2
 Enter value for name: Chinna
 Enter value for marks: 400
 old 1: insert into student values(&no, '&name', &marks)
 new 1: insert into student values(2, 'Chinna', 400)

c) INSERTING DATA INTO SPECIFIED COLUMNS USING VALUE METHOD

Syntax:
 insert into <table_name>(col1, col2, col3 … Coln) values (value1, value2, 
value3 … Valuen);
 Ex:
 SQL> insert into student (no, name) values (3, ’Sreeram’);
 SQL> insert into student (no, name) values (4, ’Nithin’);

d) INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD

Syntax:
 insert into <table_name)(col1, col2, col3 … coln) values (&col1, &col2 ….&coln);
 This will prompt you for the values but for every insert you have to use 
 forward slash.

Ex: SQL> insert into student (no, name) values (&no, '&name');
 Enter value for no: 5
 Enter value for name: Ravi
 old 1: insert into student (no, name) values(&no, '&name')
 new 1: insert into student (no, name) values(5, 'Ravi')

SQL> /
 Enter value for no: 6
 Enter value for name: Mahi
 old 1: insert into student (no, name) values(&no, '&name')
 new 1: insert into student (no, name) values(6, 'Mahi')

2) UPDATE:
 This can be used to modify the table data.

Syntax:
 Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Ex:
 SQL> update student set marks = 500;
 If you are not specifying any condition this will update entire table.

SQL> update student set marks = 500 where no = 2;
 SQL> update student set marks = 500, name = 'Venu' where no = 1;

3) DELETE:

This can be used to delete the table data temporarily.

Syntax:
 Delete from <table_name> where <condition>;

Ex:
 SQL> delete from student;
 If you are not specifying any condition this will delete entire table.

SQL> delete from student where no = 2;