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?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework