IMPORTANT QUERIES - PART 2

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;