5월 24일

* Conditional Expressions : 조건문

>> IF-THEN-ELSE logic

- CASE expression : 주로 사용

- DECODE function : 오라클 전용. 해석만 할 줄 알면 됨.

>> 예문

SELECT last_name, job_id, salary,

       CASE job_id WHEN 'IT_PROG'  THEN 1.10*salary

                   WHEN 'ST_CLERK' THEN 1.15*salary

                   WHEN 'SA_REP'   THEN 1.20*salary

                   ELSE salary

       END AS "Revised_salary"

FROM employees;

※ WHEN절 앞에 컬럼명을 쓰게 되면 동등 비교밖에 못 한다. (BETWEEN절을 쓸 수 없다.)

SELECT last_name, job_id, salary,

       CASE WHEN salary BETWEEN 2500  AND 5000  THEN 'A'

            WHEN salary BETWEEN 5001  AND 10000 THEN 'B'

            WHEN salary BETWEEN 10001 AND 20000 THEN 'C'

            ELSE 'D'

       END AS salary_grade

FROM employees

ORDER BY 4;

>> NVL2문을 CASE문으로 바꿔보기

SELECT employee_id, last_name, salary, commission_pct,

       NVL2(commission_pct, 'SAL+COMM', 'SAL') income

FROM employees

WHERE department_id IN (50, 80);

=>

SELECT employee_id, last_name, salary, commission_pct,

       CASE WHEN commission_pct IS NOT NULL THEN 'SAL+COMM'

            WHEN commission_pct IS NULL     THEN 'SAL'

       END income

FROM employees

WHERE department_id IN (50, 80);

* Group Functions : 그룹 함수

-> 한 그룹에서 결과가 하나만 나오는 함수

 - AVG

 - COUNT

 - MAX

 - MIN

 - STDDEV

 - SUM

 - VARIANCE

※ Bold체는 모든 데이터 타입이 사용 가능한 함수

>> Group Functions Syntax 그룹 함수 문법

SELECT    group_function(column), ...

FROM      table

[WHERE    condition]

[ORDER BY column];

ex)

SELECT MAX(last_name), MIN(last_name)

FROM employees;

SELECT MAX(hire_date), MIN(hire_date)

FROM employees;

SELECT AVG(salary), SUM(salary)

FROM employees

WHERE department_id = 50;

>> 그룹 함수는 null값을 빼고 계산한다.

ex)

SELECT AVG(commission_pct), AVG(NVL(commission_pct, 0))

FROM employees;

>> COUNT 함수의 사용법

SELECT COUNT(department_id), COUNT(DISTINCT department_id), COUNT(*)

FROM employees;

>> 일반적으로 그룹 함수는 그룹 함수를 사용하지 않은 컬럼을 함께 쓸 수 없다.

SELECT department_id, SUM(salary)

FROM employees;

- 쓰려면 GROUP BY절이 필요.

SELECT department_id, SUM(salary)

FROM employees

GROUP BY department_id;

- GROUP BY에 컬럼을 더 넣으면 그룹을 더 세분화할 수 있다.

  ORDER BY절은 항상 마지막 줄에.

SELECT department_id, job_id, SUM(salary)

FROM employees

GROUP BY department_id, job_id

ORDER BY 1, 2;

- GROUP BY절의 조건은 WHERE절 대신 HAVING절을 사용.

  (HAVING절 사용 시 WHERE절은 생략 가능, 추가 조건이 필요할 때 사용)

  그리고, 아래 문장은 그룹 함수의 전체 syntax

SELECT department_id, job_id, SUM(salary)

FROM employees

WHERE employee_id > 120

GROUP BY department_id, job_id

HAVING SUM(salary) > 10000

ORDER BY 1, 2;

- 그룹 함수의 중첩은 일반 컬럼이 함께 쓰일 수 없다. 행 수가 다르므로.

SELECT ROUND(AVG(salary))

FROM employees

GROUP BY department_id;

SELECT department_id, MAX(ROUND(AVG(salary)))

FROM employees

GROUP BY department_id;

* Join

>> Natural Join : 컬럼명이 같아야 조인 가능

SELECT department_id, department_name, location_id, city

FROM departments NATURAL JOIN locations;

>> USING절

SELECT employee_id, last_name, department_id, department_name

FROM employees JOIN departments

USING (department_id);

>> ON절 -> 가장 많이 사용, 응용이 가능.

SELECT employee_id, last_name, employees.department_id, department_name

FROM employees JOIN departments

ON (employees.department_id = departments.department_id);

>> 별칭 사용 가능

   별칭을 사용하게 되면 그 문장에는 원래 테이블명 대신 별칭만 존재

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);

* Equi Join, Non-Equi Join

>> Non-Equi Join : 동등하지 않은 조건의 테이블끼리 조인

ex)

SELECT e.employee_id, e.last_name, e.salary, s.grade_level

FROM employees e JOIN job_grades s

ON (e.salary BETWEEN s.lowest_sal AND s.highest_sal);

>> Natural Join, Using절은 무조건 Equi Join,

   On절에서 Equal을 사용하면 Equi Join, 아니면 Non-Equi Join

* OUTER joins : Join ~ On에서 파생된 것.

- LEFT OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

- RIGHT OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id);

- FULL OUTER JOIN

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id);

* 기본 조인은 테이블 2개이며, 테이블 3개, 1개 조인 방법도 있음.

1) 테이블이 하나일 때(같은 테이블일 때) -> 별칭을 이용하여 다른 테이블인 것처럼 만듦(셀프 조인)

ex)

SELECT e.employee_id, e.last_name, e.manager_id, m.last_name

FROM employees e JOIN employees m

                 ON (e.manager_id = m.employee_id);

2) 테이블이 3개일 때 -> 3-Way 조인

ex)

SELECT e.last_name, d.department_name, l.city

FROM employees e JOIN departments d

                 ON (e.department_id = d.department_id)

                 JOIN locations l

                 ON (d.location_id = l.location_id);

* CROSS joins : 경우의 수를 찾음. 확률 계산 시 이용.

* Join 정리. 구분.

>> 문법(syntax)

- Cross join

- Natural join

- Join ~ Using

- Join ~ On -> Outer join

>> 연산자

- Equi join

- Non-Equi join

>> Join에 만족하는 행 여부

- Inner join

- Outer join

>> Table 수

- Self join

- 3-way join

* 보너스 : Oracle 전용 Join 문법

>> Join을 사용하지 않음.

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

- Outer Join : 기호를 사용하여 행을 추가.

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id;

- 3-Way Join

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+) = d.department_id

AND e.department_id > 50;