This shows you the differences between two versions of the page.
— |
kurs:select_mit_group_by_subquery [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | select department_id, round(avg(SALARY), 2) sal, round(sum(SALARY), 2), count(*) as c from employees | ||
+ | group by department_id) | ||
+ | having avg(SALARY)>8000 -- NACHTTEIL!!! in der having clause kann man nicht auf Synonyme zugreifen | ||
+ | order by sal; | ||
+ | select * from ( | ||
+ | select department_id, round(avg(SALARY), 2) sal, round(sum(SALARY), 2), count(*) as c from employees | ||
+ | group by department_id) | ||
+ | where sal>8000 | ||
+ | order by sal; | ||
+ | |||
+ | |||
+ | -- alles auf einmal | ||
+ | |||
+ | select first_name, last_name, salary, davgs.department_id, avgs from | ||
+ | employees e, (select department_id, avg(salary) avgs from employees | ||
+ | group by department_id) davgs | ||
+ | where davgs.department_id = e.department_id | ||
+ | and salary > avgs | ||
+ | |||
+ | -- zuerst table anlegen | ||
+ | create table avg_salaray as select department_id, avg(salary) avgs from employees | ||
+ | group by department_id | ||
+ | |||
+ | |||
+ | -- nun ganz "normal" joinen | ||
+ | |||
+ | select first_name, last_name, salary, e.department_id, avgs from | ||
+ | employees e, avg_salaray | ||
+ | where salary > avgs | ||
+ | and avg_salaray.department_id = e.department_id | ||
+ | </code> | ||
+ | |||
+ | zusatzaufgabe diejenige mit der größten differenz per Department herausfinden! |