PL/SQL Cursor Exercises: Show the uses of corelated subquery in an explicit cursor

PL/SQL Cursor: Exercise-50 with Solution

Write a block in PL/SQL to show the uses of corelated subquery in an explicit cursor.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
  CURSOR emp_cur IS
    SELECT department_id, first_name,last_name, salary
    FROM employees e
    WHERE salary > ( SELECT avg(salary)
                     FROM employees
                     WHERE e.department_id = department_id
                   )
    ORDER BY department_id, last_name;
BEGIN
  FOR each_emp IN emp_cur
  LOOP
    DBMS_OUTPUT.PUT_LINE(rpad(each_emp.last_name,10)||' draws more than the average salary of department '||each_emp.department_id);
  END LOOP;
END;
/

Sample Output:

Hartstein  draws more than the average salary of department 20
Raphaely   draws more than the average salary of department 30
Bell       draws more than the average salary of department 50
Bull       draws more than the average salary of department 50
Chung      draws more than the average salary of department 50
Dilly      draws more than the average salary of department 50
Everett    draws more than the average salary of department 50
Fripp      draws more than the average salary of department 50
Kaufling   draws more than the average salary of department 50
Ladwig     draws more than the average salary of department 50
Mourgos    draws more than the average salary of department 50
Rajs       draws more than the average salary of department 50
Sarchand   draws more than the average salary of department 50
Vollman    draws more than the average salary of department 50
Weiss      draws more than the average salary of department 50
Ernst      draws more than the average salary of department 60
Hunold     draws more than the average salary of department 60
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to show the uses of subquery in FROM clause of parent query in an explicit cursor.