Useful SQL queirs

-To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

·  To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

·  Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

·  Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);

·  Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);

·  How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);

·  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)

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

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