dokuwiki

This is an old revision of the document!

---

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