====== Zeige das Departement mit den meisten Angestellten pro Land =====

<code>
with gpl as 
(
  SELECT DEPARTMENTS.DEPARTMENT_NAME, COUNTRIES.COUNTRY_NAME, (count(EMPLOYEES.EMPLOYEE_ID)) c
      FROM DEPARTMENTS , LOCATIONS , COUNTRIES , EMPLOYEES 
  WHERE ( (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID) AND
          (COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID) AND
          (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) ) 
   group by DEPARTMENTS.DEPARTMENT_NAME, COUNTRIES.COUNTRY_NAME
)
select department_name dn, country_name, c from gpl
where (country_name, c) in (select country_name, max(c) from gpl where gpl.country_name = country_name
group by country_name)
</code>

<code>
select d.department_name, l.city,  count(e.employee_id) as Anzahl, c.country_name
from departments d, employees e, LOCATIONS l, countries c
where
d.DEPARTMENT_ID = e.DEPARTMENT_ID
and
l.LOCATION_ID = d.LOCATION_ID
and
c.country_id = l.country_id
group by
d.department_name, l.city, c.country_name, e.department_id,  c.country_id,d.department_id, l.location_id
having count(e.employee_id) = 
(
    select max(count(e1.employee_id))
        from employees e1, departments d1, countries c1, locations l1
        where 
        c1.country_id = c.country_id
        and 
        e1.department_id = d1.department_id
        and
        d1.location_id = l1.location_id
        and
        l1.country_id = c1.country_id
        group by  e1.department_id, c1.country_id   
)
</code>