Differences

This shows you the differences between two versions of the page.

--- kurs:correlated_subquery [2014/09/10 21:22]
127.0.0.1 external edit
+++ kurs:correlated_subquery [2022/03/15 10:39] (current)
admin
@@ Line 1: / Line 1: @@
+<code>
+with cte_multiple_last_names as (
+select last_name,
+       count(*) c
+  from hr.employees
+  group by last_name
+  having count(*) > 1
+  order by c desc
+)
+select employee_id,
+   first_name,
+   last_name
+from hr.employees where last_name
+  in (select last_name from cte_multiple_last_names)
+select
+  emp.first_name,
+  emp.last_name
+from
+  hr.employees emp
+where
+  (select count(last_name) from hr.employees emp_sub where emp_sub.last_name = emp.last_name) > 1
 <code>
 select first_name, last_name, e.employee_id, c from employees e,
@@ Line 45: / Line 69: @@
         and  outer.fhz_name = inner.fhz_name
        )) > 1
+</code>
+<code>
+with emp_per_country as (
+SELECT dep.department_id,
+    department_name,
+    cou.country_id,
+    country_name,
+    COUNT(employee_id) c
+FROM hr.employees emp
+JOIN hr.departments dep
+ON emp.department_id = dep.department_id
+JOIN hr.locations loc
+ON loc.location_id = dep.location_id
+JOIN hr.countries cou
+ON cou.country_id = loc.country_id
+GROUP BY dep.department_id, department_name, cou.country_id, country_name
+ORDER BY COUNT(employee_id) DESC
+)
+select country_name, department_name, c from emp_per_country o
+where c = (select max(c) from emp_per_country where country_id = o.country_id)
 </code>

dokuwiki