레도널스의 개발자 되어가기

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

SQL Functions

SQL 함수

1. Single-row Functions 단일행 함수

* 함수는 중첩 사용 가능

* Character 함수

SELECT employee_id, last_name, LOWER(last_name)

FROM   employees

WHERE  department_id = 50;

*

SELECT UPPER('oracle database'), LOWER('Oracle Database'), INITCAP('oracle database')

FROM dual;

* 계산식(dual 테이블 이용)

SELECT 23542*254 AS Result FROM dual;

*

SELECT employee_id, last_name, department_id

FROM   employees

WHERE  LOWER(last_name) = 'king';

* 연결 함수 CONCAT

SELECT employee_id, CONCAT(first_name, last_name) AS Fullname  <-  AS는 생략 가능

FROM   employees;

SELECT employee_id, CONCAT(CONCAT(first_name,' '), last_name) Fullname

FROM   employees;

* SUBSTR (섭스타) : SUBSTR('text', 검색 시작 위치, 글자 수)

SELECT SUBSTR('HelloWorld', 6, 5) FROM dual;

SELECT SUBSTR('HelloWorld', -5, 5) FROM dual;

두 식은 같다.

검색 시작 위치에 음수를 쓰면 맨 뒤부터 찾음.

*

SELECT * FROM employees

WHERE SUBSTR(last_name, -1, 1) = 'n';

= WHERE last_name LIKE '%n';

last_name이 n으로 끝나는 사원을 찾음.

* INSTR (인스타)

SELECT INSTR('HelloWorld', 'l') FROM dual;  ->  'l'의 위치

SELECT INSTR('HelloWorld', 'l', 1, 2) FROM dual;  ->  1(1번째)부터 검색해서 'l'이 2번째 반환되는 위치

해당 사항이 없으면 0을 반환

*

SELECT * FROM employees

WHERE INSTR(last_name, 'A') <> 0;

* 패딩 함수 LPAD, RPAD (글자 수 포함, 나머지 문자는 지정한 문자로 채움)

- LPAD : 왼쪽부터 패딩을 준다.

- RPAD : 오른쪽부터 패딩을 준다.

ex)

SELECT employee_id, last_name, LPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT employee_id, last_name, RPAD(salary,10,'*') AS Salary

FROM   employees

WHERE  department_id = 60;

SELECT RPAD(last_name, 15, '*') AS last_name,

       LPAD(salary, 10, '*') AS salary

FROM employees;

* TRIM 함수 : 잘라내기

ex)

SELECT TRIM('w' FROM 'window') FROM dual;  ->  모든 'w'

SELECT TRIM(LEADING 'w' FROM 'window'),  ->  선행하는(앞의) 'w'

       TRIM(TRAILING 'w' FROM 'window')  ->  따르는(뒤의) 'w'

FROM dual;

* Number Functions 숫자 함수

- 올림

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)

FROM dual;

- 버림

SELECT TRUNC(45.923,2), TRUNC(45.923,0), TRUNC(45.923,-1)

FROM dual;

- 나머지

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

* 날짜 연산하기

SELECT last_name, hire_date

FROM employees

WHERE hire_date < '1988-12-17';

* sysdate 함수

Database Server의 날짜.

서버와 클라이언트가 떨어져 있는 경우(서로 다른 타임존에 있는 경우) 시차가 발생할 수 있음.

SELECT sysdate FROM dual;

* sysdate의 표시 형식 변경하기

ex)

ALTER SESSION SET nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

: Session이 유지 중일 때만 날짜 형식 변경

* sysdate는 계산이 가능

ex)

SELECT sysdate +10, sysdate -10

FROM dual;  ->  날짜 계산

SELECT sysdate, sysdate +10/24, sysdate -10/24

FROM dual;  ->  시간 계산

SELECT sysdate, sysdate +10/(24*60), sysdate -10/(24*60)

FROM dual;  ->  분 계산

*

SELECT employee_id, hire_date, sysdate-hire_date

FROM employees;

date에 시간이 함께 나온다.

-

DESC employees;

해 보면, date 데이터 타입은 길이가 없음을 알 수 있다.

그렇지만 저장할 때는 시간도 같이 저장한다.

이것을 응용하면,

-

SELECT employee_id, hire_date, TRUNC(sysdate-hire_date)

FROM employees;

* 날짜 함수

1) MONTHS_BETWEEN : 두 날짜의 차이

ex)

SELECT employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date)

FROM employees;

응용)

SELECT employee_id, hire_date, ROUND(MONTHS_BETWEEN(sysdate, hire_date))

FROM employees;

2) ADD_MONTHS : 개월 더하기

SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 4)

FROM employees;

3) NEXT_DAY : 

SELECT sysdate, NEXT_DAY(sysdate, '월요일') FROM dual;

OR

SELECT sysdate, NEXT_DAY(sysdate, '월') FROM dual;

BUT

SELECT sysdate, NEXT_DAY(sysdate, 'Monday') FROM dual; <- 지역에 따라 안 됨

따라서,

일요일 1, 토요일 7

SELECT sysdate, NEXT_DAY(sysdate, 2) FROM dual;

4) LAST_DAY : 생략

5) ROUND : 날짜 초기화(기준에 따라)

ex)

- 'year' : 6월 30일 기준으로 반올림

SELECT sysdate, ROUND(sysdate, 'year') FROM dual;

- 'mm' : 15일 기준(30/2 = 15일)

- 'dd' : 하루 기준(24/2 = 12시간)

- 'd' : 일주일 기준(7/2 = 3.5일)

6) TRUNC : 날짜 초기화(이전)

* Conversion Functions : 변환 함수

1) TO_CHAR : 문자로

ex) SELECT TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') FROM dual;

- 날짜 형식 앞에 'fm' 붙이면, 데이터 외에 필요없는 것(자리수 채우는, 0이나, 공백) 삭제

ex) SELECT employee_id, hire_date, TO_CHAR(hire_date, 'yy/mm/dd')

    FROM employees;  ->  18/05/07

    SELECT employee_id, hire_date, TO_CHAR(hire_date, 'fmyy/mm/dd')

    FROM employees;  ->  18/5/7

2) TO_NUMBER : 숫자로

ex)

SELECT employee_id, last_name, TO_CHAR(salary, 'L99,999.99') AS salary

FROM employees

WHERE salary > TO_NUMBER('$8,000', '$9,999');

3) TO_DATE : 날짜로

ex)

SELECT employee_id, last_name, TO_CHAR(hire_date, 'yyyy/month/dd') AS salary

FROM employees

WHERE hire_date > TO_DATE('31-12-99', 'dd-mm-rr');

* yy: 같은 세기에서 찾음(99, yy일 경우 2099년을 찾게 됨)

  rr: 현재 세기를 기준으로(100/2=50년), 뒤쪽의 데이터를 찾는다면 자동으로 앞의 것에서 찾음

* Nesting Functions : 함수 중첩

ex)

SELECT last_name,

UPPER(CONCAT(SUBSTR(last_name, 1, 8), '_US'))

FROM employees

WHERE department_id = 60;

* General Functions : 일반 함수

1) NVL (expr1, expr2) : 인수가 null일 경우, 계산 가능한 값(타입이 같아야 함)으로 변환

ex) SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary

    FROM employees;

- 계산하지 않는 경우에도 쓸 수 있음

ex) SELECT employee_id, last_name, salary, NVL(commission_pct, -1)

 FROM employees;

2) NVL2 (expr1, expr2, expr3) : expr1이 null이 아니면 expr2, null이면 expr3을 반환

ex)

SELECT employee_id, last_name, salary + salary*NVL(commission_pct, 0) AS monthly_salary,

NVL2(commission_pct, 'COMM', 'NOCOMM') AS bigo

FROM employees;

3) NULLIF (expr1, expr2) : 인수가 같으면 null, 다르면 expr1을 반환

ex)  SELECT NULLIF('a','a') FROM dual;

ex2) SELECT first_name, last_name, NULLIF(LENGTH(first_name), LENGTH(last_name))

  FROM employees;

4) COALESCE (expr1, expr2, ..., exprn) : 처음으로 null이 아닌 인수의 값을 반환

ex) SELECT employee_id, commission_pct, manager_id,

 COALESCE(commission_pct, manager_id, 7777)

 FROM employees;