SET OPERATORS
NOTE:
In the first query how many no of columns are selected the same no of columns
should be selected in the second query..
Ex: select deptno,dname,loc from dept
UNION
select deptno,ename,job,sal from emp;
Error: ORA-01789: query block has
incorrect number of result columns
the above query will error bcz the no of
column in first query is not matched with the no of columns in the second query
Note:
The data type sequence of columns in first query should always match the data
type sequence of columns in the second query
Ex:
select deptno,dname from dept
UNION
select deptno,sal from emp;
Error: ORA-01790: expression must have
same datatype as corresponding expression
In the above Query the data types seq is a
mismatch so, the querywill error.
So we can make it to query as below
select deptno,null sal,dname from dept
UNION
select deptno,sal,null dname from emp;
1)UNION
Using Union duplications are not allowed..
EX:
select deptno from dept
UNION
select deptno from emp;
UNION ALL
In
UNION ALL duplications are allowed.
EX:
select deptno from dept
UNION ALL
select deptno from emp;
select x.no,x.name
from (select deptno no, dname name from
dept
UNION ALL
select deptno no,ename name from emp) x
order by x.no
Exercise
Write a query to display
empno,ename,sal,deptno,dname,loc,salgrade need all information
from emp table though the deptno exists in
the dept table or not.
and use union or union all then club the
below query
display sno,sname,fees,totalmarks,grade
from student information
select e.empno RESULT_NO
,e.ename NAME
,e.sal
,e.deptno
,d.dname
,d.loc
,s.grade
,null TOTAL_MARKS
from
emp e
,dept d
,salgrade s
where e.deptno = d.deptno(+)
and
e.sal between s.lowsal and s.highsal
union all
SELECT XSR.SNO RESULT_SNO
,XSI.SNAME NAME
,null sal
,null deptno
,null dname
,null loc
,XSG.grade grade
,XSR.TOTAL_MARKS TOTAL_MARKS
FROM
XX_STD_INFO_ANK XSI
,XX_STD_RESULT_ANK XSR
,XX_MARK_GRADE_ANK XSG
,GENDER_ANK G
WHERE
XSR.SNO=XSI.SNO(+)
AND
XSR.TOTAL_MARKS BETWEEN XSG.LMARK AND XSG.HMARK
AND
G.CODE = XSI.SEX;
INTERSECTION
Only common records are displayed from
both queries
select deptno from dept
INTERSECT
select deptno from emp;
MINUS...
It displays the unmatched records from the
first query
select deptno from dept
MINUS
select deptno from emp;
PSEUDO COLUMNS
It is an Oracle assigned value used in the
same context as an oracle database column
but not stored on a disk....
ROWNUM
ROWID
SYSDATE
NEXTVAL
CURRVAL
SYSDATE -- gives the system Date and time
NEXTVAL AND CURRVAL ARE USED FOR RETREIVAL
OF SEQNENCES VALUES.
ROWID : It returns the binary address of a row in the
database table....
It is a unique value assigned to each row
of a table.
It is automatically assiged with every
record while inserting in to the table.
It is an 18 bit hexadecimal value.
It combinations are comprises of
objectid(3),blockid(6),fileid(6) and
recordid(3)
create table emp_name
as select * from emp;
insert into emp_name
select * from emp where empno =
&empno;
commit;
EX: Select rowid row_id
,ename
,sal from emp
EX1: write a query to show the duplicate
records
select rowid row_id,e.*
from emp_1 e
where
rowid not in(
select min(rowid)
from emp_1
group by empno);
select rowid row_id,e.*
from emp_1 e
where
(rowid,empno) not in(
select min(rowid),empno
from emp_1
group by empno);
select * from xx_std_info
where rowid not in(
select min(rowid) row_id from xx_std_info
group by sno)
select *
from xx_std_info
where (rowid,sno) not in (select
min(rowid),sno
from xx_std_info
group by
sno)
EX2: how to delete the duplicate
records....
delete
from xx_std_info
where (rowid,sno) not in (select
min(rowid),sno
from
xx_std_info
group
by sno)
delete
from emp_1
where
(rowid,empno) not in(
select min(rowid),empno
from emp_1
group by empno);
fetch the first record from the table
select min(rowid) from emp_1 e;
fetch the last record from the table
select max(rowid) from emp_1 e;
Ex3: Update all the emp records sal by
1000 except first record
update emp
set sal = sal+1000
where rowid not in (select min(rowid) from
emp)
Ex4: delete the last record of the emp
table.
delete from emp
where rowid in (select max(rowid) from emp)
Ex5: Delete the first and last record of
the table
delete from emp
where rowid in (select max(rowid) from emp)
OR
rowid in (select min(rowid) from emp)
Exercise
Select duplicate records
Delete duplicate records
Update Sal = sal+100 for all duplicate
records.
--
Select first and last record
Delete first and last record
Update Sal = sal+100 for all records
except first and last record
-----------------------------
ROWNUM
rownum is a sequence value generated for
every select statment
It starts from 1.
For comparing the rownum pseudo column
we will be using ony less then or equal to
operator
It will be not working for greater then
operator
Its a unique value.
This is automatically generated when ever
select statement is executed,
it is not stored in the data base....
ex: Select rownum,ename from emp....
NOTE:
ROWNUM will works as ….
where rownum = 1
where rownum <= any
number
where rownum < any number
But it will not work in below conditions
where rownum > anynumber
where rownum = anynumber other
then 1.
Ex1: Retreive first 5 records from the emp
table....
select rownum,ename,sal,empno
from emp)
where
rownum <= 5
The above stmt return the first 5 records
Select rowid row_id,
rownum ,ename,sal,empno
from emp
where rownum > 2
The above stmt will not work.. it will not
return any values
Ex2: Display top 5 high paid employees
select rownum,x.ename,x.sal,x.empno
from (Select ename,sal,empno from emp
order by sal desc) x
where rownum <= 5
Ex4: Display second highest paid emp
information
Pass :p_no = 2 in the below query...
select row_number,ename,sal,empno
from(
select rownum
row_number,x.ename,x.sal,x.empno
from (Select ename,sal,empno from emp
order by sal desc) x)
where row_number = :p_no
other way
select
rownum,x.empno,x.ename,x.sal,x.comm,x.deptno
from
(
select empno,ename,sal,comm,deptno
from emp_1
order by sal desc) x
where rownum <=3
MINUS
select
rownum,x.empno,x.ename,x.sal,x.comm,x.deptno
from
(
select empno,ename,sal,comm,deptno
from emp_1
order by sal desc) x
where rownum <3 ;
EX:5 display any record from the table
select row_num,x.empno,x.ename,x.sal
from(
select rownum row_num,empno,ename,sal
from emp) x
where x.row_num = :p_ no;
other way using minus operator
select rownum row_num,empno,ename,sal
from emp
where rownum <= 3
minus
select rownum row_num,empno,ename,sal
from emp
where rownum < 3
Ex5: Delete any particular record from emp
table
delete from emp
where empno in
(select empno
from(
select rownum row_number
,empno,ename,sal from emp)
where row_number = :p_no)
Ex6: Display Even rows from the table....
select row_number,empno,ename,sal
from(
select rownum row_number,empno,ename,sal
from emp)
where mod(row_number,2) = 0
Ex7 : Display odd rows of a table
select row_number,empno,ename,sal
from(
select rownum row_number,empno,ename,sal
from emp)
where mod(row_number,2) != 0
Ex: display 3,4,5 records
select row_num,empno,ename,sal
from
(
select rownum row_num,empno,ename,sal
from emp)
where row_num in (3,4,5)
Exercise
Select 5 top paid employee records
Select 2 top paid employee record
Select particular 5 record record
--
Select odd rows record
Select 3 ,4 and 5 record
No comments:
Post a Comment