실습 2 - GROUP

기초 문제에서는 고려하지 않고 넘어갔지만

모든 직원이 현재 근무 중인 것은 아니다.

"2. 샘플 데이터 베이스 개요"를 보면 퇴사 관련 필드가 없는 것으로 나타나 있다.

따라서 추측으로 퇴사 여부를 파악했고

종료일자가 있는 테이블의 데이터를 살펴보면 '9999-01-01'인 데이터가 있다. 

인터넷으로 본 샘플 데이터베이스에 대한 설명을 못 찾았는데

의미상 현재 근무 중인 직원은 급여나 현재 근무 부서, 업무의 종료일자가 없을 것이다. 

종료일자가 없다는 의미를 '9999-01-01'로 사용한 것 같다.

다음 SQL문을 실행해 보면 데이터 개수가 일치한다.

SELECT COUNT(*) FROM SALARIES WHERE TO_DATE='9999-01-01';

SELECT COUNT(*) FROM TITLES WHERE TO_DATE='9999-01-01';

SELECT COUNT(*) FROM DEPT_EMP WHERE TO_DATE='9999-01-01';

이 개념을 가지고 다음 문제들을 풀어갈 것이다.

지금까지의 예는 하나의 테이블을 그룹으로 집계 함수(COUNT, AVG, SUM, MIN, MAX)가 사용되었다.

이제 부터는 특정한 그룹을 형성하고 사용하는 방법에 대하여 연습한다.

1. 업무별 직원수를 구하시오

SELECT TITLE, COUNT(*)

  FROM TITLES

WHERE TO_DATE='9999-01-01'

 GROUP BY TITLE

 ORDER BY TITLE;

현재 근무하는 직원(TO_DATE='9999-01-01')에 대하여 업무별(TITLE)별로 그룹화하여 개수를 계산하였다.

연습: 직원들의 평균 급여를 구하시오.

SELECT EMP_NO, AVG(SALARY)

  FROM SALARIES

 WHERE TO_DATE='9999-01-01'

 GROUP BY EMP_NO

2. 남여 직원수를 구하시오

SELECT GENDER, COUNT(*)

   FROM EMPLOYEES  

 GROUP BY GENDER;

성별(GENDER)이 있는 직원 테이블(EMPLOYEES)에 종료일자가 없다.

따라서 다른 테이블과 조인을 필요로 하는데 아직 연습하지 않아서 위와 같이 SQL문을 만들어야 한다.

연습: 남여 직원수를 구하되 M은 '남', F는 '여'로 표시하여 출력하시오.

SELECT  IF (GENDER='M', '남','여') GENDER, COUNT(*)

   FROM EMPLOYEES

 GROUP BY GENDER;

다음과 같이 IF 문을 성별(GENDER) 필드에 적용하면 된다.

IF (조건,참일때,거짓일때)

IF문은 DBMS에 따라 다르게 사용되기 때문에 표준으로 사용되는 CASE문을 사용하는 것이 좋을 수도 있다.

3. 부서별 현재 인원수를 구하시오

SELECT DEPT_NO, COUNT(*)

  FROM DEPT_EMP

WHERE TO_DATE = '9999-01-01'

 GROUP BY DEPT_NO;

직원들의 부서를 나타내는 테이블(DEPT_EMP)을 대상으로 

부서 필드(DEPT_NO)를 그룹으로 지정하여 개수를 세면 된다.

다만, 부서명은 부서 테이블(DEPARTMENTS)에 있고 조인을 아직 연습하지 않았다.

연습: 부서별 현재 인원수를 인원수가 많은 부서부터 출력하시오

SELECT DEPT_NO, COUNT(*) CNT

  FROM DEPT_EMP

WHERE TO_DATE = '9999-01-01'

GROUP BY DEPT_NO

ORDER BY CNT DESC;

COUNT(*)를 CNT로 명명하고 ORDER BY 에서 CNT를 내림차순(DESC)으로 지정해 준다.

연습: 부서 이동이 많은 직원순으로 리스트를 출력하시오 (퇴직자 포함)

SELECT EMP_NO, COUNT(*) CNT

   FROM DEPT_EMP

  GROUP BY EMP_NO

 HAVING COUNT(*)>1

 ORDER BY CNT DESC

4. 부서별 현재 인원수가 15,000명 이상인 부서를 구하시오.

SELECT DEPT_NO, COUNT(*) CNT

  FROM DEPT_EMP

WHERE TO_DATE = '9999-01-01'

 GROUP BY DEPT_NO

HAVING COUNT(*) >= 15000

 ORDER BY CNT DESC;

그룹에 대한 조건은 WHERE이 아닌 HAVING 이라는 것이 문제의 핵심이다.

그리고, ORDER는 SELECT의 필드로 지정했지만

HAVING 은 조건으로 SELECT의 필드가 아닌 COUNT(*)를 사용한다.

연습: 급여가 70,000이상인 직원 리스트를 구하시오.

5. 부서별 현재 인원수가 가장 많은 상위 5개 부서를 구하시오

SELECT DEPT_NO, COUNT(*) CNT

  FROM DEPT_EMP

WHERE TO_DATE = '9999-01-01'

GROUP BY DEPT_NO

ORDER BY CNT DESC

 LIMIT 5;

선택된 데이터의 개수를 제한하는 LIMIT문을 사용하는 예제이다.

선택된 데이터를 ORDER BY로 정렬을 하고 LIMIT로 원하는 개수만큼 추출한다.

6. 다음 그림과 같이 직원들의 부서 이동 과정을 출력하시오

SELECT EMP_NO, GROUP_CONCAT(DEPT_NO SEPARATOR ' > ' ) PATH

  FROM DEPT_EMP

 GROUP BY EMP_NO

HAVING COUNT(*)>1

ORDER BY COUNT(*) DESC

먼저, 직원들의 부서 이동을 파악하기 위해 개인(EMP_NO) 별로 그룹화 한다.

그리고 부서 배정 회수가 1회 이상이 되어야 부서 이동이 발생하는 것이기 때문에 HAVING절이 필요하다.

마지막으로 그룹화된 내용들을 하나의 문자열로 묶어준다.

GROUP_CONCAT은 그룹으로 지정된 데이터(레코드)들을 지정한 구분자를 이용하여 하나의 문자열로 만든다. 

연습: 업무 변경이 많은 직원 명단을 변경 회수가 많은 사람순으로 출력하시오 (퇴직자 포함)

SELECT EMP_NO, GROUP_CONCAT(TITLE SEPARATOR ' > ' ) PATH

   FROM TITLES

 GROUP BY EMP_NO

 HAVING COUNT(*)>1

 ORDER BY COUNT(*) DESC;

정렬에 COUNT 함수를 직접 사용해야 한다.