Data Manipulation Language (DML)

1. Oracle Introduction

INSERT :

• By value method
• By address method

1. USING VALUE METHOD :

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

   Ex:
   SQL> insert into student values (1, 'sudha', 100);
   SQL> insert into student values (2, 'saketh', 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.
   
   
2. 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: Jagan
   Enter value for marks: 300
   old   1: insert into student values(&no, '&name', &marks)
   new   1: insert into student values(1, 'Jagan', 300)
   

   SQL> /

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

   
   
   
3. 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, 'Ramesh');
   SQL> insert into student (no, name) values (4, 'Madhu');

   
   
   
4. INSERTING DATA INTO SPECIFIED COLUMNS USING ADDRESS METHOD

   Syntax:
   insert into <table_name>(col1, col2, col3 ... coln) 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 (no, name) values (&no, '&name');

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

   SQL> /

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

Using UPDATE Command :

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

Ex:
SQL> update student set marks = 500;

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

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

Using DELETE :

Syntax:
Delete <table_name> where <condition>;

Ex:
SQL> delete student;

SQL> delete student where no = 2;