Inline Views using Rownum Queries

1. first five highest salary employees

  Answer:

   select * from ( select * from emp order by sal desc) where rownum<=5;

 

2. 5th highest salary employee 

   Answer:

  select * from(select * from emp order by sal desc) where rownum<=5 

  minus 

  select * from(select * from emp order by sal desc) where rownum<=4; 

 

3. write a query to display rows between 1 to 5 from emp table using rownum?

   Answer:

    select * from emp where rownum between 1 and 5;

 

4.    write a query to display rows between to 5 to 9 from table?

      Answer:

 select * from emp where rownum <=9

minus

select * from emp where rownum<=5;

 

5.     write a query to display second record from emp using rownum?

 Answer:

 select * from emp where rownum<=2

minus                    

select * from emp where rownum<=1;

 

6.    write a query to display last two records from emp table using ownum?

Answer:

 select * from emp where rownum<=14

minus

select * from emp where rownum<=12; 

 

7.    write a query to display second record from emp table using rownum alias name?

Answer:

 select * from (select rownum r, ename, job, sal from emp)where r=2;

 select * from (select rownum r, emp.* from emp) where r=2;

   or        

 select * from (select rownum r, e.* from emp e) where r=2;

 

8.    write a query to display second, third, fifth, seventh, eighth rows from emp table using rownum alias name?

     Answer:

     select * from (select rownum r, e.* from emp e) where r in(2,3,5,7,8);

 

9.    write a query to display records from 5 to 9 from emp table using rownum alias name?

Answer:

select * from(select rownum r, emp.* from emp)  where r between 5 and 9;

 

10. write a query to display first and last records from emp table using rownum alias name?            

Answer:

 select * from (select rownum r, emp.* from emp) where r=1 or r=(select count(*) from emp);

 

11. write a query to display even number of records from emp table using rownum alias name?

Answer:

 select * from (select rownum r, emp.* from emp) where mod(r,2)=0;   

     

12. write a query to display skip first 5 rows from emp table using rownum alias name?                        

Answer:

select * from (select rownum r, emp.* from emp) where r>5;