DML COMMANDS :-
- Insert
- Update
- Delete
- 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.