This is an old revision of the document!
/* create table dep_avg as select d.department_id, department_name, avg(salary) davg from employees e join departments d on d.department_id = e.department_id group by d.department_id, department_name */ -- select * from dep_avg select first_name, last_name, department_name, salary from employees e, departments d where e.department_id = d.department_id and salary > (select davg from dep_avg where department_id = d.department_id) --and department_name = 'IT' select first_name, last_name, department_name, salary from employees e, departments d where e.department_id = d.department_id and salary > (select avg(salary) from -- employees ei join departments di on di.department_id = ei.department_id employees ei, departments di where di.department_id = ei.department_id and -- di.department_id = d.department_id di.department_name = d.department_name ) select first_name, last_name, department_name, salary, round(davg), round(salary-davg) mehr from employees e, dep_avg d where e.department_id = d.department_id and salary > davg order by mehr desc select first_name, last_name, department_name, salary, round(davg), round(salary-davg) mehr from employees e, -- hier stellen wir uns das avg salary pro department zusammen (select d.department_id, department_name, avg(salary) davg from employees e join departments d on d.department_id = e.department_id group by d.department_id, department_name ) d -- ende avg sal per dep where e.department_id = d.department_id and salary > davg order by mehr desc