실습 1

데이터를 제공하는 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');