SQL Complex Queries

1. Select FIRST n records from a table.

select *
from emp
where rownum <= &n;

2. Find the 5th MAX salary in the emp table.

select distinct sal
from emp e1
where 5 = (select count(distinct sal)
                  from emp e2
                   where e1.sal <= e2.sal
                   );

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

4. How can I create an empty table emp1 with same structure as emp?

Create table emp1 as
select * from emp where 1=2;

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

6. How to fetch only common records from two tables emp and emp1?
Select * from emp
 Intersect
Select * from emp1;

7. For each department that has more than five employee, retrive the department number and the number of employee who are making more than 20,000 Rs.

select depno, count(*)
from dept, emp
where dept.deptno=emp.deptno
and emp.sal>=20000
and dept.deptno in
                          ( select deptno
                             from emp
                             group by deptno
                             having count(*)>5);


8. To fetch alternate records from a table.

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