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
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;