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


'Database > SQL 연습' 카테고리의 다른 글

실습 2 - GROUP  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02
연습 2  (0) 2016.04.02
연습 3  (0) 2016.04.02

1. 부서별 부서장 정보를 출력하시오.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME, SALARY

  FROM DEPT_MANAGER DM

  INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DM.DEPT_NO

  INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DM.EMP_NO

  INNER JOIN SALARIES SAL ON SAL.EMP_NO=DM.EMP_NO

WHERE DM.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

ORDER BY DEPT_NAME;


2. 부서별 정보(부서장, 부서별 급여 평균)을 출력하시오.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME, AVG_SALARY

  FROM DEPT_MANAGER DM

  INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DM.DEPT_NO

  INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DM.EMP_NO

  INNER JOIN (
            SELECT DEPT_NO, AVG(SALARY) AVG_SALARY

              FROM DEPT_EMP DE

              INNER JOIN SALARIES SAL ON SAL.EMP_NO=DE.EMP_NO

             WHERE DE.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

             GROUP BY DEPT_NO

) DS ON DS.DEPT_NO=DM.DEPT_NO

 WHERE DM.TO_DATE='9999-01-01' 

 ORDER BY DEPT_NAME


3. 부서별 직원 리스트를 출력하되 부서장이면 표시를 하고,

   각 부서에서 가장 먼저 나오게 출력하시오.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME, IF(DM.EMP_NO IS NULL, NULL, 'MANAGER') POSITION

  FROM DEPT_EMP DE

  INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DE.DEPT_NO

  INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DE.EMP_NO

    LEFT OUTER JOIN DEPT_MANAGER DM ON DM.EMP_NO=DE.EMP_NO AND DM.TO_DATE='9999-01-01'

 WHERE DE.TO_DATE='9999-01-01' 

 ORDER BY DEPT_NAME, DM.EMP_NO DESC, FIRST_NAME, LAST_NAME


4. 부서별 직원 리스트와 급여를 출력하되,

   부서장이면 표시를 하고 직원들의 급여 평균을 표시하시오.

  이 문제는 실적 계산에 많이 사용하는 SQL문으로

  급여가 아닌 실적으로 하는 것이 적절하지만 데이터가 없어서

  급여를 실적처럼 사용해서 문제를 풀어본다.

  즉, 개인은 개인의 실적, 팀장은 팀원 실적의 평균으로 계산.

SELECT DEPT_NAME, FIRST_NAME, LAST_NAME

        , IF(DM.EMP_NO IS NULL, SAL.SALARY, DS.AVG_SALARY) AVG_SALARY

        , IF(DM.EMP_NO IS NULL, NULL, 'MANAGER') POSITION

  FROM DEPT_EMP DE

INNER JOIN DEPARTMENTS DEP ON DEP.DEPT_NO=DE.DEPT_NO

INNER JOIN EMPLOYEES EMP ON EMP.EMP_NO=DE.EMP_NO

INNER JOIN SALARIES SAL ON SAL.EMP_NO=DE.EMP_NO

   LEFT OUTER JOIN DEPT_MANAGER DM ON DM.EMP_NO=DE.EMP_NO AND DM.TO_DATE='9999-01-01'

   LEFT OUTER JOIN (

            SELECT DEPT_NO, AVG(SALARY) AVG_SALARY

              FROM DEPT_EMP DE

             INNER JOIN SALARIES SAL ON SAL.EMP_NO=DE.EMP_NO

             WHERE DE.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

             GROUP BY DEPT_NO

) DS ON DS.DEPT_NO=DE.DEPT_NO

WHERE DE.TO_DATE='9999-01-01' AND SAL.TO_DATE='9999-01-01'

ORDER BY DEPT_NAME, DM.EMP_NO DESC, FIRST_NAME, LAST_NAME



'Database > SQL 연습' 카테고리의 다른 글

실습 2 - GROUP  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02
연습 1  (3) 2016.04.02
연습 3  (0) 2016.04.02

앞서 부서별로 직원 수를 구하는 문제를

3가지 방법으로 살펴 보았다.

조인(Join) 4번, 하위쿼리(SubQuery)에서 1번과 4번 문제로

정리하는 의미에서

부서 이동 문제를 이용하여 다음과 같이 정리하였다.


실습 2 - GROUP의 6번 문제에서

[다음 그림과 같이 직원들의 부서 이동 과정을 출력하시오] 라는 문제를 풀었다.

SELECT DE.EMP_NO, GROUP_CONCAT(DE.DEPT_NO SEPARATOR ' > ' ) PATH
  FROM DEPT_EMP DE
GROUP BY DE.EMP_NO
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;

이 문제는 좀 더 많은 설명과 구현이 필요한 SQL문이다.

먼저, 다음 그림과 같이 실행 결과에서 직원의 이름과 부서 명이 필요하다.

(아래의 설명을 읽는 것도 좋겠지만

위와 같이 출력되게 하는 SQL문을 직접 작성해 보면 실력향상에 도움이 될 것이다.)

SELECT DE.EMP_NO, E.FIRST_NAME, E.LAST_NAME, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ' ) PATH
  FROM DEPT_EMP DE
 INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO
 INNER JOIN EMPLOYEES E ON E.EMP_NO=DE.EMP_NO
GROUP BY DE.EMP_NO, E.FIRST_NAME, E.LAST_NAME
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;

직원의 이름(FIRST_NAME, LAST_NAME)과

부서 명(DEPT_NAME)이 출력되기 위해서는

직원 테이블(EMPLOYEES)과 부서 테이블(DEPARTMENTS)을 추가해 주면 된다.

그리고, 직원 정보로 GROUP화하고

부서 명(DEPT_NAME)을 묶으면 된다.


여기서 생각해야 할 것이 GROUP으로 직원 정보를 사용하는 것이다.

직원명외에 생일을 추가한다고 할 경우

GROUP에 생일 필드를 추가해야 한다.

GROUP에 가변길이 문자열을 많은 추가하는 것은

SQL 성능(속도)상 추천되지 않는다.

다음과 같이 하위쿼리(SubQuery)를 이용하는 것이 바람직하다.

SELECT DS.EMP_NO, E.FIRST_NAME, E.LAST_NAME, PATH
  FROM (
        SELECT DE.EMP_NO, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ' ) PATH, COUNT(*) CNT
          FROM DEPT_EMP DE
          INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO
        GROUP BY DE.EMP_NO
        HAVING COUNT(*)>1
) DS
 INNER JOIN EMPLOYEES E ON E.EMP_NO=DS.EMP_NO
 ORDER BY CNT DESC;

부서는 조인을 이용하지만

개인정보는 하위쿼리를 사용하는 것이 좋다.

GROUP에 사용된 다는 것은 인덱스가 필요하다는 의미로

좀 더 복잡한 처리가 필요한 것도 있지만,

단순하게 데이터 개수의 의미도 있다.


조인을 사용할 경우

직원 수가 약 30만(300,024)이고,

부서 할당 정보가 약 33만 (331,603)이니

30만 * 33만의 처리가 발생한다.


하위 쿼리로 실행하면,

다음 SQL에서 알수 있듯이

처리 개수가 3만(31,579)개로 줄어든다.

부서 이동이 발생한 직원들만 처리하기 때문이며,

당연이 조인시 발생하는 처리 수도 줄어 든다.

SELECT COUNT(*)
  FROM (
        SELECT DE.EMP_NO, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ' ) PATH, COUNT(*) CNT
          FROM DEPT_EMP DE
          INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO
         GROUP BY DE.EMP_NO
         HAVING COUNT(*)>1
) DS


제대로 완성 시키려면,

현재 근무하는 직원만 출력해야 한다.

현재 근무하는 직원은

부서 할당(DEPT_EMP) 테이블에

근무 종료(TO_DATE)가 9999-01-01인 직원들이므로

다음과 같이 두개의 부서 할당 테이블이 필요하다.

하나는 현재 근무 중인 직원을 가져오는 테이블(DEPT_EMP DE1),

나머지는 부서 이동 정보를 가져오는 테이블(DEPT_EMP DE2)이다.

SELECT DS.EMP_NO, E.FIRST_NAME, E.LAST_NAME, PATH
  FROM (
        SELECT DE1.EMP_NO, GROUP_CONCAT(D.DEPT_NAME SEPARATOR ' > ') PATH, COUNT(*) CNT
          FROM DEPT_EMP DE1
         INNER JOIN DEPT_EMP DE2 ON DE2.EMP_NO=DE1.EMP_NO
         INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE2.DEPT_NO
         WHERE DE1.TO_DATE='9999-01-01'
         GROUP BY DE1.EMP_NO
        HAVING COUNT(*)>1
) DS
 INNER JOIN EMPLOYEES E ON E.EMP_NO=DS.EMP_NO
 ORDER BY CNT DESC;

부서 테이블과의 조인은(D.DEPT_NO=DE2.DEPT_NO)

부서 이동 정보를 가져오는 테이블(DEPT_EMP DE2)이다.



'Database > SQL 연습' 카테고리의 다른 글

실습 2 - GROUP  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02
연습 1  (3) 2016.04.02
연습 2  (0) 2016.04.02

+ Recent posts

티스토리툴바