Saturday, March 12, 2016

SET OPERATORS

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

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...