Paginating the records in SQL

Paginating is nothing but controlling the no. of records in the result set 
eg: fetch first 5 records, fetch 10 to 20 records, etc.,

Employees -Table

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        100 Steven                    24000 
        101 Neena                     17000 
        102 Lex                       17000 
        103 Alexander                  9000 
        104 Bruce                      6000 
        105 David                      4800 
        106 Valli                      4800 
        107 Diana                      4200 

1) Below query fetches first 5 records of the table order by highest salary.
('fetch next n rows' clause is supported from Oracle 12c onwards).

select e.employee_id, e.first_name, e.last_name, e.salary 
      from employees e  
      order by e.salary desc
      fetch next 5 rows only;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24000 
        101 Neena                Kochhar                        17000 
        102 Lex                  De Haan                        17000 
        103 Alexander            Hunold                          9000 
        104 Bruce                Ernst                           6000 

 2) In another example, we fetch next 5 records starting from the 5th record. Since the table has only 8 records, it returns records from 6 to 8.

 offset 5 rows - skip first 5 rows

select e.employee_id, e.first_name, e.last_name, e.salary 
      from employees e  
      order by e.salary desc offset 5 rows

      fetch next 5 rows only;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        105 Steven               King                           4800 
        106 Neena                Kochhar                        4800 
        107 Lex                  De Haan                        4200     

For Oracle older versions:

3) ROW_NUMBER() is used to give the row number for each row order by salary and this row number is used for paginating the records.

select * from (select  e.employee_id, e.first_name, e.last_name, e.salary, 
                       row_number() over (order by salary desc) rownumber 

                       from employees e)
               where rownumber <=10 offset 5 row ;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY         RN
----------- -------------------- ------------------------- ---------- ----------
        105 David                Austin                          4800          6 
        106 Valli                Pataballa                       4800          7 
        107 Diana                Lorentz                         4200          8