This shows you the differences between two versions of the page.
— |
kurs:joins_-_subsetvergleich [2017/10/24 15:51] (current) admin created |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | /* die zahl der employees pro department die MEHR als 6000 verdienen | ||
+ | und die Gehaltssumme ALLER Employees | ||
+ | */ | ||
+ | with salary_dep as ( | ||
+ | select d.department_id, | ||
+ | department_name, | ||
+ | salary | ||
+ | from employees e | ||
+ | join departments d | ||
+ | on e.department_id = d.department_id | ||
+ | ), | ||
+ | sum_dep_komplett as ( | ||
+ | select department_id, | ||
+ | department_name, | ||
+ | sum(salary) sum_sal, | ||
+ | count(*) count_emp | ||
+ | from salary_dep | ||
+ | group by department_id, department_name | ||
+ | ), | ||
+ | sum_dep_sal_gt_6000 as ( | ||
+ | select department_id, | ||
+ | department_name, | ||
+ | sum(salary) sum_sal, | ||
+ | count(*) count_emp | ||
+ | from salary_dep | ||
+ | where salary > 6000 | ||
+ | group by department_id, department_name | ||
+ | ) | ||
+ | select sdk.department_name, | ||
+ | sdk.sum_sal summe_komplett, | ||
+ | sd6000.sum_sal summe_mehr_als_6000, | ||
+ | sdk.count_emp, | ||
+ | sd6000.count_emp | ||
+ | from | ||
+ | sum_dep_komplett sdk join sum_dep_sal_gt_6000 sd6000 | ||
+ | on sdk.department_id = sd6000.department_id | ||
+ | </code> |