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;