PL/SQL>SQL>Data Manipulation Language

Data Manipulation Language(insert,update,delete)

INSERT

This will be used to insert the records into table.

We have two methods to insert data.

  1. By value method
  2. By Address method

a) Using Value Method:

      Syntax:  insert into table_name values (value1, value2, value3 …. Valuen);

      Example:  SQL> insert into Graduates values (1, ’Adiya’, 100);

                    SQL> insert into Graduates values (2, ’Aahil’, 200);

      To insert a new record again you have to type entire insert command, if there are 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.)

      Example:  SQL> insert into Graduates values (&no, '&name', &marks);

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

Enter value for no: 2
Enter value for name: Garry
Enter value for marks: 400
old 1: insert into Graduates values(&no, '&name', &marks)
new 1: insert into Graduates values(2, 'Garry', 400)

c) Inserting Data into Specified Columns using Value Method:

      Syntax:  insert into table_name (col1, col2, col3 … Coln) values (value1, value2, value3 …Valuen);

      Example:  SQL> insert into Graduates (no, name) values (3, ’Darsh’);

                      SQL> insert into Graduates (no, name) values (4, ’Grace’);

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.)

      Example:  SQL> insert into Graduates (no, name) values (&no, '&name');

Enter value for no: 5
Enter value for name: Jaison
old 1: insert into Graduates (no, name) values(&no, '&name')
new 1: insert into Graduates (no, name) values(5, 'Jaison')

Enter value for no: 6
Enter value for name: Niket
old 1: insert into Graduates (no, name) values(&no, '&name')
new 1: insert into Graduates (no, name) values(6, 'Niket')

MULTIPLE INSERTS

We have table called DEPT with the following columns and data

DEPTNO   DNAME   LOC
--------   --------   ----
10   accounting   new york
20    research    dallas
30   sales           Chicago
40   operations   boston

i) Create Graduates table:

      SQL> Create table Graduates(no number(2),name varchar(2),marks number(3));

ii) Multi Insert with all Fields:

SQL> Insert all
Into Graduates values(1,’a’,100)
Into Graduates values(2,’b’,200)
Into Graduates values(3,’c’,300)
Select *from dept where deptno=10;

-- This inserts 3 rows

iii) Multi Insert with Specified Fields:

SQL> insert all
Into Graduates (no,name) values(4,’d’)
Into Graduates(name,marks) values(’e’,400)
Into Graduates values(3,’c’,300)
Select *from dept where deptno=10;

-- This inserts 3 rows

iv) Multi Insert with Duplicate Rows:

SQL> insert all
Into Graduates values(1,’a’,100)
Into Graduates values(2,’b’,200)
Into Graduates values(3,’c’,300)
Select *from dept where deptno > 10

-- This inserts 9 rows because in the select statement retrieves 3 records (3 inserts for each row retrieved)

v) Multi Insert with Conditions Based:

SQL> Insert all
When deptno > 10 then
Into Graduates1 values(1,’a’,100)
When dname = ‘SALES’ then
Into Graduates2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into Graduates3 values(3,’c’,300)
Select *from dept where deptno>10;

-- This inserts 4 rows because the first condition satisfied 3 times, second conditionsatisfied once and the last none.

vi) Multi Insert with Conditions Based and Else:

SQL> Insert all
When deptno > 100 then
Into Graduates1 values(1,’a’,100)
When dname = ‘S’ then
Into Graduates2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into Graduates3 values(3,’c’,300)
Else
Into Graduates values(4,’d’,400)
Select *from dept where deptno>10;

-- This inserts 3 records because the else satisfied 3 times

vii) Multi Insert with Conditions Based and First:

SQL> Insert first
When deptno = 20 then
Into Graduates1 values(1,’a’,100)
When dname = ‘RESEARCH’ then
Into Graduates2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into Graduates3 values(3,’c’,300)
Select *from dept where deptno=20;

-- This inserts 1 record because the first clause avoid to check the remaining conditions once the condition is satisfied.

viii) Multi Insert with Conditions Based, First and Else:

SQL> Insert first
When deptno = 30 then
Into Graduates1 values(1,’a’,100)
When dname = ‘R’ then
Into Graduates2 values(2,’b’,200)
When loc = ‘NEW YORK’ then
Into Graduates3 values(3,’c’,300)
Else
Into Graduates values(4,’d’,400)
Select *from dept where deptno=20;

-- This inserts 1 record because the else clause satisfied once

ix) Multi Insert with Multiple Tables:

SQL> Insert all
Into Graduates1 values(1,’a’,100)
Into Graduates2 values(2,’b’,200)
Into Graduates3 values(3,’c’,300)
Select *from dept where deptno=10;

-- This inserts 3 rows

** You can use multi tables with specified fields, with duplicate rows, with conditions, with first and else clauses.

UPDATE

This can be used to modify the table data.

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

      Example:  SQL> update Graduates set marks = 500; (If you are not specifying any condition this will update entire table.)

                      SQL> update Graduates set marks = 500 where no = 2;

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

DELETE

This can be used to delete the table data temporarily.

      Syntax:  Delete table_name where condition;

      Example:  SQL> delete Graduates; (If you are not specifying any condition this will delete entire table.)

                      SQL> delete Graduates where no = 2;

Go Back