Some Complex quries

1) How to delete duplicate records
A) delete from emp where rowid != (select max(rowid) from emp where group by empno);
==========================================================================================================
2) update the salary with avg sal of the employee whoes salary is less than avg salary of their own dept.

update emp a set sal = (select avg(sal) from emp b where a.deptno = b.deptno)
where sal < (select avg(sal) from emp c where  c.deptno = b.deptno);
==========================================================================================================
3) N th  Max sal record

    1)select * from (select rownum R,sal S (select distinct sal s from emp order by sal desc)) where R = &no;
  if you want records then --
    2)select * from emp where rowid in
 ( select a from ( select  rowid a, dense_rank()  over (order by sal desc) r ,sal from (select sal from emp order by sal desc) ) where r = &N)

     3)select max(sal) ,level from emp where level = &n  connect by prior sal>sal GROUP by level;
==========================================================================================================   
4) list of top 5 salaries

select * from (select rownum R,sal S (select distinct sal s from emp order by sal desc)) where R <=5;
==========================================================================================================
5)3 rd max/min sal

select a.sal from emp a where 3=(select count(*)  from emp b where a.sal<=b.sal);
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
===========================================================================================================
6)select max sal of each dept

select deptno ,max(sal) from emp group by deptno;   
===========================================================================================================
7) fetch alternative records in table(even)

select * from emp where rowid in (select decode( mod(rownum,2) ,0 ,rowid ,null) from emp)

8)fetch alternative records in table(odd)

select * from emp where rowid in (select decode( mod(rownum,2) ,0 ,null,rowid) from emp)

9) last n records

select * from emp minus
(select * from emp where ronum <=
(select count(*)-&n from emp ))

10) Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

11)If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)

12)How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

13) How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)

14)Count the total sal  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

15) select second highest salry of each deptno.

select max(sal) from emp a
where sal < (select max(sal) from emp b where a.deptno = b.deptno)
group by deptno;