8일차 # 3장, 4장 정리 문제

문제) 부서별 급여합을 아래와 같이 가로 형태로 구하기

    TOTAL        D10        D20        D30        D40

---------- ---------- ---------- ---------- ----------

    29025       8750      10875       9400          0

  해답)

select deptno, sal, sal, sal, sal, sal

from emp;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , sum(sal) as d10, sum(sal) as d20, sum(sal) as d30, sum(sal) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , decode(deptno, 10, sum(sal)) as d10

    , sum(sal) as d20, sum(sal) as d30, sum(sal) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , decode(deptno, 10, sum(sal)) as d10

    , decode(deptno, 20, sum(sal)) as d20

    , decode(deptno, 30, sum(sal)) as d30

    , decode(deptno, 40, sum(sal)) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select sum(sum(sal)) as total

    , max(decode(deptno, 10, sum(sal))) as d10

    , max(decode(deptno, 20, sum(sal))) as d20

    , max(decode(deptno, 30, sum(sal))) as d30

    , max(decode(deptno, 40, sum(sal))) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select sum(sum(sal)) as total

    , nvl(max(decode(deptno, 10, sum(sal))), 0) as d10

    , nvl(max(decode(deptno, 20, sum(sal))), 0) as d20

    , nvl(max(decode(deptno, 30, sum(sal))), 0) as d30

    , nvl(max(decode(deptno, 40, sum(sal))), 0) as d40

from emp

group by deptno;

  문제) 부서별 직무별 급여합 구하기 (아래와 같이 나타내시오.)

JOB            TOTAL        D10        D20        D30        D40

--------- ---------- ---------- ---------- ---------- ----------

ANALYST         6000          0       6000          0          0

CLERK           4150       1300       1900        950          0

MANAGER         8275       2450       2975       2850          0

PRESIDENT       5000       5000          0          0          0

SALESMAN        5600          0          0       5600          0

 cf.) select deptno, job, sum(sal)

from emp

group by deptno, job

  select job, nvl(decode(deptno, 10, sal), 0) as d10

            , nvl(decode(deptno, 20, sal), 0) as d20

            , nvl(decode(deptno, 30, sal), 0) as d30

            , nvl(decode(deptno, 40, sal), 0) as d40

  from emp;

↓ ↓ ↓ 

  select job, sum(nvl(decode(deptno, 10, sal), 0)) as d10

            , sum(nvl(decode(deptno, 20, sal), 0)) as d20

            , sum(nvl(decode(deptno, 30, sal), 0)) as d30

            , sum(nvl(decode(deptno, 40, sal), 0)) as d40

  from emp

  group by job;

  문제) 연도별 입사자수 구하기

    TOTAL      Y1983      Y1982      Y1981   PREVIOUS

---------- ---------- ---------- ---------- ----------

       14          1          2         10          1

  select to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY')

  from emp

  order by 1 desc;

  select to_char(hiredate, 'YYYY'), decode(to_char(hiredate, 'YYYY'), '1983', '1983') as Y1983

                                  , decode(to_char(hiredate, 'YYYY'), '1982', '1982') as Y1982

                                  , decode(to_char(hiredate, 'YYYY'), '1981', '1981') as Y1981

                                  , decode(to_char(hiredate, 'YYYY'), '1983', null, '1982', null, '1981', null, 'Previous') as Prev

  from emp

  order by 1 desc;

  select count(decode(to_char(hiredate, 'YYYY'), '1983', '1983')) as Y1983

       , count(decode(to_char(hiredate, 'YYYY'), '1982', '1982')) as Y1982

       , count(decode(to_char(hiredate, 'YYYY'), '1981', '1981')) as Y1981

       , count(decode(to_char(hiredate, 'YYYY'), '1983', null, '1982', null, '1981', null, 'Previous')) as Prev

  from emp

  order by 1 desc;

oj_SQL.txt