데이터베이스 3일차 - 그룹함수, 조인(JOIN)

DECODE, CASE 함수

DECODE

- 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출할 수 있다.

- DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용하며 VALUE값이 IF1일 경우 THEN1을 반환하고 IF2일 경우 THEN2를 순차적으로 반환한다.

- DECODE 함수 안에 DECODE 함수를 중첩으로 사용할 수 있지만, 조건연산자 사용 불가.

--DECODE 함수
SELECT last_name 이름, job_id,  salary,
    DECODE(JOB_ID, 'IT_PROG',  SALARY*1.10,
                    'ST_CLERK',SALARY*1.15,
                    'SA_REP',   SALARY*1.20,
                                SALARY) 수정월급
                            
FROM employees;

DECODE

DECODE 예제) 월급에 따른 세율 출력하기

--예제
SELECT last_name 이름, job_id 직무, salary 월급, 
    DECODE(FLOOR(SALARY/2000),0, '00%',
                            1, '09%',
                            2, '20',
                            4, '40%%',
                            3, '30%',
                            5, '42%',
                            6, '44%',
                               '45%') 세율 
FROM employees;

DECODE 예제

 

CASE

- 조건 연산자를 사용할 수 있어 DECODE 함수가 제공하지 못하는 비교연산을 수행할 수 있다.

--CASE 함수
SELECT last_name 이름, job_id, salary,
    CASE        WHEN salary<5000    THEN 'Low'
                WHEN salary<10000   THEN 'Medium'
                WHEN salary<20000   THEN 'Good'
                ELSE                     'Excellent'
    END "급여 수준"
FROM employees;

CASE 예제

위와 같이 CASE WHEN THEN ELSE END 순으로 사용하며 DECODE와 형태는 비슷하다.

 

 

그룹함수

집계함수

그룹만들기

--그룹 만들기 GROUP BY
SELECT department_id, SUM(salary) -- 부서별로 그룹 지어 월급 합계
FROM employees
GROUP BY department_id; -- SELECT 절에 있는 열의 이름이 있어야 함

그룹만들기

* GROUP BY에 여러개의 열을 적어 여러 그룹을 만들 수 있다.

 

그룹 예제1) 부서별 사원수, 최대최소급여, 급여합계, 평균급여 출력

--예제1
SELECT department_id 부서명,COUNT(employee_id) 사원수,
        MAX(salary) 최고급여,MIN(salary) 최소급여,
        SUM(salary) 급여합계,ROUND(AVG(salary),0) 평균급여
FROM employees
GROUP BY department_id
ORDER BY 급여합계 DESC;

그룹 예제1

그룹 예제2) 부서별 최고 월급과 최저 월급 들의 평균을 출력

--예제2
SELECT  ROUND(AVG(MAX(salary)),0) 부서별최고월급평균, 
        ROUND(AVG(MIN(SALARY)),0) 부서별최저월급평균
FROM employees
GROUP BY department_id;

 

그룹 예제2

JOIN

- 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법

- 행들의 공통된 값인 기본키 및 외래키를 사용하여 조인

- 두 개의 테이블을 SELECT 문장 안에서 조인하려면 적어도 하나의 컬럼이 공유되어야 한다.

 

JOIN의 종류

 동등조인

  - 내츄럴 조인(NATURAL JOIN) : 동일한 열이 있으면 JOIN하는 방식으로 ON 절 생략가능

-- 내츄럴 조인 : ON 절 생략가능
SELECT employee_id, first_name, JOB_TITLE, JOB_ID
FROM employees
NATURAL JOIN JOBS;

* 내츄럴 조인은 테이블 간에 동일한 열이 2개 이상 있을 때 원하는 조인을 할 수 없다.

 

  - 기본 방식 (ON 사용)

-- 동등 조인(기본)
SELECT  e.employee_id, e.last_name, e.department_id,
        d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

  - WHERE 절 추가

--WHERE 절 추가
SELECT  e.employee_id, e.last_name, e.department_id,
        d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id = 50;

 

 

  - 3개의 테이블 JOIN

--3개의 테이블 JOIN
SELECT e.employee_id, city, d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id
JOIN locations l
    ON d.location_id = l.location_id;

JOIN 예제) 부서명, 도시명, 국가명을 출력한다. 도시는 Seattle, London 만, 국가명은 United가 앞쪽에 붙은 도시만 출력 

--예제
SELECT d.department_name 부서명, l.city 도시명, country_name 국가명
FROM departments d
JOIN locations l
    ON d.location_id = l.location_id
JOIN countries c
    ON c.country_id = l.country_id
WHERE l.city = 'Seattle' or l.city = 'London'
        AND c.country_name LIKE 'United%';

 비동등조인

비동등조인

위의 두 테이블처럼 공유하는 열이 없을 때 조인을 하여 아래와 같은 결과를 만들어 낸다.

 

 

 자체조인(Self Join)

  어떤 열의 데이터가 다른 열에 들어가 있는 경우 한 테이블에서 자체적으로 조인을 하는 방식.

--자체 조인
SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id;

매니저들의 직원번호가 다른 사원들의 매니저 번호로 들어가 있으므로 자체조인을 하여 각 사원들의 매니저 이름을 출력하였다.