여기에서는 MariaDB를 이용하여 SQL문을 연습한다.

MariaDB 설치는 다운로드 페이지에 “Instructions”를 참조하거나 구글링을 하면 쉽게 찾을 수 있다.

MariaDB와 Mysql은 기본적으로 같기 때문에

다음 사이트로 접속해서 샘플 데이터 베이스를 다운 받아서 설치한다 (상세한 내용은 검색해 보길...).

        https://dev.mysql.com/doc/employee/en/employees-installation.html

최근(2018년 5월) 확인 결과 위 주소에서 링크로 제공되는 github에서 다운로드 받을 수 있다.

압축 파일(test_db-master.zip)로 다운 받아서, 다음 내용대로 실행하면 된다.

압축 파일 명이 employees_db-full-1.0.6.tar.bz2 이 아니고, test_db-master.zip이다.

이 주소의 주요 내용은 employees_db-full-1.0.6.tar.bz2 파일을 다운 받아서 압축을 해제 한 뒤

        mysql -t < employees.sql

로 실행하면 된다는 것인데

이렇게 하면 잘 안되고 다음과 같이 비밀번호를 입력하게 처리해야 제대로 설치가 된다.

        mysql -u root -p < employees.sql

대부분 MariaDB(Mysql)을 설치하면서 root 비밀번호를 설정했기 때문이다.

리눅스는 압축을 해제하고 해제한 디렉토리에서 mysql 실행이 가능한데

윈도우는 MariaDB(Mysql)의 설치 경로를 환경변수로 등록해야 가능하다.

자세한 내용은 이 블로그를 참고하기 바란다.

환경 변수 등록후 예제의 압축 파일을 해제한 디렉토리에서 위 명령을 실행하면 된다.

설치가 제대로 되었다면

터미널(putty등)에서 MariadB에 접속해서 “show databases”로 employees 데이터 베이스가 제대로 생성되었는지 확인한다.

또는 HeidiSQL 이나 mysql에서 제공하는 Workbench로 확인해도 좋다.

개인적으로 Workbench이 사용이 더 편하다고 생각해서 이것을 사용한다.








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

2. 샘플 데이터 베이스 개요  (0) 2016.04.02
실습 1  (2) 2016.04.02
실습 2 - GROUP  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02

Mysql에서 제공되는 직원(Employees) 데이터 베이스는 

다음 그림과 같이 6개의 테이블로 구성되어 있다.



원본 주소: https://dev.mysql.com/doc/employee/en/sakila-structure.html

직원(employees), 부서(departments), 급여(salaries)의 기본 테이블이 있고

직원이 속한 부서를 나타내는 dept_emp, 해당부서의 부서장을 저장하는 dept_manager 있다.

마지막으로 직원의 업무(titles)를 저장하는 테이블이 있다.

 

직원(employees)

설명

필드명

자료형

크기

기타

직원번호emp_noINT 기본키
생년월일birth_dateDATE  
이름first_nameVARCHAR14 
last_nameVARCHAR16 
성별genderEUM  
입사일자hire_dateDATE  

급여(salaries)

설명필드명자료형크기기타
직원번호emp_noINT 외래키
급여salaryINT  
지급시작일from_dateDATE 보조키
지급종료일to_dateDATE  

부서(departments)

설명필드명자료형크기기타
부서번호dept_noCHAR4기본키
부서명dept_nameVARCHAR40 

소속부서(dept_emp)

설명필드명자료형크기기타
직원번호emp_noINT 외래키
부서번호dept_noCHAR4외래키
근무시작일from_dateDATE  
근무종료일to_dateDATE  

부서장(dept_manager)

설명필드명자료형크기기타
직원번호emp_noINT 외래키
부서번호dept_noCHAR4외래키
부서장시작일from_dateDATE  
부서장종료일to_dateDATE  

직원의 업무(titles)

설명필드명자료형크기기타
직원번호emp_noINT 외래키
업무명titleVARCHAR50보조키
업무시작일from_dateDATE 보조키
업무종료일to_dateDATE  

 

직원이 속한 부서를 나타내는 소속부서(dept_emp) 테이블은

직원(employees) 테이블의 직원번호(emp_no)와 부서(departments) 테이블의 부서번호(dept_no)를 기본키(Primary key)로 가지는데

이것을 외래키(Foreign Key)라고 한다.

부서의 부서장을 저장하는 부서장(dept_manager) 테이블도 동일한 방식으로 생성되었다.


급여(salaries) 테이블은 직원번호(emp_no)가 외래키로 지정되었는데

직원번호만 외래키로 할 경우 데이터 중복이 발생한다.

한 직원이 1년 동안 2,000원을 받는 다고 할 때 퇴사할 때까지 2,000원 받는 것이 아니고

3,000, 4,000 등으로 변하기 때문이다.

따라서 한 개인의 급여(연봉)는 중복 될 수 있고 중복을 피하기 위해서는 보완적인 키가 필요하다.

급여 테이블에서는 지급시작일(from_date)이 보조키로 지정되어 있다.


직원의 업무(titles)도 시간의 흐름에 따라 바뀌기 때문에 이와 유사한 구조로 되어 있는데

업무명(title)이 키로 지정되어 3개의 복합키(Composite Key)로 지정되었다.

      


 

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

1. 설치  (2) 2016.04.02
실습 1  (2) 2016.04.02
실습 2 - GROUP  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02

데이터를 제공하는 MySQL에 따르면

https://dev.mysql.com/doc/employee/en/employees-validation.html

준비된 데이터는 다음과 같은 개수(expected_records)를 가진다.

각 테이블의 데이터를 확인하는 SQL문을 만들어 보자

+--------------+------------------+------------------------------------------+
| table_name    | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees     |               300024   | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments   |                        9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |                      24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp        |               331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles              |                443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries          |              2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+

        SELECT COUNT(*) FROM EMPLOYEES;
        SELECT COUNT(*) FROM DEPARTMENTS;
        SELECT COUNT(*) FROM DEPT_MANAGER;
        SELECT COUNT(*) FROM DEPT_EMP;
        SELECT COUNT(*) FROM TITLES;
        SELECT COUNT(*) FROM SALARIES;

COUNT 함수는 집계 함수 (Aggregate function) 라고 하여

개수(COUNT), 합(SUM), 평균(AVG), 최대값(MAX), 최소값(MIN)이 있다.

지정된 그룹(GROUP)에 대한 계산을 진행하는 함수로

여기에서는 그룹이 지정되지 않았기 때문에 전체 그룹(테이블)을 대상으로 계산된다.


여기서는 작성해야할 SQL문에 대한 일반적인 질문을 SQL문으로 작성하면서 SQL 능력을 키우려고 한다.

다음 문제들을 가급적 SQL문을 보지 않고 직접 작성해 보면서 익히길 바란다.


1. 직원 이름이 빠른 순(A, B, C …) 순으로 리스트를 출력하시오.

SELECT *

   FROM EMPLOYEES

 ORDER BY FIRST_NAME, LAST_NAME;

ORDER BY는 데이터 정렬을 의미하는 것으로 올림차순(ASC)과 내림차순(DESC)이 있다.

올림차순은 ABC~~가나다~~순을 의미하고 내림차순은 반대의 개념이다.

ORDER BY 뒤에 필드 명을 쓰고 정렬을 생략하면 올림차순으로 조회된다.


연습: 직원 나이가 적은 순으로 출력하시오.

SELECT *

   FROM EMPLOYEES

 ORDER BY BIRTH_DATE DESC;


2. 직원 중 나이가 가장 많은 사람의 생년월일은 언제 인가?

SELECT MIN(BIRTH_DATE)

  FROM EMPLOYEES


3. 직원 중 나이가 가장 많은 사람의 나이는 몇 살 인가?

SELECT TIMESTAMPDIFF(YEAR, MIN(BIRTH_DATE), NOW())

  FROM EMPLOYEES

NOW는 현재 시간을 가져오는 함수이고,

TIMESTAMPDIFF와 DATEDIFF 시간의 차이를 구하는 함수이다.

연습: 가장 나이 많은 직원과 적은 직원의 나이 차이는 몇 살 인가?

SELECT TIMESTAMPDIFF(YEAR, MIN(BIRTH_DATE), MAX(BIRTH_DATE)) FROM EMPLOYEES;


4. 직원들의 업무(titles)에는 직원별로 업무가 저장되어 있다. 이 회사의 업무 종류 리스트를 구하시오.

SELECT DISTINCT TITLE

  FROM TITLES;

DISTINCT는 지정된 필드에 대한 고유값을 보여주는 명령어 이다.


연습: 이 회사의 업무 종류 개수를 구하시오.

SELECT COUNT(DISTINCT TITLE)

  FROM TITLES;

집계 합수에도 DISTINCT를 사용할 수 있다.


5. 가장 최근에 입사한 사람 100명만 출력하시오

SELECT *

  FROM EMPLOYEES

ORDER BY HIRE_DATE DESC

   LIMIT 100;

LIMIT는 조회 데이터의 개수를 제한하는 명령어 이다.

Limit뒤에 정수만 단복으로 나올 경우 (ex: LIMIT 100) 이 정수는 개수를 의미한다.

Limit뒤에 정수가 둘일 경우는 는 순서와 개수를 의미한다.

예로 LIMIT 100, 10 일 경우 100은 100번째, 10은 개수를 의미한다.

즉, 100부터 110까지의 데이터가 조회된다.

다만, 순서가 1부터가 아닌 0부터 시작하기 때문에 실제 의미는 101부터 111까지의 데이터가 조회된다.


연습: 급여가 가장 많은 사람 10명을 구하시오.

SELECT *

   FROM SALARIES

 ORDER BY SALARY DESC

   LIMIT 10;


연습: 급여가 가장 많은 사람 10명을 제외하고 다음 10명을 구하시오.

즉, 11등부터 20등 까지…

SELECT *

   FROM SALARIES

  ORDER BY SALARY DESC

    LIMIT 10, 10;


6. 입사한지 가장 오래된 사람의 이름은 무엇인가?

SELECT *

  FROM EMPLOYEES

ORDER BY HIRE_DATE

   LIMIT 1;


7.1999년에 입사한 직원 리스트를 구하시오.

SELECT *

  FROM EMPLOYEES

 WHERE YEAR(HIRE_DATE)=1999;

WHERE는 테이블 전체에 대한 조건을 의미하는 함수로 그룹에 조건을 사용하는 Having과 차이가 있다.

YEAR 함수는 지정된 값에서 년도를 추출하는 함수로 날짜 함수는 찾아보길…


8. 1999년에 입사한 직원 중 여자 직원(GENDER='F') 리스트를 구하시오.

SELECT *

  FROM EMPLOYEES

 WHERE YEAR(HIRE_DATE)=1999 AND GENDER='F';

조건이 여러 개일 경우 AND나 OR를 사용할 수 있다.


연습: 1998년에 입사한 직원 중 남자 직원(M)은 몇 명인가?

SELECT COUNT(*)

   FROM EMPLOYEES

 WHERE YEAR(HIRE_DATE)=1999 AND GENDER='M';


9. 1998년에 입사한 남자 직원 중 나이가 어린 5명을 구하시오.

SELECT *

  FROM EMPLOYEES

WHERE YEAR(HIRE_DATE)=1999 AND GENDER='M'

ORDER BY BIRTH_DATE

 LIMIT 5;


10. 1998년이나 1999년에 입사한 직원의 수를 구하시오.

SELECT COUNT(*) -- 5669

  FROM EMPLOYEES

WHERE YEAR(HIRE_DATE)=1998 OR YEAR(HIRE_DATE)=1999


연습: 1998년이나 1999년에 입사한 직원 중 남자 직원의 수를 구하시오.

SELECT COUNT(*) -- 3364

  FROM EMPLOYEES

WHERE (YEAR(HIRE_DATE)=1998 OR YEAR(HIRE_DATE)=1999) AND GENDER='M'

OR와 AND가 같이 사용될 때는 괄호() 사용에 주의해야 한다.

년도는 OR로 괄호를 이용하여 먼저 처리하고 이 조건에 의해 구해진 데이터 중 성별이 남자(AND)인 직원만 추출하게 된다.

많이 하는 실수로 잘 못 사용하면 처리 속도에 문제가 생기고 원하지 않는 데이터들이 나오게 된다.


11. 1995년부터 1999년까지 입사한 직원의 수를 구하시오.

SELECT COUNT(*) -- 34027

  FROM EMPLOYEES

WHERE YEAR(HIRE_DATE) BETWEEN 1995 AND 1999

    BETWEEN은 두 조건 사이의 값이 일치하는 데이터를 계산하는 명령어이다.


12. 1995, 1997, 1999년에 입사한 직원의 평균 나이를 구하시오.

SELECT AVG(TIMESTAMPDIFF(YEAR, BIRTH_DATE, NOW())) -- 57.1324

  FROM EMPLOYEES

WHERE YEAR(HIRE_DATE) IN (1995, 1997, 1999);

IN 명령어는 개별 값에 대한 조건을 부여하는 것으로 다수의 OR를 의미한다.

문자에 대해서도 사용할 수 있다.


연습: 성(last_name)이 Senzako, Pettis, Henseler인 직원을 출력하시오.

SELECT *

  FROM EMPLOYEES

WHERE LAST_NAME IN ('Senzako', 'Pettis', 'Henseler');









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

1. 설치  (2) 2016.04.02
2. 샘플 데이터 베이스 개요  (0) 2016.04.02
실습 2 - GROUP  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02

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

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

"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 함수를 직접 사용해야 한다.



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

2. 샘플 데이터 베이스 개요  (0) 2016.04.02
실습 1  (2) 2016.04.02
실습 3 - Join  (0) 2016.04.02
실습 4 - SubQuery  (0) 2016.04.02
연습 1  (3) 2016.04.02

DBMS는 데이터를 하나의 테이블에 저장하는 것이 아니라 "샘플 데이터 베이스 개요"의 ERD처럼 특성에 맞추어 분할해서 저장한다.

따라서 제대로 된 정보를 보기 위해서는 관련된 테이블을 결합(JOIN)해서 보여 줘야 한다.

이러한 결합을 조인(JOIN)이라고 하고 기준에 따라 Left, Right로 나뉘고, 

데이터 일치 여부에 따라 Inner, Outer 등으로 구분된다.

여기서는 Left Inner만 다룰 예정으로 대부분의 SELECT문이 Left Inner 조인 이기 때문이다.

일반적으로 조인이라고 하면 Left Inner을 의미할 정도로 많이 쓰이고, 

정의하자면 두 테이블 간의 교집합이라고 할 수 있다.

즉, 두 테이블간에 공통으로 있는 값을 찾는 것이라고 할 수 있다.


샘플 데이터베이스를 예로 하면 한 직원이 속한 부서는 3개의 테이블로 구성되어 있다.

직원 정보(EMPLOYEES), 부서 정보(DEPARTMENTS) 그리고 한 직원이 속한 부서를 저장하는 테이블(dept_emp)이 필요하다.

이 테이블 들이 모여서 한 직원의 부서에 대한 제대로 된 정보를 알 수 있는 것이다. 

자세한 내용은 데이터 베이스 정규화 등 관련 자료를 찾아보길 바란다.


조인(Join)문을 쉽게 이해하고 풀기 위해서는 "샘플 데이터 베이스 개요"의 ERD를 참고하는 것이 좋다.


1. 현재 근무 중인 직원 정보를 출력하시오.

SELECT E.*

  FROM EMPLOYEES E

  INNER JOIN DEPT_EMP DE ON DE.EMP_NO=E.EMP_NO

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

2개 이상의 테이블이 사용되고,

각 테이블에는 같은 필드명(EMP_NO)이 사용될 수 있어서 테이블 별칭을 사용한다.

직원 테이블(EMPLOYEES)은 E, 부서 할당 테이블(DEPT_EMP)은 DE로 사용한다.

그리고 두 테이블 간의 관계를 나타내는 직원번호(EMP_NO)를 이용하여

조인을 하고 필요한 데이터를 구성하게 된다.

ON절에는 두 테이블의 관계를 나타내는 조건을 작성한다.

즉, 직원과 할당된 부서 테이블은 직원번호(EMP_NO)필드로 관계가 구성되어 있기 때문에 

두 테이블의 직원번호가 같은(DE.EMP_NO=E.EMP_NO) 데이터를 선택하라고 한다.


연습: 현재 근무 중인 직원의 모든 정보(수행업무 포함) 를 출력하시오.

SELECT E.*, T.TITLE

   FROM EMPLOYEES E

   INNER JOIN TITLES T ON T.EMP_NO=E.EMP_NO

WHERE T.TO_DATE = '9999-01-01';

직무 테이블(TITLES)과 조인하면 된다.


2. 다음 그림과 같이 현재 근무 중인 부서를 출력하시오.

SELECT E.EMP_NO, E.FIRST_NAME, D.DEPT_NAME

   FROM DEPT_EMP DE

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

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

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

부서 테이블(DEPARTMENTS)을 추가하여 총 3개의 테이블이 사용된다.

테이블이 더 추가되더라도 위 코드처럼 LEFT JOIN을 추가해 주면 된다.

부서는 부서번호(DEPT_NO)를 이용하여 관계를 설정한다.


3. 가장오래 근무한 직원 10명의 현재 부서를 출력하시오.

SELECT E.EMP_NO, E.FIRST_NAME, E.LAST_NAME, D.DEPT_NAME, E.HIRE_DATE

  FROM DEPT_EMP DE

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

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

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

ORDER BY HIRE_DATE

  LIMIT 10 ;

2번 문제와 동일한 데이터를 대상으로 입사일(HIRE_DATE)로 정렬하여 10명을 추출(LIMIT)한다.


4.부서별로 직원 수를 구하되 부서 이름이 나오게 출력하시오.

SELECT DEPT_NAME, COUNT(*)

   FROM DEPT_EMP DE

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

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

  GROUP BY DEPT_NAME

  ORDER BY DEPT_NAME;

이 경우는 조인보다 서브 쿼리가 더 효율적일 수 있다(Subquery 참조)

왜냐하면 위 SQL은 조인을 하고 문자열인 DEPT_NAME으로 그룹핑을 하기 때문에 데이터 양에 따라 속도 변화가 크다.

코드로 그룹핑하고 처리하는 것이 더 빠르게 처리 된다.


연습: 부서별, 성별 직원 수를 구하시오

SELECT D.DEPT_NAME, E.GENDER, COUNT(*)

   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

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

 GROUP BY D.DEPT_NAME, E.GENDER

 ORDER BY D.DEPT_NAME, E.GENDER;

성별(GENDER)은 직원(EMPLOYEES) 테이블에 있기 때문에 조인할 테이블을 추가하고

그룹 필드를 부서명과 성별로 2개가 사용된다.


5. 급여 평균이 가장 높은 부서 5개를 출력하시오.

SELECT D.DEPT_NAME, AVG(SALARY) SA

  FROM DEPT_EMP DE

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

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

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

GROUP BY D.DEPT_NAME

ORDER BY SA DESC

LIMIT 5;

연습: 급여 평균이 가장 높은 부서를 제외하고, 급여 평균이 높은 부서를 5개를 출력하시오.

SELECT D.DEPT_NAME, AVG(SALARY) SA

  FROM DEPT_EMP DE

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

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

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

GROUP BY D.DEPT_NAME

ORDER BY SA DESC

  LIMIT 1, 5;


6. 급여를 많이 받는 부서장 리스트를 출력하시오.

SELECT E.*, S.SALARY

   FROM DEPT_MANAGER DM

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

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

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

 ORDER BY SALARY DESC;

ERD에 따르면 부서장 테이블(DEPT_MANAGER)은

직원 테이블을 (EMPLOYEES) 통해서 급여(SALARIES) 테이블과 관계가 있는 것으로 나온다.

하지만, 실질적으로는 직원번호(EMP_NO)를 통해 관계를 맺기 때문에 직접 조인을 맺어 주면 된다.

여기에 사용된 직원 테이블은 급여를 가지고 온후 직원 이름을 보여주기 위해 사용된 것으로 의미가 다르다.

주의: ERD에 관계 설정이 간접적이더라도 조인 조건에서는 바로 설정해서 사용하는 경우가 있다.


7. 개발부(Development)에서 급여를 가장 많이 받는 직원 5명을 출력하시오.

SELECT E.*, SALARY

   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 DEPT_NO='D005'

 ORDER BY SALARY DESC

    LIMIT 5;

개발부(Development)의 부서코드를 찾아야 한다.

부서(DEPARTMENTS) 테이블을 조회해 보면 개발부의 부서코드는 D005이다.

따라서 부서 할당 테이블의 부서코드(DEPT_NO)가 D005인 직원 리스트를 조회해서 급여순으로 정렬하면 된다.


생각해보기: 각 부서에서 급여를 가장 많이 받는 직원 리스트를 구하시오.


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

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

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;

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;

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;

급여를 알기 위해 급여 테이블을 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


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

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

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

조인을 사용하는 것이 추천되는 방식인데

문제는 문자열로 그룹화 하는 것이다.

가급적 정형화된 코드로 그룹화 하는 것이 좋다.

더욱이 조인을 먼저 하기 때문에 많은 데이터 조인(부서수 10 & 인원수 240,124)이 발생한다.


개인적으로 가장 선호하는 방식은 위에 작성한 코드처럼

부서코드로 그룹화해서 인원을 계산하고, 부서명을 찾기 위해 조인하는 것이다.

부서수가 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');

전체 평균을 Subquery로 구하여 조건절(WHERE)에서 사용하였다.

먼저, 현재 근무 중인 직원들의 급여 평균(AVG(SALARY))를 계산하고

그 금액보다 큰(>=) 직원들 인원을 계산(COUNT)한다.


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

SELECT *

   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 *

  FROM EMPLOYEES E 

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

이 데이터에서는 퇴직 필드가 없기 때문에 퇴직의 개념부터 먼저 정의해야 한다.

이 데이터에서 근무중인 직원은 "실습 2 - GROUP" 에서 정의한 데로

급여나 현재 근무 부서, 업무의 종료일자가 '9999-01-01'인 것이다. 

따라서 퇴사는 종료일자가 '9999-01-01'아닌 것이 될 것이다. 

즉, 직원중에서 급여, 근무 부서, 업무 테이블에 종료일자가 '9999-01-01'인 데이터가 없는(NOT EXISTS, NOT IN ) 직원이 퇴사자가 된다.


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)

앞 5번 SQL문을 Subquery로 하여 그룹화 하였다.

퇴사 날짜를 구하기 위해 직원 테이블이 아닌 할당된 부서테이블(DEPT_EMP)을 사용하였다.

퇴사 날짜는 부서에서 근무한 마지막 날짜인 종료날짜(TO_DATE) 중에서 가장 큰값(Max)이 퇴사 날짜가 될 것이다.

각 개인별(GROUP BY DE.EMP_NO) 퇴사 날짜를 구해서

퇴사날짜를 년도별로 그룹화 하면 연도별 퇴사 직원수를 구할 수 있다.

이 경우는 다음과 같은 SQL 문이 더 효율적일 수 있다.


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


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

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

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