6. 실습(4) - SubQuery

~~Edit~~

6. 실습(4) - SubQuery

샘플 데이터 베이스 개요
Subquery는 SQL 안에 SQL문을 사용하는 것으로 SELECT, FROM, WHERE 등 다양한 곳에서 사용된다.

1. 다음과 같이 부서별 직원 수를 Subquery로 구하시오.

SELECT D.DEPT_NAME
     , (SELECT COUNT(*) 
          FROM DEPT_EMP DE 
         WHERE DE.TO_DATE = '9999-01-01' AND DE.DEPT_NO=D.DEPT_NO)
  FROM DEPARTMENTS D;

SELECT D.DEPT_NAME, COUNT(*)
  FROM DEPARTMENTS D
 INNER JOIN DEPT_EMP DE ON DE.DEPT_NO=D.DEPT_NO
 WHERE DE.TO_DATE = '9999-01-01'
 GROUP BY D.DEPT_NAME;

NOTE : SELECT절에 Subquery를 사용하는 예이다.
Subquery를 사용한 SQL문은 부서를 가져오면서 각 부서(DEPT_EMP)에 배정된 직원의 인원수를 계산(COUNT)한다.
앞서 연습한 Join을 사용한 SQL문은 부서와 할당된 부서(DEPT_EMP)를 조인해서 부서명으로 그룹화하고 그 부서의 직원수를 계산(COUNT)한다.
둘다 적절한 SQL문은 아니고 설명을 위해 작성한 SQL문이다.

2. 각 부서에서 가장 오래 근무한 직원을 출력하시오

SELECT DEPT_NAME
    , (
        SELECT FIRST_NAME
          FROM DEPT_EMP DE
         INNER JOIN EMPLOYEES E ON E.EMP_NO = DE.EMP_NO
         WHERE DE.TO_DATE = '9999-01-01' AND DE.DEPT_NO=D.DEPT_NO
         ORDER BY FROM_DATE
         LIMIT 1
       )EMPLOYEE
  FROM DEPARTMENTS D;

NOTE : Subquery에서 조인을 사용했다.
먼저, 부서 리스트(DEPARTMENTS )를 조회한다.
부서를 가져오면서 각 부서(DEPT_EMP)에 배정받은 날짜(FROM_DATE)가 가장 빠른 직원의 이름(EMPLOYEES)을 조회한다.

연습: 가장 오래된 직원 10명이 근무했던 처음과 마지막 부서를 출력하시오.

SELECT EM.*
     , (
         SELECT DEPT_NAME 
           FROM DEPT_EMP DEE, DEPARTMENTS DE
          WHERE DEE.DEPT_NO = DE.DEPT_NO 
          ORDER BY FROM_DATE 
          LIMIT 1 
        ) FIRST_DEPT
     , (
         SELECT DEPT_NAME 
           FROM DEPT_EMP DEE, DEPARTMENTS DE
          WHERE DEE.DEPT_NO=DE.DEPT_NO 
          ORDER BY FROM_DATE DESC 
          LIMIT 1
        ) LAST_DEPT
  FROM EMPLOYEES EM
 ORDER BY HIRE_DATE
 LIMIT 10;

3. 각 부서에서 급여를 가장 많이 받는 직원 리스트를 구하시오.

SELECT DEPT_NAME
     , (
         SELECT FIRST_NAME
           FROM DEPT_EMP DE
          INNER JOIN EMPLOYEES E ON E.EMP_NO = DE.EMP_NO
          INNER JOIN SALARIES S ON S.EMP_NO = DE.EMP_NO
          WHERE DE.TO_DATE = '9999-01-01' AND DE.DEPT_NO=D.DEPT_NO
          ORDER BY SALARY DESC
          LIMIT 1
       ) EMPLOYEE
  FROM DEPARTMENTS D;

NOTE : 급여를 알기 위해 급여 테이블을 Subquery의 조인에 추가하였다.
먼저, 부서 리스트(DEPARTMENTS )를 조회한다.
부서를 가져오면서 해당 부서에 속한 직원들(DEPT_EMP)의 급여(SALARIES)를 확인한 뒤, 급여가 큰 순으로 정렬해서 가장 많이 받는 직원 1명(LIMIT)의 이름(EMPLOYEES)을 가져와 출력하게 된다.

4. 부서별로 직원 수를 구하시오.

-- 1번에서는 부서별 직원수를 조인과 SELECT절에 subquery를 사용하였다.
-- 이 경우 데이터의 양이 많을 경우 속도가 느려질 수 있는데 속도를 고려하여 적절한 SQL을 작성 해보자.

SELECT DEPT_NAME, CNT
  FROM DEPARTMENTS D
 INNER JOIN (
             SELECT DEPT_NO, COUNT(*) CNT
               FROM DEPT_EMP
              WHERE TO_DATE='9999-01-01'
              GROUP BY DEPT_NO
            ) DS ON D.DEPT_NO=DS.DEPT_NO
 ORDER BY D.DEPT_NO

NOTE : 1번 문제에서 두 가지 방법을 사용하였다.

10개의 부서를 추출하고, 각 부서의 인원수를 계산하는 것

부서와 인원 테이블을 조인해서 데이터를 구성하고 부서명으로 그룹화 한 뒤 인원을 계산하는 것

조인을 사용하는 것이 추천되는 방식인데 문제는 문자열로 그룹화 하는 것이다.
가급적 정형화된 코드로 그룹화 하는 것이 좋다.
더욱이 조인을 먼저 하기 때문에 많은 데이터 조인(부서수 10 & 인원수 240,124)이 발생한다.

NOTE : 개인적으로 가장 선호하는 방식은 위에 작성한 코드처럼 부서코드로 그룹화해서 인원을 계산하고, 부서명을 찾기 위해 조인하는 것이다.
부서수가 10개이므로 10개의 데이터(10 & 10)로 조인을 하기 때문에 더 빠르게 처리된다.
SQL 속도 처리(튜닝)는 처리하는 데이터 개수만 잘 세어도 거의 문제가 발생하지 않는다.
따라서 항상 몇 건의 데이터를 조인하고 그룹화 하는지 파악해야 한다.

5. 전체 평균보다 많이 받는 직원 수를 계산하시오.

SELECT COUNT(*) -- 107706
  FROM SALARIES S 
 WHERE S.TO_DATE = '9999-01-01'
   AND SALARY >= (SELECT AVG(SALARY) 
                    FROM SALARIES 
                   WHERE TO_DATE = '9999-01-01');

NOTE : 전체 평균을 Subquery로 구하여 조건절(WHERE)에서 사용하였다.
먼저, 현재 근무 중인 직원들의 급여 평균(AVG(SALARY))를 계산하고 그 금액보다 큰(>=) 직원들 인원을 계산(COUNT)한다.

6. 다음과 같이 퇴직한 직원수를 구하시오

SELECT COUNT(*)
  FROM EMPLOYEES E
 WHERE NOT EXISTS(SELECT 1 
                    FROM DEPT_EMP DE 
                    WHERE DE.TO_DATE = '9999-01-01' 
                      AND E.EMP_NO=DE.EMP_NO);

SELECT COUNT(*)
  FROM EMPLOYEES E
 WHERE EMP_NO NOT IN (SELECT DISTINCT EMP_NO 
                        FROM DEPT_EMP DE 
                       WHERE DE.TO_DATE = '9999-01-01');

NOTE : 이 데이터에서는 퇴직 필드가 없기 때문에 퇴직의 개념부터 먼저 정의해야 한다.
이 데이터에서 근무중인 직원은 실습(2) - GROUP 에서 정의한 데로 급여나 현재 근무 부서, 업무의 종료일자가 ‘9999-01-01’인 것이다.
따라서 퇴사는 종료일자가 ‘9999-01-01’아닌 것이 될 것이다.
즉, 직원중에서 급여, 근무 부서, 업무 테이블에 종료일자가 ‘9999-01-01’인 데이터가 없는(NOT EXISTS, NOT IN ) 직원이 퇴사자가 된다.

NOTE : EXISTS와 IN 둘다 많이 사용되는 명령어로 DBMS에 따라 다르지만 대부분 Subquery의 양이 많으면 EXISTS를 사용하라고 권하고 있다.
개인적으로 속도가 빨라서 EXISTS를 자주 사용한다.

연습 : 다음 SQL문을 실행하여 정확성을 검증해 보자.

-- 전체 직원수(300,024) = 근무중인 직원수(240,124) + 퇴사한 직원수(59,900)

SELECT COUNT(*)
  FROM EMPLOYEES E;

SELECT COUNT(DISTINCT EMP_NO)
  FROM DEPT_EMP DE
 WHERE DE.TO_DATE = '9999-01-01';

7. 연도별 퇴직 직원수를 구하여라

SELECT YEAR(TO_DATE), COUNT(*)
  FROM (
         SELECT DE.EMP_NO, MAX(TO_DATE) TO_DATE
           FROM DEPT_EMP DE
          WHERE NOT EXISTS(SELECT 1 
                             FROM DEPT_EMP DE1 
                            WHERE DE1.TO_DATE = '9999-01-01' 
                              AND DE1.EMP_NO=DE.EMP_NO)
          GROUP BY DE.EMP_NO
        ) DS
 GROUP BY YEAR(TO_DATE)

NOTE : 앞 5번 SQL문을 Subquery로 하여 그룹화 하였다.
퇴사 날짜를 구하기 위해 직원 테이블이 아닌 할당된 부서테이블(DEPT_EMP)을 사용하였다.
퇴사 날짜는 부서에서 근무한 마지막 날짜인 종료날짜(TO_DATE) 중에서 가장 큰값(Max)이 퇴사 날짜가 될 것이다.
각 개인별(GROUP BY DE.EMP_NO) 퇴사 날짜를 구해서 퇴사날짜를 년도별로 그룹화 하면 연도별 퇴사 직원수를 구할 수 있다.
이 경우는 다음과 같은 SQL 문이 더 효율적일 수 있다.

생각해보기 : 각 부서에서 급여를 가장 많이 받는 직원이름과 급여를 출력하시오.

출처 : SW 개발이 좋은 사람

저작자표시

'Programming > SQL' 카테고리의 다른 글

8. 연습(2) (0)	2017.03.11
7. 연습(1) (0)	2017.03.11
5. 실습(3) - Join (0)	2017.02.28
4. 실습(2) - Group (0)	2017.02.25
3. 실습(1) - BASIC (0)	2017.02.25