Live ORACLE

DECLARE

CURSOR c1 IS

  -- main query returns only rows where the salary is greater than the average

  SELECT employee_id,

         last_name

  FROM   employees

  WHERE  salary > (SELECT Avg(salary)

                   FROM   employees);

 CURSOR c2 IS

   -- subquery returns all the rows in descending order of salary

   -- main query returns just the top 10 highest-paid employees

   SELECT *

   FROM   (SELECT   last_name,

                    salary

           FROM     employees

           ORDER BY salary DESC,

                    last_name)

   WHERE  ROWNUM < 11;

BEGIN

FOR person IN c1 LOOP

  dbms_output.Put_line('Above-average salary: '

                       ||person.last_name);

END LOOP;



FOR person IN c2 LOOP

  dbms_output.Put_line('Highest paid: '

                       ||person.last_name

                       ||' $'

                       ||person.salary);

END LOOP;

-- subquery identifies a set of rows to use with CREATE TABLE or INSERT

END;

/

Above-average salary: Hartstein
Above-average salary: Mavris
Above-average salary: Baer
Above-average salary: Higgins
Above-average salary: Gietz
Above-average salary: King
Above-average salary: Kochhar
Above-average salary: De Haan
Above-average salary: Hunold
Above-average salary: Greenberg
Above-average salary: Faviet
Above-average salary: Chen
Above-average salary: Sciarra
Above-average salary: Urman
Above-average salary: Popp
Above-average salary: Raphaely
Above-average salary: Weiss
Above-average salary: Fripp
Above-average salary: Kaufling
Above-average salary: Vollman
Above-average salary: Russell
Above-average salary: Partners
Above-average salary: Errazuriz
Above-average salary: Cambrault
Above-average salary: Zlotkey
Above-average salary: Tucker
Above-average salary: Bernstein
Above-average salary: Hall
Above-average salary: Olsen
Above-average salary: Cambrault
Above-average salary: Tuvault
Above-average salary: King
Above-average salary: Sully
Above-average salary: McEwen
Above-average salary: Smith
Above-average salary: Doran
Above-average salary: Sewall
Above-average salary: Vishney
Above-average salary: Greene
Above-average salary: Marvins
Above-average salary: Lee
Above-average salary: Ozer
Above-average salary: Bloom
Above-average salary: Fox
Above-average salary: Smith
Above-average salary: Bates
Above-average salary: Abel
Above-average salary: Hutton
Above-average salary: Taylor
Above-average salary: Livingston
Above-average salary: Grant
Highest paid: King $24000
Highest paid: De Haan $17000
Highest paid: Kochhar $17000
Highest paid: Russell $14000
Highest paid: Partners $13500
Highest paid: Hartstein $13000
Highest paid: Errazuriz $12000
Highest paid: Greenberg $12000
Highest paid: Higgins $12000
Highest paid: Ozer $11500

PL/SQL procedure successfully completed.

Using a Subquery in a Cursor