2/26 [Database#5] 일반/ 그룹함수, Join

5. 일반 함수

3) decode ( char, a1, b1, a2, b2, ......., c ) : a 조건을 만족하면 b 반환. 만족하지 않으면 c(default value) 반환.

- a, b의 개수는 가변(여러개 쓸 수 있음)

- equal 비교만 가능함 (대소비교시에는 case 함수 사용)

-- 사번, 이름, 부서번호, 직원유형

-- 직원유형

-- 부서번호 60 개발자

--          90 임원진

--          나머지 비개발자

select employee_id 사번, first_name 이름, department_id 부서번호,

        decode(department_id,

                60, '개발자',

                90, '임원진',

                '비개발자') 직원유형

from employees;

        

[그룹 함수] group by는 나중에 배운다고함~~~ 일단 아래 것만 숙지할 것

- 그룹지정은 select절에서 하기때문에, 그룹 함수는 where 조건절에 사용할 수 없음. 

    (select 절보다 where 조건절이 먼저 실행되기에 그룹지정 정보를 받아올 수 없는거임.)

-- 평균급여보다 많이 받는 사원의 사번, 이름, 급여

select employee_id, first_name, salary

from employees

where salary > avg(salary);

▷ count ( col ) : 해당 컬럼의 개수

▷ sum ( col ) : 해당 컬럼 값의 합

▷ avg ( col ) : 해당 컬럼 값의 평균

▷ max ( col ) : 해당 컬럼 값의 최대 값

▷ min ( col ) : 해당 컬럼 값의 최소값

- count( * )를 제외한 나머지의 경우에는 null 값을 제외하고 반환됨

- distinct를 사용하면 중복 값을 제외할 수 있음

- 전체 테이블을 대상으로 위의 함수를 사용할 때는 단일행으로 반환됨. 그룹을 지정하여 사용하면 다중행 반환됨

-- 회사의 총사원수, 급여총합, 급여평균, 최고급여, 최저급여

select count(employee_id), sum(salary), avg(salary), max(salary), min(salary)

from employees;

[ JOIN과 SubQuery ]

1. JOIN : 둘 이상의 테이블을 연결하여 데이터를 검색

1) 기본문법 : from절에서 join할 테이블을 ,로 연결. 가독성을 높이기 위하여 테이블 이름에 alias 사용

    * . (마침표) : in, have의 의미를 가지고 있음 (ex. employees.department_id "직원테이블에 있는 부서번호")

    

    * alias 특이사항!!

    - 컬럼 이름에 alias를 지정했을 때는, order by에서 컬럼 이름 or 지정한 alias 어느 것이든 사용 가능

    - 테이블 이름에 alias를 지정했을 때는, 테이블 이름은 사용불가 (지정한 alias만 사용가능.)

    

    

        >> 다만, 위 first_name처럼 한 테이블에만 있는 컬럼의 경우에는 테이블 이름을 명시하지 않아도 됨

select employees.employee_id, employees.first_name, employees.department_id, departments.department_name

-- 부서테이블의 부서번호를 가져와도 됨. 나머지는 테이블표시를 안해도 되지만, 가독성을 위해 보통 표기함

from employees, departments

where employees.department_id = departments.department_id -- 부서번호가 없는 킴벌리는 누락됨

order by employee_id;

-- alias 사용

select a.employee_id, a.first_name, a.department_id, b.department_name

from employees a, departments b

where a.department_id = b.department_id

order by employee_id;

2) Cartesian Product: Join 조건을 설정하지 않았을 때, 각 테이블이 1 대 1 매핑처리되어 나오는 결과 (테이블 * 테이블)

    * ANSI 표준 : Cross Join

- '모든 경우의 수'가 고려된 테이블의 단순 결합이기에, 사실상 이 자체로 유의미한 데이터는 아님

select *

from employees, departments    -- 직원 테이블과 부서 테이블이 1:1 매핑되어버림. (카타시안 프로덕트)

order by employee_id;

3) Equi Join : equality condition에 의한 join    (* ANSI 표준 : Natural Join)

- Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에 값이 없다면 데이터를 반환하지 못함

(1) 구문형태

select table1.colum1[, table2.column2 ...]

from table1 join table2

where table1.colum1 = table2.column2

(2) join 조건 고려사항

- where절의 조건 개수는 n-1개 (n은 join한 테이블의 개수) 이상    (일반적으로 n-1개로 함)

- 조건은 테이블간 관계성을 의미해야 함

- PK 및 FK 값을 이용하여 조인함

    * Primaray Key(고유키) : 중복값이 존재하지 않음. null 값이 존재하지 않음

    * Foreign Key(외래키/참조키) : 다른테이블과 연결되는 컬럼

- null 값은 null 값과 equal 비교도 할 수 없음

(3) 예제

--1. 'seattle'에 근무하는 사원의 사번, 이름, 부서이름, 도시이름

select a.employee_id, a.first_name, b. department_name, c.city

from employees a, departments b, locations c

where a.department_id = b.department_id and b.location_id = c.location_id and

        lower(c.city) = lower('seattle');

--2. 'asia'에 근무하는 사번, 이름, 부서이름, 도시이름

select e.employee_id, e.first_name, d.department_id, l.city

from employees e, departments d, locations l, countries c, regions r

where e.department_id = d.department_id

            and d.location_id = l.location_id

            and l.country_id = c.country_id

            and c.region_id = r.region_id

        and lower(r.region_name) = lower('europe');    > 화면에 반환되는 데이터와 join 해야 하는 데이터는 상이할 수 있음

--3. 10, 80, 90번 부서에 근무중인 사원의

-- 사번, 이름, 직책이름, 부서이름

select e.employee_id, e.first_name, j.job_title, d.department_name

from employees e, jobs j, departments d

where e.job_id = j.job_id

            and e.department_id = d.department_id

        and e.department_id in (10, 80, 90); -- 부서 아이디는 employees 테이블에서 가져와야함.

                                          -- (문제 문맥상 근무중인 사원의 부서 ID이므로.. 또한 DB에 따라서는 결과값이 달라져버릴 수도 있음)

--4. 사번이 200인 사원의 근무 이력

-- 사번, 이름, 직책이름, 부서이름, 근무개월수(소수점 둘째자리까지)

select e.employee_id 사번, e.first_name 이름, j.job_title 직책이름, d.department_name 부서이름,

        to_char(months_between(h.end_date, h.start_date), '999.99') 근무개월수    -- to_char 사용하면 소수점 뒷 자리가 0으로 표현됨

from employees e, job_history h, jobs j, departments d                           -- (round보다는 to_char를 많이 사용함)

where e.employee_id = h.employee_id

            and h.job_id = j.job_id

            and h.department_id = d.department_id

        and h.employee_id = 200;

4) Self Join : 자기 자신 테이블과의 join

-- 모든 사원의 부서이름, 사번, 이름, 매니저사번(직속상관), 매니저이름

select d.department_name 부서이름, e.employee_id 사번, e.first_name 이름,

        e.manager_id "직속상관 사번", em.first_name "직속상관 이름"

from employees e, departments d, employees em

where e.department_id = d.department_id

        and e.manager_id = em.employee_id;

- end.