Queries

1. Select even row from emp table

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

2. Select odd row from emp table

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

3. Select Nth row from emp table where N=10

Select * from EMP where rowid=(select rowid from EMP where ROWNUM <= 10
MINUS
Select rowid from EMP where ROWNUM < 10)

4. Select rows X to Y from emp table where X=5 and Y=7

Select * from EMP where rowid in (select rowid from EMP
Where rowNUM <= 7
MINUS
Select rowid from EMP where rownum < 5);

5. All even, Odd, Nth rows from a table

Select * from emp where (rowid, 0) in (select rowid, mod (rownum,&n) from emp);

6. Select N rows from a table
Select * from EMP a
Where &n>(select count (distinct (sal)) from emp b where a. Sal >= b.sal)
Order by sal desc;

Please check this query for the correctness for n=1,2,3,4,5

7. Select Employee having maximum salary in department from emp table

Select deptno, empno, sal from EMP a where (deptno, sal) in (
Select deptno, max (sal) from EMP b
Where a.deptno = b.deptno
Group by b.deptno) order by 1