DML COMMANDS :-

  1. Insert
  2. Update
  3. Delete
  4. Merge

Insert Command in SQL Example:

Copying the data from one table to another table:

(i)   Syntax: insert into <target table>

Select * from <source table>

Note:   Both target table & source table structure must be same.

Example: insert into emp1

Select * from emp       {coping all records to emp1}

(2) copying the particular record :

 Example: insert into emp1

Select * from emp where deptno in(10,20)

(3) If structure is different :     

{emp1 having (Target Tab) 3 cols emp

Emp having  ( Source Tab) number of cols}

Insert into emp1

Select empno,ename,sal from emp.

(4). If emp1 table having : Tar. Table

 Empno ename sal dno hiredate

And emp table having : source table

Empno ename sal.

Example :insert into emp1(empno,ename,sal)

Select *from emp.

UPDATE COMMAND (Update Command in SQL Example) :–

  • Update command is used to modify data in the table.

Syntax   :-

                    Update <tabname>

                     Set column name=value

                      [colname=value——–]

                      [where <condition>]

  • update all employees commission is to 500.
Update employee set comm=500
  • Update the employee commission is 500,whos commission is  null,”

      Update emp set comm=500

        Where comm is NULL.

  • ”Increment employee commission by 200 whos commission is not null”.

      Update emp set comm =comm+200

             Where comm is not null.

  • Increment employee salary by 10% those who are working for dept 10&20.

                 Update emp set sal=sal*1.1

                  Comm =comm*1.2

    Where dept in (10,20).

  • Increment employee salary as follows:

     If clerk         10%

Salesman            15%

Manager              20%

Others                   5%

Update emp

         Set sal=case job

                      When ‘clerk’ then sal*1.1

                       When ‘salesman’ then sal*1.15

                        When ‘manager’ then sal*1.2 

                     Else

                         Sal *1.05

                            End

DELETE COMMAND (Delete Command in SQL Example) :–

  • It is used to delete record(or)records from a table.

Syntax  :- delete from <tabname>[where <condition>]

Example:- delete from emp where empno=7369.

Delete from emp where ename like ‘s%’

ROW  FRAGMENT    :-

  • Row fragmentation occurs when varchar fields are updated.
  • The value belongs to one row stored in two different rows.
  • Row fragmentation degrates performance.
  • The fields which are not frequently updated there fields must be declared with varchar type.
  • The fields which are frequently updated those fields must be declared with char type.