DML is also known as RML, which is Responsible to
manipulated language, the records of table and statements are given below
Insert,
update, delete
Syntax
for insert
Insert into <t_name>
[(col1, col2, col3,….N)
Values (value1, value2,
value3……..N);
Above syntax is syntax of data insertion using
column list. User has only remembered name of column not the sequence.
Insert
NULL value in a column
NULL
value can be assigned in a particular column in two different manners.
è Explicitly
using NULL keyword
è Implicitly
using column list
Ex
Insert
into sysemp6
Values
(2,BBB,NULL,’manger’);
Insert
into sysemp6(empno,job, name)
Values
(3,’manger’, ‘CCC’);
NOTE:
User is not allowed null value a column explicitly
or implicitly if column explicitly, if column is protected with not null
constraint.
Data
insertion using substation variable
Ex
Insert
into sysemp6
Values(&empno,’&name’,&salary,’&job’);
Syntax
record using update
Update<T_name>
Set
col_name=<value>[col_name=value, …..];
[where <condition>];
Ex
Update sysemp6
Set sal=1000, job= ‘manger’
Where
empno in (4,5);
When where clues are omitted from update statement
then it will update all value of specific column.
Ex
– update sysemp6
Set sal = NULL;
DELETE using delete
Delete from <t_name>
[Where <condition>];
If where clues are omitted from delete statement
then all data are deleting from that table.
Some important queries
To
copy a table
Create table <new t_name>
As select col1,col2,…..coln from
<old t_name>;
NOTE:
Whenever a table is copied only NOTNULL constraint
are copied to the new table except other constraint.
Copy
only structure of table without data
Ex
–
Create table hi
As select * from emp
Where 1=2;
DELETE identical record from a table:
Whenever ‘n’ number of column in a table is
created in oracle database then ‘n+1’ column in a table is created, which is automatically and that column is known
as ROWID. ROWID is a physical
invisible column and their data are not equal by system.
Ex-
Select
rowid, ename, sal from emp;
Ex
-
Delete
from emp
Where
rowed like ‘%c’;
NOTE:
ROWNUM
is another column provide by oracle, which is virtual column responsible to
display the number of records using serial number .
Ex
–
Select
rownum, ename, ename from emp;
