<code>
/*
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
</code>

==== Finde den Angestellten mit dem höchsten Gehalt pro Department ====

<code>
SELECT first_name, last_name, salary, dep_outer.DEPARTMENT_NAME, dep_outer.department_id
FROM EMPLOYEES join DEPARTMENTS dep_outer
on  (dep_outer.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID)
where 
 salary = (select max(salary) from employees where department_id = dep_outer.department_id) 
order by dep_outer.department_id, salary 
</code>


==== den Angestellten mit der niedrigsten id und gleichem Nachnamen ====

<code>
select employee_id, 
       last_name
from   employees e
where  employee_id =
  (select min(employee_id) from employees where last_name = e.last_name)
order by employee_id;
</code>