[국비지원] KH 정보교육원 38일차 (1/2)

kh day 039

 

 오늘은 SQL의 GROUP BY, HAVING절과 동등조인을 배웠다. 이제 오늘로 SQL도 기본적인 문법은 다 배웠고 중급쿼리로 들어갔는데, 아직까지는 크게 어렵지 않다. 다만 곧 개강 2개월이 다 되어가다보니 복습할 양이 점점 늘어나고 있는데,  짜투리 시간을 잘 활용해야겠다. 자바시간에는 원래 람다의 표준 API의 함수적 인터페이스 부분을 다 마치기로 했었는데 설명을 자세히 해주시다보니 절반 정도 진도를 나갔다. 내일은 한주의 마무리인 만큼 SQL 시간엔 조인을 마무리하고 자바 시간엔 람다식을 마무리하지 않을까 싶다. 주말에는 SQL은 전반적으로 복습하고, 자바의 람다와 인터페이스 클래스 부분을 다시 훑어봐야겠다.

 

-------------------------------------------------------------------------------------

 

SELECT 문의 기본구조와 각 절의 실행순서

 - Clauses -                 - 실행순서 -
SELECT clause                        (5)
FROM clause                          (1)
[ WHERE clause ]                     (2)
[ GROUP BY clause ]                 (3)
[ HAVING clause ]                    (4)
[ ORDER BY clause ]                 (6)


------------------------------------------------------

GROUP BY clause

SELECT
  [단순컬럼1, ]
   ..
   [단순컬럼n, ]
   
   [표현식1, ]
   ..
   [표현식n, ]

   그룹함수1,
   그룹함수2,
    ...
   그룹함수n

   FROM table
   [ GROUP BY { 단순컬럼1, .., 단순컬럼n | 표현식1, .., 표현식n } ]
   [ HAVING 조건식 ]
   [ ORDER BY caluse ];
------------------------------------------------------
*주의할 점1: 
  GROUP BY뒤에, Column alias or index 사용불가!!! 
  (Alias는 select절에서 생성되는데, group by는 select절 보다 먼저 수행되기 때문)
*주의할 점2:
  GROUP BY뒤에 명시된 컬럼은, SELECT절에 그룹함수와 함께 사용가능!!
*주의할 점3:
  ORDER BY절의 다중정렬과 비슷하게, 다중그룹핑 가능
*주의할 점4:
  WHERE 절을 사용하여, 그룹핑하기 전에, 행을 제외시킬 수 있다!!
*주의할 점5:
  HAVING 절을 사용하여, 그룹핑한 후에, 행(X)이 아니라, 그룹(OK)을 
  제외시킬 수 있다!!
*주의할 점6:
  WHERE 절에는 그룹함수를 사용할 수 없다!! 
  (where절은 행을 대상으로 필터링하는데 그룹함수는 그룹을 대상으로 하기 때문)
*주의할 점7:
  GROUP BY 절은 NULL 그룹도 생성함!!
------------------------------------------------------
*** Chapter04 Page 10 참고할 것
------------------------------------------------------

SELECT
    department_id  AS 부서번호,        -- 그룹생성 단순컬럼
    avg(salary)        AS 평균월급         -- 각 그룹마다 적용될 그룹함수
FROM
    employees
GROUP BY
    department_id                       -- NULL도 그룹으로 생성 (*주의*)
ORDER BY
    1 ASC;


SELECT
    department_id   AS 부서번호,        -- 그룹생성 단순컬럼
    max(salary)     AS 최대월급,        -- 각 그룹마다 적용될 그룹함수1
    min(salary)     AS 최소월급         -- 각 그룹마다 적용될 그룹함수2
FROM
    employees
GROUP BY
    department_id        -- OK
    -- 1                      -- X (주의할점1 참고)
    -- 부서번호            -- X (주의할점1 참고)
ORDER BY
    1 ASC;
    -- department_id ASC; -- OK (ORDER BY절은 맨 마지막에 수행)
    -- 부서번호 ASC;         -- OK (ORDER BY절은 맨 마지막에 수행)

SELECT
    to_char( hire_date , 'YYYY' )   AS 년,       -- 다중그룹생성 표현식1
    to_char( hire_date , 'MM')     AS 월,       -- 다중그룹생성 표현식2
    sum(salary)                        AS 총급여  -- 각 그룹마다 적용될 그룹함수
FROM
    employees
GROUP BY
    to_char( hire_date , 'YYYY'),               -- 다중그룹생성 표현식1
    to_char( hire_date , 'MM')                  -- 다중그룹생성 표현식2
ORDER BY
    년 ASC;

-- 다중 칼럼 GROUP BY

------------------------------------------------------

HAVING 조건식
 : GROUP BY 절에 의해 생성된 결과(그룹들) 중에서, 지정된 조건에 일치하는 데이터를 추출할 때 사용
 
(1) 가장 먼저, FROM 절이 실행되어 테이블이 선택되고,
(2) WHERE절에 지정된 검색조건과 일치하는 행들이 추출되고,
(3) 이렇게 추출된 행들은, GROUP BY에 의해 그룹핑 되고,
(4) HAVING절의 조건과 일치하는 그룹들이 추가로 추출된다!!!

이렇게, HAVING 절까지 실행되면, 테이블의 전체 행들이, 2번의 필터링(filtering)이 수행된다.
( WHERE절: 1차 필터링, HAVING절: 2차 필터링 )

------------------------------------------------------


SELECT
    department_id, sum(salary)    -- 4th.
FROM
    employees                         -- 1st.
GROUP BY
    department_id                     -- 2nd.
HAVING
    sum(salary) >= 90000            -- 3rd.
ORDER BY
    1 ASC;                                -- 5th.

-- 각 부서별, 직원수 구하기
SELECT
    department_id,
    count(employee_id)
FROM
    employees
GROUP BY
    department_id                   -- NULL 그룹도 생성됨을 기억할 것!!!
HAVING
     count(employee_id) >= 6       -- 1st. filtering (for groups).
     -- salary >= 3000                  -- XX: 각 그룹에 대해, 단순컬럼들만 사용불가 (WHERE절에선 가능)
     -- department_id IN (10, 20)    -- OK: GROUP BY절에 나열된 단순컬럼들은 사용가능
     -- department_id > 10            -- OK: GROUP BY절에 나열된 단순컬럼들은 사용가능
     -- department_id IS NULL        -- OK: NULL 그룹도 있음을 기억할 것!!
ORDER BY
    1 ASC;


-- 각 부서별, 월급여 총계 구하기
SELECT
    department_id,
    sum(salary)
FROM
    employees
WHERE
    salary >= 3000              -- 1st. filtering (전체 사원중 월급 3000이상인 직원들만 보여줌).
GROUP BY
    department_id               --  salary >= 3000인 직원들로만 부서별로 그루핑
HAVING
    sum(salary) >= 90000        -- 2nd. filtering (부서별로 직원들 월급 합이 90000 이상인 부서만 보여줌).
ORDER BY
    1 ASC;  

--------------------------------------------------

-- Child(= Slave) table to refer to others.
DESC employees;

-- Parent(= Master) table to be referred.
DESC departments;   


-- 1. 특정 직원의 부서번호 찾아내기
SELECT
    last_name,
    department_id
FROM
    employees
WHERE
    last_name = 'Whalen';

-- 2. 찾아낸 부서번호를 이용한 부서명 조회
SELECT
    department_name
FROM
    departments
WHERE
    department_id = 10;

------------------------------------------------------
"JOIN" : 필요한 데이터가, 여러 테이블에 분산되어 있을 경우에,
여러 테이블의 공통된 컬럼을 연결시켜, 원하는 데이터를 검색하는
방법을 "조인"이라 한다.

따라서, 조인은 검색하고자 하는 컬럼이, 두개 이상의 테이블에
분산되어 있는 경우에 사용된다.
------------------------------------------------------

join 도식화

 

*두 테이블이 조인이 가능하려면 두 테이블이 성질이 동일한 컬럼을 가지고 있어야 한다.*

이 합병 가능한 조건을 만족하는 대표적인 케이스 
=> 참조 무결성 제약조건을 가지는 두 테이블간의 PK와 FK를 이용한 조인!

*
PK = Unique + Not NULL
FK = 중복OK, NULL OK
FK는 부모테이블의 PK를 가져야함

ex) 사람의 속성으로 "나이" 추출
     한 테이블에 속성으로 나이를 넣을 때, age라고 이름을 지음
     다른 테이블에도 나이가 있는데 얘는 이름을 my age라고 지음
     이런 경우 두 테이블의 age라는 칼럼과 my age라는 칼럼은 성질이 동일한 것임.

cf) 위의 경우에서 age (1~100) / my age (1~80) 이라면 (도메인이 다르다면) 두 칼럼은 성질이 동일하지 않다.


------------------------------------------------------
1. Oracle Join (Oracle에서만 사용되는 조인)
   : 여러 테이블을 연결하는 조인조건을 WHERE절에 명시
------------------------------------------------------
  a. Catesian Product (곱집합)
  b. Equi Join (동등 조인)
  c. Non-equi Join (비동등 조인)
  d. Self Join (셀프 조인)
  e. Outer Join (외부 조인)
------------------------------------------------------

A. Catesian Product (곱집합)
 가. 두 개 이상의 테이블을 공통컬럼없이 무지성으로 연결하는 조인 
 나. 모든 조인에서 가장 먼저 발생하는 조인이자 기본이 됨.
 다. 유효한 데이터로 사용되지 못함. (의미가 없다)
 라. 조인조건이 생략된 경우에 발생.

* 조인결과: 테이블1 x ... x 테이블n 개의 레코드 생성
------------------------------------------------------
 SELECT 테이블1.컬럼 , 테이블2.컬럼
 FROM 테이블1, 테이블2
------------------------------------------------------


SELECT
    count(employee_id) -- 107
FROM
    employees;


SELECT
    count(department_id) -- 27
FROM
    departments;


SELECT
    count(*)  -- 107 * 27= 2889
FROM
    employees,
    departments;


SELECT
    * 
FROM
    employees,
    departments;

------------------------------------------------------
B. Equal(= Equi) Join (동등 조인)
 가. 가장 많이 사용하는 조인
 나. 두 테이블에서, 공통으로 존재하는 컬럼의 값이 일치하는 행들을 연결하여 데이터를 반환.
       일치하지 않는 데이터는 제외됨.
 다. 대부분, 기본키(PK)를 가진 테이블(Parent)과 참조키(FK)를 가진 테이블(Child)을 조인할 때 사용
------------------------------------------------------
SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;
------------------------------------------------------


SELECT
    last_name,

    employees.department_id,
    departments.department_id,

    department_name
FROM
    employees,
    departments
WHERE
    employees.department_id = departments.department_id;

SELECT
    last_name,
    department_name,
    employees.department_id     -- 공통컬럼 사용시, 소속테이블명 기재로 모호성 제거
FROM
    employees,
    departments
WHERE
    employees.department_id = departments.department_id;

---

테이블에 별칭 사용

가. SELECT 절에서, 컬럼 별칭(Column Alias)을 사용했듯이,
     FROM 절에서도, 테이블 별칭(Table Alias)을 사용가능하다.
나. 테이블명이 길거나, 식별이 힘든 경우에 유용하다.
다. (*주의*) 테이블 별칭을 지정한 경우에는, 반드시 이 별칭을 사용하여, 컬럼을 참조해야 한다.
     만일, 테이블 별칭을 사용하지 않고, 테이블명으로 컬럼을 참조하면, 테이블명을 별칭(Alias)으로 인식하기 때문에 오류 발생.

------------------------------------------------------
SELECT alias1.컬럼 , alias2.컬럼
FROM 테이블1 alias1, 테이블2 alias2  --- (AS쓰면 안 된다)
WHERE alias1.공통컬럼 = alias2.공통컬럼;
------------------------------------------------------

-- 테이블 별칭(alias) 사용
SELECT
    emp.last_name,          
    dept.department_name,      
    emp.department_id  
    -- employees.department_id  -- 오류발생

FROM
    employees emp,          -- emp : 테이블 별칭(alias)
    departments dept        -- dept: 테이블 별칭(alias)
WHERE
    emp.department_id = dept.department_id;              -- 조인조건
    -- employees.department_id = dept.department_id;  -- 오류발생

---

검색조건 추가
가. Oracle 조인에서는, WHERE절에 AND / OR 연산자를 사용하여 조인조건에 검색조건을 추가할 수 있다.
나. 이로인해, WHERE의 어떤 조건이 조인조건이고, 어떤 조건이 검색조건인지, 쉽게 파악이 안되어, 가독성이 떨어짐
다. (*주의*) 따라서, 조인조건을 우선 명시하고, 나중에 검색조건을 명시하는 방법으로, 가독성을 향상 시켜야 한다.
라. 결과: 조인조건의 결과 중에서, 검색조건으로 필터링 된 결과를 반환

SELECT
    last_name,            -- 컬럼의 모호성이 없으면 테이블 별칭 안 써도 된다
    salary,                  -- 컬럼의 모호성이 없으면 테이블 별칭 안 써도 된다
    department_name  -- 컬럼의 모호성이 없으면 테이블 별칭 안 써도 된다
FROM
    employees emp,
    departments dept
WHERE
    emp.department_id = dept.department_id  -- 조인조건
    AND last_name='Whalen';                       -- 검색조건

SELECT
    department_name AS 부서명  , -- 컬럼의 모호성이 없으면 테이블 별칭 안 써도 된다
    count(employee_id) AS 인원수  -- 컬럼의 모호성이 없으면 테이블 별칭 안 써도 된다
FROM
    employees e,
    departments d
WHERE
    e.department_id = d.department_id           -- 조인조건
    AND to_char( hire_date , 'YYYY') <= 2005    -- 검색조건
GROUP BY
    department_name;  -- 컬럼의 모호성이 없으면 테이블 별칭 안 써도 된다