11/14/16

ORACLE IMPORTANT QUERIES-PART2

1.     Get duplicate rows from the table:
Select empno, count (*) from EMP group by empno having count (*)>1;
2.     Remove duplicates in the table:
Delete from EMP where rowid not in (select max (rowid) from EMP group by empno);
3.     Below query transpose columns into rows.
Name
No
Add1
Add2
abc
100
Hyd
bang
xyz
200
Mysore
pune

Select name, no, add1 from A
UNION
Select name, no, add2 from A;
4.     Below query transpose rows into columns.
select
emp_id,
max(decode(row_id,0,address))as address1,
max(decode(row_id,1,address)) as address2,
max(decode(row_id,2,address)) as address3
from (select emp_id,address,mod(rownum,3) row_id from temp order by emp_id )
group by emp_id
Other query:
select 
emp_id,
max(decode(rank_id,1,address)) as add1,
max(decode(rank_id,2,address)) as add2,
max(decode(rank_id,3,address))as add3 
from
(select emp_id,address,rank() over (partition by emp_id order by emp_id,address )rank_id from temp )
group by
emp_id
5.     Rank query:
Select empno, ename, sal, r from (select empno, ename, sal, rank () over (order by sal desc) r from EMP);
6.     Dense rank query:
The DENSE_RANK function works acts like the RANK function except that it assigns consecutive ranks:
Select empno, ename, Sal, from (select empno, ename, sal, dense_rank () over (order by sal desc) r from emp);
7.     Top 5 salaries by using rank:
Select empno, ename, sal,r from (select empno,ename,sal,dense_rank() over (order by sal desc) r from emp) where r<=5;
Or 
Select * from (select * from EMP order by sal desc) where rownum<=5;
8.     2 nd highest Sal:
Select empno, ename, sal, r from (select empno, ename, sal, dense_rank () over (order by sal desc) r from EMP) where r=2;
9.     Top sal:
Select * from EMP where sal= (select max (sal) from EMP);
10.                        How to display alternative rows in a table?
          SQL> select *from emp where (rowid, 0) in (select rowid,mod(rownum,2) from emp);

11.                        Hierarchical queries
Starting at the root, walk from the top down, and eliminate employee Higgins in the result, but
process the child rows.
SELECT department_id, employee_id, last_name, job_id, salary
FROM employees
WHERE last_name! = ’Higgins’
START WITH manager_id  IS NULL
CONNECT BY PRIOR employee_id = menagerie;

Google Q&A Forum