Use ROWNUM - Exercise

Contact Us or call 1-877-932-8228
Use ROWNUM - Exercise

Use ROWNUM

Duration: 10 to 15 minutes.
  1. Get a list of all department names in the departments tables
  2. Add the ROWNUM pseudo-column to the SELECT clause
  3. Add an ORDER BY department_name. Notice that the rownum is retreived prior to ordering the results.
  4. Add ROWNUM to the WHERE clause to limit the results to those with ROWNUMS less than five.

Solution:

Pseudo-Columns-and-Functions/Solutions/rownum_solutions.sql
--Initial list of department names

SELECT department_name 
FROM departments;

--Add ROWNUM to the SELECT clause

SELECT rownum, department_name 
FROM departments;
				  
--Add ORDER BY department_name
SELECT rownum, department_name 
FROM departments 
ORDER BY department_name;
				  
--Although we have not yet covered subqueries, note that you can force the ROWNUMS 
--to be assigned after the order by by using a subquery:

SELECT department_name, rownum 
FROM
    (
      SELECT department_name 
      FROM departments 
      ORDER BY department_name
    );
				  
-- Add ROWNUM in the WHERE clause to limit to those less than five.
SELECT * 
FROM departments 
WHERE ROWNUM < 5;
Next