연습 1

1. 1999 년의 월별 신입 사원수를 출력하시오

SELECT MONTH(HIRE_DATE), COUNT(*) 

 FROM EMPLOYEES 

WHERE YEAR(HIRE_DATE)=1999 

GROUP BY MONTH(HIRE_DATE);

2. 월별 신입 사원수를 다음 그림과 같이 출력하시오(월-행을 열로)

SELECT SUM(CASE WHEN MM=1 THEN CNT ELSE 0 END) M1
        , SUM(CASE WHEN MM=2 THEN CNT ELSE 0 END) M2
        , SUM(CASE WHEN MM=3 THEN CNT ELSE 0 END) M3
        , SUM(CASE WHEN MM=4 THEN CNT ELSE 0 END) M4
        , SUM(CASE WHEN MM=5 THEN CNT ELSE 0 END) M5
        , SUM(CASE WHEN MM=6 THEN CNT ELSE 0 END) M6
        , SUM(CASE WHEN MM=7 THEN CNT ELSE 0 END) M7
        , SUM(CASE WHEN MM=8 THEN CNT ELSE 0 END) M8
        , SUM(CASE WHEN MM=9 THEN CNT ELSE 0 END) M9
        , SUM(CASE WHEN MM=10 THEN CNT ELSE 0 END) M10
        , SUM(CASE WHEN MM=11 THEN CNT ELSE 0 END) M11
        , SUM(CASE WHEN MM=12 THEN CNT ELSE 0 END) M12
  FROM (
            SELECT MONTH(HIRE_DATE) MM, COUNT(*) CNT
             FROM EMPLOYEES
            WHERE YEAR(HIRE_DATE)=1999
            GROUP BY MONTH(HIRE_DATE)
) DS;

3. 다음 그림과 같이 1999년 신입 사원수과 전년(1998) 대비 월별 신입 사원수를 출력하시오.

SELECT YR
        , SUM(CASE WHEN MM=1 THEN CNT ELSE 0 END) M1
        , SUM(CASE WHEN MM=2 THEN CNT ELSE 0 END) M2
        , SUM(CASE WHEN MM=3 THEN CNT ELSE 0 END) M3
        , SUM(CASE WHEN MM=4 THEN CNT ELSE 0 END) M4
        , SUM(CASE WHEN MM=5 THEN CNT ELSE 0 END) M5
        , SUM(CASE WHEN MM=6 THEN CNT ELSE 0 END) M6
        , SUM(CASE WHEN MM=7 THEN CNT ELSE 0 END) M7
        , SUM(CASE WHEN MM=8 THEN CNT ELSE 0 END) M8
        , SUM(CASE WHEN MM=9 THEN CNT ELSE 0 END) M9
        , SUM(CASE WHEN MM=10 THEN CNT ELSE 0 END) M10
        , SUM(CASE WHEN MM=11 THEN CNT ELSE 0 END) M11
        , SUM(CASE WHEN MM=12 THEN CNT ELSE 0 END) M12
  FROM (
        SELECT YEAR(HIRE_DATE) YR, MONTH(HIRE_DATE) MM, COUNT(*) CNT
          FROM EMPLOYEES
         WHERE YEAR(HIRE_DATE) IN (1998, 1999)
         GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
) DS
 GROUP BY YR;

4. 3번 문제를 다음 그림과 같이 출력하시오(년도를 열로 표시).

SELECT MM
        , SUM(CASE WHEN YR=1998 THEN CNT ELSE 0 END) BEFOREYEAR
        , SUM(CASE WHEN YR=1999 THEN CNT ELSE 0 END) THISYEAR
  FROM (
            SELECT YEAR(HIRE_DATE) YR, MONTH(HIRE_DATE) MM, COUNT(*) CNT
             FROM EMPLOYEES
            WHERE YEAR(HIRE_DATE) IN (1998, 1999)
            GROUP BY YEAR(HIRE_DATE), MONTH(HIRE_DATE)
) DS
 GROUP BY MM;

5. 급여를 다음과 같이 구간으로 만들어 직원수를 구하시오.

- 100,000달러 이상이면 1

- 80,000 달러 이상이면 2

- 65,000 달러 이상이면 3

- 45,000 달러 이상이면 4

- 45,000 달러 미만이면 5

SELECT CASE WHEN SALARY >= 100000 THEN 1
                 WHEN SALARY >= 80000 THEN 2
                 WHEN SALARY >= 65000 THEN 3
                 WHEN SALARY >= 45000 THEN 4
                   ELSE 5 END SALARYGRADE

   , COUNT(*) CNT

  FROM SALARIES WHERE TO_DATE='9999-01-01'

 GROUP BY CASE WHEN SALARY >= 100000 THEN 1
                      WHEN SALARY >= 80000 THEN 2
                      WHEN SALARY >= 65000 THEN 3
                      WHEN SALARY >= 45000 THEN 4
                      ELSE 5 END;

또는,

SELECT SALARYGRADE, COUNT(*)
 FROM (
            SELECT CASE WHEN SALARY >= 100000 THEN 1
                             WHEN SALARY >= 80000 THEN 2
                             WHEN SALARY >= 65000 THEN 3
                             WHEN SALARY >= 45000 THEN 4
                             ELSE 5 END AS SALARYGRADE, EMP_NO
            FROM SALARIES

WHERE TO_DATE='9999-01-01'

) DS
 GROUP BY SALARYGRADE;

6. 연령대별(10대, 20대, 30대~~) 직원수를 구하시오.

SELECT CASE WHEN BIRTH>=60 THEN 60
                 WHEN BIRTH>=50 THEN 50
                 WHEN BIRTH>=40 THEN 40
                 WHEN BIRTH>=30 THEN 30
                 ELSE 20 END AGE

  , SUM(CNT)

  FROM (       
            SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) BIRTH, COUNT(*) CNT
             FROM EMPLOYEES E, SALARIES S
            WHERE S.EMP_NO=E.EMP_NO
               AND TO_DATE='9999-01-01'
            GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW())
) DS
  GROUP BY CASE WHEN BIRTH>=60 THEN 60
                       WHEN BIRTH>=50 THEN 50
                      WHEN BIRTH>=40 THEN 40
                       WHEN BIRTH>=30 THEN 30
                       ELSE 20 END;

또는,

SELECT BIRTH DIV 10 * 10, SUM(CNT)
 FROM (       
            SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) BIRTH, COUNT(*) CNT
             FROM EMPLOYEES E, SALARIES S
            WHERE S.EMP_NO=E.EMP_NO
               AND TO_DATE='9999-01-01'
            GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW())
) DS
  GROUP BY BIRTH DIV 10;

또는,

SELECT TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) DIV 10 * 10 BIRTH, COUNT(*) CNT

 FROM EMPLOYEES E, SALARIES S 

WHERE S.EMP_NO=E.EMP_NO AND TO_DATE='9999-01-01'

GROUP BY TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW()) DIV 10