SQL (6/4 오후)

플래시백

conn / as sysdba

SELECT * FROM v$option;

WHERE parameter like 'Flashback%';  ← 플래시백 유료 옵션 확인하기

* 플래시백 실습

conn hr/hr

UPDATE employees

SET salary = salary*1.5

WHERE employee_id = 141;

commit;

SELECT salary FROM employees

WHERE employee_id = 141;

=> 롤백은 안 되지만, undo 파일에 남아있을 것임.

* 플래시백 쿼리

SELECT employee_id, salary FROM employees

as of timestamp (systimestamp-5(24*60))

WHERE employee_id = 141;

* 플래시백 버전 쿼리

col versions_starttime for a25

col versions_endtime for a25

set line 120

SELECT versions_starttime, versions_endtime, salary

FROM employees

versions between timestamp minavalue and maxvalue

WHERE employee_id = 141;

Time Zones

* 시간대

표준 시 : 그리니치(Greenwich) 천문대 시간

우리나라 : 표준 시 + 9

- sysdate : date type을 반환

- systimestamp : timestamp(date/time) type을 반환

SELECT sysdate, systimestamp FROM dual;

-> 서버 시간

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 내 시간

ALTER SESSION set time_zone = '-07:00';

-> 나의 세션 설정 바꾸기(미국 시간)

SELECT current_date, current_timestamp, localtimestamp FROM dual;

-> 다시 확인해보면 날짜가 바뀌어 있다.

※ 글로벌 환경에서는 시간대 조정이 필요할 수 있다.

* 시간 데이터 타입 확인하기

실습)

CREATE TABLE timetest1

(a date,

 b timestamp,

 c timestamp with time zone,

 d timestamp with local time zone);

INSERT INTO timetest1

VALUES(sysdate, sysdate, sysdate, sysdate);

SELECT * FROM timetest1;

>> local time zone : 클라이언트의 시간대로 바꿔준다.

>> 시간 정보에서 초(sec)의 정밀도는 기본(default)값이 6, 최대 9까지 지정 가능(Max 11byte)

SELECT * FROM tab;

※ DB에 있는 테이블 보기

* 데이터 타입 변경하기

CREATE TABLE dept50

AS

SELECT employee_id, last_name, hire_date, job_id

FROM employees

WHERE department_id = 50;

SELECT * FROM dept50;

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2);  ← 바로 변경 가능

ALTER TABLE dept50

MODIFY hire_date TIMESTAMP(2) with time zone;  ← with time zone 속성은 테이블에 값이 들어있으면 불가

* Interval : 간격

CREATE TABLE timetest2

(a interval year to month,

 b interval day to second);

INSERT INTO timetest2

VALUES(interval '5-10' year to month,  ← 5년 10개월

       interval '90 12:30:30' day to second);  ← 90일 12시간 30분 30초

SELECT systimestamp+a, systimestamp+b

FROM dual, timetest2;

* extract : 날짜 함수 (TO_CHAR 등으로 변환하지 않고 날짜 그대로 추출)

* TO_YMINTERVAL, TO_DSINTERVAL

* Daylight Saving Time (DST, 일광시간절약제, 서머타임)

* 고급 서브쿼리

>> Multiple-column Subquery

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE (manager_id, department_id) IN (SELECT manager_id, department_id

                                      FROM empl_demo

                                      WHERE first_name = 'John')

                                      AND first_name <> 'John';

이 문장과 아래의 문장은 다르다.

SELECT employee_id, manager_id, department_id

FROM empl_demo

WHERE manager_id IN

                 (SELECT manager_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND department_id IN

                 (SELECT department_id

                  FROM empl_demo

                  WHERE first_name = 'John')

AND first_name <> 'John';

※ IN이 AND보다 우선순위가 높다.

* Scalar Subqueries: Examples

SELECT employee_id, last_name,

       (CASE

        WHEN department_id = (SELECT department_id FROM departments

                              WHERE location_id = 1800)

        THEN 'Canada' ELSE 'USA' END) location

FROM employees;

SELECT employee_id, last_name

FROM employees e

ORDER BY (SELECT department_name

FROM departments d

WHERE e.department_id = d.department_id);

* 상호 관련(Correlated) 서브쿼리

- 메인 쿼리의 행이 다 떨어질 때까지 서브쿼리를 실행하여 비교하는 방법으로 수행됨.

- 구조가 좋지 않은 대표적인 SQL 중 하나.

ex)

SELECT last_name, salary, department_id

FROM employees outer_table

WHERE salary > (SELECT AVG(salary)

                FROM employees inner_table

                WHERE inner_table.department_id = outer_table.department_id);

-> 서브쿼리가 여러 번 실행된다.

※ 성능을 좋게 하는 SQL 문장 튜닝의 예 >

SELECT e.employee_id, e.last_name, e.salary, b.avg_sal, e.department_id

FROM employees e JOIN (SELECT department_id, AVG(salary) avg_sal

                       FROM employees

                       GROUP BY department_id) b

ON (e.department_id = b.department_id)

WHERE e.salary > b.avg_sal;

-> 조건을 FROM절에 작성.

* EXISTS 연산자 : 상호 관련 서브쿼리

- 조건에 맞는 것이 발견되면 추가로 더 이상 찾지 않는다.

- 성능을 개선할 수 있음.

ex)

SELECT employee_id, last_name, job_id, department_id

FROM employees outer

WHERE EXISTS (SELECT 'X'

              FROM employees

              WHERE manager_id = outer.employee_id);

* Correlated UPDATE : 상호 관련 업데이트

실습)

SELECT * FROM dept50;

ALTER TABLE dept50

ADD salary NUMBER(10);  ← 행을 추가한 후,

UPDATE dept50 d

SET salary = (SELECT salary FROM employees e

              WHERE e.employee_id = d.employee_id);

← 데이터를 기존에 있던 테이블과 똑같이 넣고 싶을 때 사용.

* Correlated DELETE : 상호 관련 삭제

* WITH문 : 인라인뷰를 확장한 기능.

ex)

WITH

dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total

               FROM employees e JOIN departments d

               ON e.department_id = d.department_id

               GROUP BY d.department_name),

avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg

             FROM dept_costs)

SELECT * FROM dept_costs

WHERE dept_total > (SELECT dept_avg

                    FROM avg_cost)

ORDER BY department_name;