Using a Correlated Subquery
DECLARE
-- For each department, find the average salary. Then find all the employees in
-- that department making more than that average salary.
CURSOR c1 IS
SELECT department_id,
last_name,
salary
FROM employees t
WHERE salary > (SELECT Avg(salary)
FROM employees
WHERE t.department_id = department_id)
ORDER BY department_id;
BEGIN
FOR person IN c1 LOOP
dbms_output.Put_line('Making above-average salary = '
||person.last_name);
END LOOP;
END;
/
Output :-
Making above-average salary = Hartstein
Making above-average salary = Raphaely
Making above-average salary = Weiss
Making above-average salary = Fripp
Making above-average salary = Kaufling
Making above-average salary = Vollman
Making above-average salary = Mourgos
Making above-average salary = Ladwig
Making above-average salary = Rajs
Making above-average salary = Sarchand
Making above-average salary = Bull
Making above-average salary = Chung
Making above-average salary = Dilly
Making above-average salary = Bell
Making above-average salary = Everett
Making above-average salary = Hunold
Making above-average salary = Ernst
Making above-average salary = Russell
Making above-average salary = Partners
Making above-average salary = Errazuriz
Making above-average salary = Cambrault
Making above-average salary = Zlotkey
Making above-average salary = Tucker
Making above-average salary = Bernstein
Making above-average salary = Hall
Making above-average salary = King
Making above-average salary = Sully
Making above-average salary = McEwen
Making above-average salary = Vishney
Making above-average salary = Greene
Making above-average salary = Ozer
Making above-average salary = Bloom
Making above-average salary = Fox
Making above-average salary = Abel
Making above-average salary = King
Making above-average salary = Greenberg
Making above-average salary = Faviet
Making above-average salary = Higgins
PL/SQL procedure successfully completed.
Posts
0 comments:
Post a Comment