Data Manipulation Language:
- Data manipulation language(DML) is a core part of SQL. When you want to add, update, or delete data in the database, you execute a DML statement.
- A collection of DML statements that form a logical unit of work is called a transaction.
- The DML in SQL which contain four commands those are
i) INSERT
ii) UPDATE
iii)
DELETE
i) INSERT:
This command is used to insert the
records in the table using this command we can insert the data into the table
in two methods.
i) Explicit method.
ii)
Implicit method.
i)Explicit Method:
In this method user need to enter
all the values into the columns without left any column data.
Syntax:
insert into <table name>
values(value1,value2,....,valuen);
EX:
SQL>insert into emp values(1001,'SCOTT',10);
SQL>insert into employee('1002,'netlojava@netlojava.com');
Note:
We can use && in front of
any column. By placing like this the use is – “It takes particular column
values as default for remaining all values.
Syntax to insert the records into the table using i
nsertion operation:
insert into <table name>
values(&column1,&column2,.....,&column_n);
EX:
SQL>insert into emp
values(&empno,'&ename','&job',&sal);
SQL>insert into employee
values(&empno,'&email',&mobileno);
Implicit method:
This method we can enter the values
at required columns in the table.
Syntax:
insert into <table
name>(column1,column2,.....,column_n);
EX:
SQL>insert into emp(empno,ename) values(1003,'MILLER');
SQL>insert into employee (email,mobileno) values ('netlojava@netlojava'
,9876543210);
Syntax to insert record using & Symbol:
insert into <table name>(column1,column2,.....,column_n)
values (&column1,&column2,.....,&column_n);
EX:
SQL>insert into emp(empno,job) values(&empno,'&job')
values (1004,'MANAGER');
SQL>insert into employe(ename,sal,deptno)
values('&ename',&sal,&deptno);
ii) UPDATE:
Update is used to modify the
existing values in Table or in the base table of View.
The Table must be in the Own
schema.
UPDATE object privilege should be
available.
Syntax:
update <table name> set
<column name>=<value>[,<column name2>,...]
[where condition];
EX:
SQL>update emp set deptno=20 where empno='1002';
SQL>update employee set sal=5000 where empno='1003';
SQL>update emp set sal=3000,comm=NULL
where
job='CLERK';
Updating Two Columns with a Sub Query:
SQL>update emp set job=(select job from emp
where
empno=1004),
sal=(select job from emp
where
empno=1005),
where
empno=1002;
SQL>update emp set deptno=(select deptno from emp
where
empno=1001)
where
job=(select job from emp where empno=1002);
SQL>update emp set (job,deptno)=(select job,deptno
from
dept where empno=1003)
where
empno=1002;
Updating Rows based on Another Table:
use sub queries in UPDATE
statements to update rows in a table based on values from another table.
SQL>update emp set deptno=(select deptno from dept
where
dname='ACCOUNTING')
where
deptno=(select deptno from dept
where
dname=-'SALES');
Appling default Values:
It is used to UPDATE a value in a
column with DEFAULT value set in the constraints.
SQL>update emp set sal=DEFAULT where ename='FORD';
SQL>update emp set hiredate=DEFAULT where empno=1003;
Returning Clause:
The returning clause is introduced
from 8.0.
It is used to return a value from a
aggregate function.
This feature is introduced in grid
technology.
The clause can be specified for
table and materialized views and for views with a single base table.
Restrictions:
Each express must be a simple
expression or a single set aggregate function.
It cannot be specified for multiple
table insert.
It cannot be specified upon a view
upon which a instead of trigger is defined.
SQL>VAR sumsal number;
SQL>update emp set sal=sal*0.25
where
deptno=10
returning
sum(sal) into :sumsal;
SQL>var bsal number;
SQL>var bname varchar;
SQL>update emp set sal=sal+sal*0.25
where
empno=1004
returning
ename,sal into :bname,:bsal;
SQL>print :bname :bsal;
iii) DELETE:
This command is used to delete the
records from the existing table. Using this command we can delete all the
records and also we can delete specific records
from the table.
To DELETE rows from a table, the table
must be in the USERS SCHEMA.
DELETE any table system privilege
allows to DELETE rows from any TABLE or PARTITION TABLE or from the base table
of any VIEW.
Syntax:
delete [FROM] <table name>
[WHERE
Condition];
Ex:
SQL>delete from emp;
SQL>delete from emp empno;
SQL>delete from emp where empno in(7566,7900);
SQL>delete from emp where ename='SCOTT';
SQL>delete from emp where job='SALESMAN' and
hiredate<Sysdate;
SQL>var vsal number;
SQL>delete from emp where job='SALESMAN' and
hiredate<Sysdate
returning
SUM(sal) into :vsal;
SQL>print :vsal;
SQL>delete from emp where ename='SCOTT' or ename='KING';
No comments:
Post a Comment