DML is also known as RML, which is Responsible to manipulated language, the records of table and statements are given below
Insert, update, delete
Syntaxfor 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’);
Syntaxrecord 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;
DELETEusing 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:
ROWNUMis 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;
0 Comments