
select 
    department_name,
    count(*) c
from employees e join departments d
on e.department_id = d.department_id
group by 
    department_name
having count(*) >= 5    
order by c desc


with ym as (
select employee_id,
    --   extract(year from hire_date) y,
       extract(month from hire_date) m
       from employees
) 
select m, count(employee_id)
  from ym 
       group by m
       order by m


with gehaltklassen as (
select salary, case when salary < 7000 then 0
            when salary < 12000 then 1
            else 2 end gehaltklasse
from employees
)
select gehaltklasse, dg, count(dg) from (
   select gehaltklasse, decode(gehaltklasse,
          0, 'wenig',
          1, 'mittel',
          2, 'viel') dg
     from gehaltklassen )
group by gehaltklasse, dg 
order by gehaltklasse


select decode(cc, 
              0, 'alt',
              1, 'mittel',
              2, 'jung'),
       count(cc) from (
select 
(case 
   when jahr > 15 then 0
   when jahr > 10 then 1
   else 2
   end) cc
 from (
select first_name, 
       last_name, 
       trunc(MONTHS_BETWEEN(SYSDATE, hire_date)/12) jahr
       from employees       
)
) group by cc
order by cc


SELECT COUNTRIES.COUNTRY_NAME, count(DEPARTMENTS.DEPARTMENT_ID)
FROM DEPARTMENTS , LOCATIONS , COUNTRIES 
WHERE ( (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID) AND
(COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID) )
group by COUNTRY_NAME