﻿ PL/SQL Cursor: Show the uses of corelated subquery in an explicit cursor - w3resource

# 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:

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

What is the difficulty level of this exercise?

﻿

Inviting useful, relevant, well-written and unique guest posts