CREATE SEQUENCE sequence_name
[START WITH n]
[INCREMENT by n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
create sequence depts_seq
start with 91
increment by 1
maxvalue 100
nocycle
nocache;
2. 시퀀스 확인
- USER_SEQUENCES 데이터 사전 뷰에서 시퀀스 값을 검사
- LAST_NUMBER 열은 다음 이용 가능한 시퀀스 번호 출력
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;
3. NEXTVAL과 CURRVAL
- NEXTVAL은 다음 사용 가느한 시퀀스 값을 반환
- 시퀀스가 참조될 때마다 사용자에게 유일한 값을 반환
- CURRVAL이 현재 시퀀스 값 반환
- CURRVAL이 참조되기 전에 NEXTVAL 이 먼저 이용되어야 함
4. 시퀀스 사용
insert into depts(deptno, dname, loc)
values (depts_seq.NEXTVAL, 'MARKETING', 'SAN DIEGO');
select * from depts;
select depts_seq.currval
from dual;
5. 시퀀스 수정
ALTER SEQUENCE sequence_name
[INCREMENT by n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
alter sequence depts_seq
maxvalue 99999;
6. 시퀀스 삭제
- DROP SEQUENCE 문장을 사용하여 데이터 사전에서 시퀀스 제거
- 한 번 제거되었다면 시퀀스는 더는 참조될 수 없음.
drop sequence sequence_name;
인덱스
- 인덱스는 테이블이나 클러스터에서 쓰이는 선택적인 객체
- DB 테이블에서 원하는 레코드를 빨리 찾아갈 수 있음
1. 인덱스 생성
- 자동 : 유일인덱스는 테이블 정의 primary key 또는 unique 제약조건 정의 시 자동 생성
- 수동 : 사용자는 행에 대한 엑세스 시간을 줄이기 위해 열에서 유일하지 않은 인덱스 생성 가능
CREATE [UNIQUE | BITMAP] INDEX index_name
ON table_name(col1, col2);
create index emps_first_name_idx
on emps(first_name);
2. 인덱스 삭제
DROP INDEX emps_first_name_idx;
동의어
- 객체를 위한 대체 이름 제공
CREATE [PUBLIC] SYNONYM [schema.]synonym
FOR [schema.]object;
- 같은 데이터를 다른 뷰로 나타낼 수 있으므로 같은 데이터에 대해 동시에 여러 사용자의 다른 응용프로그램 요구를 지원해줌.
2. 단순 뷰와 복합 뷰
특징
단순 뷰
복합 뷰
테이블 수
하나
둘 이상
함수 포함
없음
있음
데이터 그룹 포함
없음
있음
뷰를 통한 DML
있음
없음
*뷰에 관한 정보
SELECT * FROM USER_VIEWS;
뷰 생성, 데이터 검색, 수정, 삭제
1. 뷰 생성 권한
select * from user_role_privs; --현재 사용자의 롤
select * from user_sys_privs; -- 현재 사용자에게 주어진 권한
2. 뷰 생성
- CREATE VIEW 문장 내에서 서브쿼리 작성
- 서브쿼리는 조인 구문 포함 가능
- CREATE VIEW 절에서 열 별칭은 서브쿼리에서의 열과 같은 순서로 나열됨.
CREATE [OR REPLACE]
[FORCE | NOFORCE]
VIEW view_name[(alias[, alias ...])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY]
1) 뷰 생성
create view emp_view_dept60
as select employee_id, first_name, last_name, job_id, salary
from employees
where department_id=60;
select * from emp_view_dept60;
2) 뷰 생성 서브쿼리에서 별칭 사용
create view emp_dept60_salary
as select
employee_id as empno,
first_name || ' ' || last_name as anme,
salary as monthly_salary
from employees
where department_id=60;
create view emp_dept60_salary (empno, name, monthly_salary)
as select
employee_id,
first_name || ' ' || last_name,
salary
from employees
where department_id=60;
select * from emp_dept60_salary;
3. 뷰 질의
select * from user_views;
4. 뷰 수정
- CREATE OR REPLACE VIEW 절을 사용하여 뷰를 수정
- ALTER 사용X
CREATE OR REPLACE VIEW veiw_name...
create or replace view emp_dept60_salary
as select
employee_id as empno,
first_name||' '||last_name as name,
job_id job,
salary
from employees
where department_id=60;
desc emp_dept60_salary;
5. 복합 뷰 생성
- 두 개 이상 테이블로부터 값을 출력하는 뷰를 생성
create view emp_view
as select
e.employee_id as id,
e.first_name as name,
d.department_name as department,
j.job_title as job
from employees e
left join departments d on e.department_id=d.department_id
join jobs j on e.job_id=j.job_id;
select * from emp_view;
6. 뷰 삭제
- 뷰는 데이터베이스에서 기본 테이블을 기반으로 하므로 데이터 손실 없이 삭제 가능
DROP VIEW view_name;
뷰를 이용한 DML 연산
1. 뷰에서 DML 연산 실행 규칙
- 단순 뷰에서 DML 연산 수행가능
- 뷰가 다음을 포함한다면 행 제거 불가능(그룹함수, GROUP BY, DISTINCT)
- 뷰가 다음을 포함한다면 데이터 수정 불가능(행제거불가표현, 표현식, ROWNUM)
- 뷰가 다음을 포함한다면 데이터 추가 불가능(행제거불가조건, 수정불가조건, NOTNULL)
create table emps as select * from employees;
1) 데이터 조작이 가능한 경우
- 단순 뷰에서 DML 연산수행 가능
create or replace view emp_dept60
as select * from emps where department_id=60;
delete from emp_dept60 where employee_id=104;
select * from emps where employee_id=104;
2) 행 제거가 안 되는 경우
create or replace view emp_dept60
as select distinct * from emps where department_id=60;
delete from emp_dept60 where employee_id=60;
3) 데이터 수정이 안 되는 경우
create or replace view emp_dept60
as select
employee_id,
first_name||' '||last_name as name,
salary*12 as annual_Salary
from emps where department_id=60;
update emp_dept60 set annual_salary=annual_salary*1.1
where employee_id=106;
4) 데이터 입력이 안 되는 경우
create or replace view emp_dept60
as select
employee_id,
first_name,
last_name,
email,
salary
from emps where department_id=60;
insert into demp_dept60
values(500,'JinKyoung','Heo', 'HEOJK',8000);
2. WITH CHECK POINT
- 뷰에 대한 DML연산이 뷰의 조건을 만족할 때만 수행하게 함
create or replace view emp_dept60
as select employee_id, first_name, hire_Date, salary, department_id
from emps
where department_id=60
with check option;
update emp_dept60
set department_id=10
where employee_id=105;
3. WITH READ ONLY
- DML 연산을 수행할 수 없게 함
- DML 수행 시 에러 발생
create or replace view emp_dept60
as select employee_id, first_name, hire_Date, salary, department_id
from emps
where department_id=60
with read only;
delete from emp_dept60
where employee_id=105;
인라인 뷰(Inline View)
- SQL 구문에서 사용 가능한 별칭(상관 이름)을 사용하는 서브 쿼리
- 기본질의의 FROM 절에 명명된 서브쿼리를 사용하는 것과 유사
- 객체가 아님.
select row_number, first_name, salary
from ( select first_name,salary,
row_number() over(order by salary desc) as row_number
from employees)
where row_number between 1 and 10;
- commit/rollback으로 명시적으로 종료하거나 DDL/DCL 로 자동커밋되어 종료될 수 있음
문장
설명
COMMIT
미결정 데이터를 영구적으로 변경. 현재 트랜잭션 종료
SAVEPOINT savepoint_name
현재 트랜잭션 내에 savepoint 표시
ROLLBACK [[TO [SAVEPOIT]]] savepoint_name]
모든 미결정 데이터 변경을 버림. 현재의 트랜잭션 종료.
1. 암시적 트랜잭션 종료
* 자동 커밋
- DDL 문장 완료
- DCL 문장 완료
- 명시적 COMMIT / ROLLBACK 없이 SQL plus정상 종료할 때
* 자동 롤백
- SQL plus 비정상적 종료시
- 시스템 실패 시
create table emp_temp as select * from employees;
delete emp_temp where department_id=20;
savepoint svpnt_del_20;
delete emp_temp where department_id=50;
savepoint svpnt_del_50;
delete emp_temp where department_id=60;
rollback to savepoint svpnt_del_50;
CREATE TABLE emp1 AS SELECT * FROM employees;
SELECT count(*) from emp1;
create table emp2 as select * from employees where 1=2;
select count(*) from emp2;
INSERT
INSERT INtO ( col1, col2,...)
VALUES (val1, val2, ...)
- col과 val은 순서를 맞춰야 함.
- 열 이름이 생략될 경우 모든 값을 다 넣어야 함.
- 값을 넣고 싶지 않은 부분은 null을 넣음
1. 테이블 구조 확인
DESC tablename;
2. 새로운 행 삽입
- 각각의 열에 대한 값을 포함하는 새로운 행 삽입
- 테이블에 있는 열의 티폴트 순서로 값 나열
- INSERT 절에서 열을 선택적으로 나열
- 문자와 날짜 값은 단일 따옴표 내에 둠.
INSERT INTO departments
VALUES(280, 'Data Analytics', null, 1700);
=>실행을 취소하려면 ROLLBACK; 사용(커밋했을 때로)
3. 다른 테이블로부터 행 복사
- 서브쿼리로 INSERT 문장 작성
- VALUES 절 사용X
- 서브쿼리의 열 수와 INSERT절의 열 수는 일치해야 함.
INSERT INTO table(col1, col2, ...)
select_sub_query;
CREATE TABLE MANAGERS As
select * from employees where 1=2;
insert into managers
select * from employees where job_id like '%MAN';
UPDATE
- update 문장으로 기존의 행 갱신
- 하나 이상의 행을 갱신 가능
create table emps as select * from employees;
alter table emps
add(constraint emps_emp_id_ppk primary key(employee_id),
constraint emps_manager_id_fk foreign key(manager_id)
references emps(employee_id));
1. 테이블 행 갱신
select employee_id, first_name, salary
from emps
where employee_id=103;
update emps
set salary=salary*1.1
where employee_id=103;
2. 서브쿼리로 다중 열 갱신
UPDATE table
SET (col1, col2, ...) =
(SELECT col1, col2, ...
FROM table
WHERE condition)
WHERE condition;
update emps
set(job_id, salary, manager_id) =
(select job_id, salary, manager_id
from emps
where employee_id = 108)
where employee_id=109;
DELETE
DELETE [FROM] table
[WHERE condition];
1. 행 삭제
delete from emps
where employee_id = 104;
delete from emps
where emplyee_id=103;
-- 104번 사원의 매니저는 103이므로 제약조건때문에 삭제되지 않음.
2. 다른 테이블을 이용한 행 삭제
create table depts as
select * from departments;
delete from emps
where department_id=
(select department_id
from depts
where department_name='Shipping');
--45개 행 이(가) 삭제되었습니다.
3. RETURNING
- DML 조작 시 특정 값을 임시로 저장할 수 잇음
- Oracle 10g에 추가
variable emp_name varchar2(50);
variable emp_sal number;
variable;
delete emps
where employee_id=109
returning first_name, salary into :emp_name, :emp_sal;
select * from emps where employee_id=109;
print emp_name;
print emp_sal;
MERGE
- INSERT 문과 UPDATE 문을 동시에 사용할 수 있습니다.
- Oracle 9i에 추가되었습니다.
MERGE INTO table[alias]
USING (target | view | subquery) [alias]
ON(join_condition)
WHEN MATCHED THEN
UPDATE SET col1=val1
WHEN NOT MATCHED THEN
INSERT(column_lists) VALUES(value_lists);
create table emps_it as select * from employees where 1=2;
merge into emps_it a
using (select * from employees where job_id='IT_PROG') b
on (a.employee_id = b.employee_id)
when matched then
update set
a.phone_number =b.phone_number,
a.hire_date=hire_date,
a.job_id = b.job_id,
a.salary=b.salary,
a.commission_pct = b.commission_pct,
a.manager_id = b.manager_id,
a.department_id = b.department_id
when not matched then
insert values
(b.employee_id, b.first_name, b.last_name, b.email,
b.phone_number, b.hire_date, b.job_id, b.salary,
b.commission_pct, b.manager_id, b.department_id);
Multiple INSERT
INSERT [ALL | FISRT]
[[WHEN condition1 THEN]
INTO [table1] VALUES(col1, col2, ...)]
[[WHEN condition2 THEN]
INTO [table2] VALUES(col1, col2, ...)]
[ELSE
INTO [table3] VALUES (col1, col2)]
1. UNCONDITIONAL INSERT ALL
- 조건과 상관없이 기술된 여러 개의 테이블에 데이터 입력
insert all
into emp1
values (300, 'Kildong', 'Hong', 'KHONG', '011.624.7902',
TO_DATE('2015-05-11','yyyy-mm-dd'), 'IT_PROG', 6000, null, 100, 90)
into emp2
values (400, 'Kilseo', 'Hong', 'KSHONG', '011.324.7952',
TO_DATE('2015-06-20','yyyy-mm-dd'), 'IT_PROG', 5500, null, 100, 90)
SELECT * FROM dual;
create table emp_salary as
select employee_id, first_name, salary, commission_pct
from employees
where 1=2;
create table emp_hire_date as
select employee_id, first_name, hire_Date, department_id
from employees
where 1=2;
insert all
into emp_salary values
(employee_id, first_name, salary, commission_pct)
into emp_hire_date values
(employee_id, first_name, hire_date, department_id)
select * from employees;
--218개 행 이(가) 삽입되었습니다.
2. CONDITIONAL INSERT ALL
- 특정 조건들을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 난어 삽입
create table emp_10 as select * from employees where 1=2;
create table emp_20 as select * from employees where 1=2;
insert all
when department_id=10 then
into emp_10 values
(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
when department_id=20 then
into emp_20 values
(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
select * from employees;
--3개 행 이(가) 삽입되었습니다.
select * from emp_10;
select * from emp_20;
3. CONDITIONAL INSERT FIRST
- 첫번째 when 절에서 조건을 만족하는 경우 다음의 when절은 수행하지 않음.
create table emp_sal5000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sa10000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal15000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal20000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal25000 as
select employee_id, first_name, salary from employees where 1=2;
insert first
when salary <=5000 then
into emp_sal5000 values(employee_id, first_name, salary)
when salary <=10000 then
into emp_sal10000 values(employee_id, first_name, salary)
when salary <=15000 then
into emp_sal15000 values(employee_id, first_name, salary)
when salary <=20000 then
into emp_sal20000 values(employee_id, first_name, salary)
when salary <=25000 then
into emp_sal25000 values(employee_id, first_name, salary)
select employee_id, first_name, salary from employees;
--109개 행 이(가) 삽입되었습니다.
select count(*) from emp_sal5000; --49
select count(*) from emp_sal10000; --45
select count(*) from emp_sal15000; --12
select count(*) from emp_sal20000; --2
select count(*) from emp_sal25000; --1
4. PIVOTING INSERT
- 여러개의 into절을 사용하지만 그 뒤에 오는 테이블은 모두 동일
- 비관계형DB를 관계형DB로 바꿀 때 사용
insert all
into sales_log
values(employee_id, week_id, 'SALES_MON', sales_mon)
into sales_log
values(employee_id, week_id, 'SALES_TUE', sales_tue)
into sales_log
values(employee_id, week_id, 'SALES_WED', sales_wed)
into sales_log
values(employee_id, week_id, 'SALES_THU', sales_thu)
into sales_log
values(employee_id, week_id, 'SALES_FRI', sales_fri)
select employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri
from sales;
--10개 행 이(가) 삽입되었습니다.
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
* 서브쿼리 사용시 규칙
- 서브쿼리는 괄호로 둘러싸여야 함(CTAS 제외)
- 비교 연산자의 오른쪽에 위치
- 두 종류의 비교연산자 사용(단일행, 다중행)
select first_name, salary
from employees
where salary > (select salary from employees where first_name='Nancy');
단일행 서브쿼리
- 내부 select 문장으로부터 하나의 행을 반환하는 질의
연산자
설명
=
같다
>
보다 크다
>=
보다 크거나 같다
<
작다
<=
작거나 같다
<>, !=
같지 않다
select first_name, job_id, hire_date
from employees
where job_id = (select job_id from employees where employee_id=103);
다중행 서브쿼리
- 서브쿼리의 결과가 2개 행 이상일 경우
연산자
설명
IN
목록의 어떤 값과 같은지 확인
EXISTS
값이 있는지 확인. + 서브쿼리만 가능
ANY, SOME
비교 연산자. 하나만 만족
ALL
비교 연산자. 모든 값과 비교해서 만족
ALL / ANY 차이점
< ANY : 가장 큰 값보다 작으면 됨 > ANY : 가장 작은 값보다 크면 됨 < ALL : 가장 작은 값보다 작으면 됨 > ALL : 가장 큰 값보다 크면 됨 = ANY : IN과 같은 역할
select first_name, salary
from employees
where salary > (select salary from employees where first_name='David');
ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action
select first_name, salary
from employees
where salary > any (select salary from employees where first_name='David');
select first_name, department_id, job_id
from employees
where department_id in (select department_id
from employees
where first_name = 'David');
--in(60,80)과 같음
상호연관 서브쿼리
- 한 개의 행을 비교할 때마다 결과가 메인쿼리로 반환됨
- 한 행을 처리할 때마다 서브쿼리로 주고 값을 처리한 후 결과를 다시 메인쿼리로 전달=> 성능 저하
- 메인쿼리와 서브쿼리 사이에 결과를 교한하기 위해 서브쿼리의 where 조건절에서 메인쿼리 테이블과 연결
select first_name, salary
from employees a
where salary > (select avg(salary)
from employees b
where b.department_id = a.department_id);
스칼라 서브쿼리
- SELECT 절에 사용하는 서브쿼리
select first_name, (select department_name from departments d where d.department_id = e.department_id) department_name
from employees e
order by first_name;
select first_name, department_name
from employees e
join departments d on (e.department_id = d.department_id)
order by first_name;
인라인 뷰
- FROM절에 서브쿼리가 온 것
select row_number, first_name, salary
from (select first_name, salary,
row_number() over(order by salary desc) as row_number
from employees)
where row_number between 1 and 10;
3중 쿼리와 Top-N 쿼리
- 서브쿼리 안에 또 다른 서브쿼리를 갖는 것
select rownum, first_name, salary
from (select first_name, salary from employees
order by salary desc)
where rownum between 1 and 10;
계층형 쿼리
- 수직적 관게를 맺고 있는 행들의 계층형 정보를 조회할 때 사용
SELECT select_list
FROM table
WHERE conditions
START WITH top_level_cindition
CONNECT BY [NOCYCLE] [PRIOR] connect_condition
ORDER SIBLING BY order_condition;
select employee_id,
lpad(' ',3*(level-1)) || first_name || ' ' ||last_name,
level
from employees
start with manager_id is null
connect by prior employee_id = manager_id;
select employee_id,
lpad(' ',3*(level-1)) || first_name || ' ' ||last_name,
level
from employees
start with employee_id=113
connect by prior manager_id=employee_id ;
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column;
1. CARTESIAN PRODUCT
- 조인 조건이 생략된 경우
- 조인 조건이 잘못된 경우
- 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행과 조인되는 경우
SELECT table1.col, table2.col
FROM table1, table2;
--카티션프로덕트발생(조인 조건이 없기 때문)
2. EQUI JOIN
- 2개 이상의 테이블이 공통된 열에 의해 논리적 결합하는 조인 기법
- WHERE 절에 사용된 열들이 동등연산자(=) 에 의해 비교
select e.first_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
3. SELF JOIN
- 자체적 테이블 조인
select e.first_name, m.first_name
from employees e, employees m
where e.manager_id = m. employee_id and e.employee_id=103;
4. NON-EQUI JOIN
- equal 연산자(=)로 조인하지 않음
select e.first_name, e.salary, j.job_title
from employees e, jobs j
where e.salary
between j.min_salary and j.max_salary
order by e.first_name;
5. OUTER JOIN
- 조인 조건을 만족하지 않는 행들도 보기 위해서 Outer 조인을 사용함
- Outer 조인 연산자는 더하기 기호(+) 임.
SELECT table.column, table.column
FROM table1, table2
WHERE table1.column(+) = table2.column;
SELECT table.column, table.column
FROM table1, table2
WHERE table1.column = table2.column(+);
select e.employee_id, e.first_name, e.hire_date,
j.start_date, j.end_date, j.job_id, j.department_id
from employees e, job_history j
where e.employee_id = j.employee_id
order by j.employee_id;
안시 조인
- 오라클 9i 버전부터 ANSI SQL 표준 문법 사용이 가능
- 조인의 형태가 FROM절에서 지정되며, 조인 조건이 ON 절 또는 USING 절에 표시
SELECT t1.col, t2, col
FROM t1
[LEFT|RIGHT|FULL][OUTER] JOIN t2
ON jooin_conditions
[WHERE conditions];
1. CROSS JOIN
- 두 개의 테이블에 대한 Cartesian Product와 같은 결과 출력
SELECT table1.column1, table2.column2
FROM table1
CROSS JOIN table2;
select employee_id, department_name
from employees
cross join departments;
2. NATURAL JOIN
- 모든 같은 이름을 갖는 열들에 대해 조인
- 지정해주지 않아도 자동으로 같은 이름가진 열에 대해 Equi조인을 수행한다.
SELECT table1.column1, table2.column2
FROM table1
NATURAL JOIN talbe2;
select first_name, job_title
from employees
natural join jobs;
3. USING JOIN
- using 절을 이용하여 원하는 열에 대해서만 선택적으로 Equi 조인을 할 수 있음.
SELET table1.column1, table2.column2
FROM table1
JOIN table2
USING (column);
select first_name, department_name
from employees
join departments
using (department_id);
4. ON JOIN
- on을 이용해 where에 들어갈 조인 조건을 적을 수 있음
SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON join_condition;
select department_name, street_Address, city, state_province
from departments d
join locations l
on d.location_id = l.location_id;
1) 여러 테이블의 조인
select e.first_name, d.department_name,
l.street_address || ', ' || l.city || ', '||
l.state_province as address
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id;
2) WHERE 절과의 혼용
select e.first_name as name,
d.department_name as department
from employees e
join departments d
on e.department_id = d.department_id
where employee_id = 103;
select e.first_name, d.department_name,
l.street_address || ', ' || l.city || ', '||
l.state_province as address
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
where employee_id=103;
3) ON절에 WHERE절의 조건 추가
select e.first_name as name,
d.department_name as department
from employees e
join departments d
on e.department_id = d.department_id and employee_id = 103;
select e.first_name, d.department_name,
l.street_address || ', ' || l.city || ', '||
l.state_province as address
from employees e
join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id and employee_id=103;
5. OUTER JOIN
SELECT table1.column, table2.column
FROM table1
[LEFT|RIGHT|FULL][OUTER]JOIN table2
ON join_conditions
select e.employee_id, e.first_name, e.hire_date,
j.start_date, j.end_date, j.job_id, j.department_id
from employees e
left outer join job_history j
on e.employee_id = j.employee_id
order by e.employee_id;
1) LEFT OUTER JOIN
- 왼쪽의 테이블이 기준
select department_name, first_name
from departments d
left join employees e
on d.manager_id = e.employee_id;
2) RIGHT OUTER JOIN
- 오른쪽 테이블이 기준
select department_name, first_name
from employees e
right join departments d
on d.manager_id = e.employee_id;
Anlytic_Funtion
OVER ( PARTITION BY column_list
ORDER BY column_list [ASC|DESC]
Windowing)
* PARTITION BY : 열의 행을 그룹화. 분석 함수로 계산될 대상 행의 그룹을 지정.
* ORDER BY : 정렬 (가장 많이 사용). 파티션 안에서의 순서를 지정.
* Windowing : 윈도우 절. 파티션으로 분할 그룹화 된 그룹에 대해 더 상세한 그룹으로 분할 할 때 사용
1. 순위 구하기 함수 - RANK, DENSE_RANK, ROW_NUMBER
RNAK
순위 결정(중복순위 계산o) 같은 값이면 1,2,2,4위 순
DENSE_RANK
순위 결정(중복순위 계산x) 같은 값이면 1,2,2,3,4위 순
ROW_NUMBER
조건을 만족하는 모든 행의 번호를 제공 - select절에서 순서를 매기기 때문에 - order by로 정렬을 하면 순서가 바뀔 수 잇음 - DB에 데이터가 물리적으로 저장된 순서에 따라 다름.
select employee_id, department_id, salary,
rank() over(order by salary desc) sal_rank,
dense_rank() over(order by salary desc) sal_dense_rank,
row_number() over(order by salary desc) sal_number
from employees;
2. 가상순위와 분포 - CUME_DIST, PERCENT_RANK
CUME_DIST
최대값 1을 기준으로 분산된 값 제공. 첫번째 0이 아님.
PERCENT_RANK
최대값 1을 기준으로 백분율 값 제공. 첫번째는 0
select employee_id, department_id,salary,
cume_dist() over(order by salary desc) sal_cume_dist,
percent_rank() over(order by salary desc) sal_pct_rank
from employees;
3. 비율 함수 - RATIO_TO_REPORT
RATIO_TO_REPORT
해당 컬럼 값의 총 값 중 차지하는 비율을 백분율로 표현. 소수점으로 제공
select first_name, salary,
round(ratio_to_report(salary) over(), 4) as salary_ratio
from employees
where job_id ='IT_PROG';
4. 분배 함수 - NTILE
NTILE
전체 데이터의 분포를 n개의 구간으로 나누어 표시 만약 45명을 10개씩 나누면 4.5이므로 앞쪽부터 하나씩 더 가져감
select employee_id, department_id, salary,
ntile(10) over(order by salary desc) sal_quart_tile
from employees
where department_id='50';
5. LAG, LEAD
- 윈도우 구간 지정
LAG
윈도우의 이전 n번째 행의 값을 가져옴 LAG(salary,2,0) : salary에서 현재 행 이전 2번째 행의 값을 가져오고 값이 없으면 0 출력.
LEAD
윈도우의 이후 n번째 행의 값을 가져올 수 있음 - LEAD(hire_date, 1,0) : 현재 행 기준 다음 입사일 값을 가져오고 없으면 0 출력
select employee_id,
lag(salary,1,0) over(order by salary) as lower_sal,
salary,
lead(salary,1,0) over(order by salary) as higher_sal
from employees
order by salary;
6. LISTAGG
LISTAGG(expression, 'delimiter')
WITHIN GROUP(ORDER BY column)
LISTAGG
함수의 expression을 delimiter로 연결해 여러 행을 하나의 행으로 변환해 출력하는 함수
select department_id, first_name
from employees
group by department_id;
--에러
select department_id,
listagg(first_name,', ') within group(order by hire_date)
from employees
group by department_id;
윈도우 절
- 파티션으로 분할된 그룹에 대해 다시 그룹을 만드는 역할
{rows|range}
{BETWEEN {
UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr{PRECEDING|FOLLOWING}
}
AND {
UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr{PRECEDING|FOLLOWING}
}
|{UNBOUNDED PRECEDING
| CURRENT ROW
| value_Expr PRECEDING
}
}
select department_id, first_name, salary,
sum(salary) over(partition by department_id
order by salary
rows between unbounded preceding
and current row) as sum_rows
from employees;
select department_id, first_name, salary,
sum(salary) over(partition by department_id
order by salary
range between unbounded preceding
and current row) as sum_rows
from employees;
select avg(salary), regr_avgx(commission_pct, salary), regr_avgy(commission_pct, salary)
from employees;
2. REGR_COUNT(y,x)
- 두 인수의 값이 not null인 쌍 수를 계산
select distinct department_id,
regr_count(manager_id, department_id)
over (partition by department_id) "REGR_COUNT"
from employees
order by department_id;
3. REGR_SLOPE(y,x), REGR_INTERCEPT(y,x)
* slope: 회귀 직선의 기울기 측정(weight)
- COVAR_POP(y,x) / VAR_POP(x)
*intercept : 회귀 직선의 y절편(bias)
- AVG(y) - REGR_SLOPE(y,x) * AVG(x)
select
job_id, employee_id, salary,
round(sysdate-hire_date) "working_day",
round(regr_slope(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_slope",
round(regr_intercept(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_intercept"
from employees
where department_id = 80
order by job_id, employee_id;
select
distinct
job_id,
round(regr_slope(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_slope",
round(regr_intercept(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_intercept",
round(regr_r2(salary, sysdate-hire_date)
over(partition by job_id),2) "regr_r2"
from employees
where department_id=80;
=> 두 회귀모형 중 SA_MAN이 SA_REP보다 유용성이 더 높다고 판단할 수 있다.
피벗 테이블
1. 관계형 데이터베이스 형식과 스프레드시트 형식
create table sale_log(
employee_id number(6),
week_id number(2),
week_day varchar(10),
quantity number(8,2)
);
insert into sale_log values(1101, 4 , 'sales_mon', 100);
insert into sale_log values(1101, 4 , 'sales_tue', 150);
insert into sale_log values(1101, 4 , 'sales_wed', 80);
insert into sale_log values(1101, 4 , 'sales_thu', 60);
insert into sale_log values(1101, 4 , 'sales_fri', 120);
insert into sale_log values(1102, 5 , 'sales_mon', 300);
insert into sale_log values(1102, 5 , 'sales_tue', 300);
insert into sale_log values(1102, 5 , 'sales_wed', 230);
insert into sale_log values(1102, 5 , 'sales_thu', 120);
insert into sale_log values(1102, 5 , 'sales_fri', 150);
commit;
select * from sale_log;
SELECT col1, col2, ...var_col1, ... var_coln
FROM tables
PIVOT
(
aggregate_function(value_column)
FOR variable_column
IN(expr1 [[AS] var_col1], ... , expr_n [[AS] var_coln])
)
WHERE conditions
ORDER BY expression [ASC | DESC];
select * from sale_log
pivot(
sum(quantity)
for week_day in(
'sales_mon' as sales_mon,
'sales_tue' as sales_tue,
'sales_wed' as sales_wed,
'sales_thu' as sales_thu,
'sales_fri' as sales_fri
)
)
order by employee_id, week_id;
3. UNPIVOT
SELECT col1, ... , variable_column, value_column
FROM tables
UNPIVOT(
value_column
FOR variable_column
IN(expr1, expr2, ..., expr_n)
)
WHERE conditions
ORDER BY expression [ASC | DESC];
select employee_id, week_id, week_day, quantity
from sales
unpivot
(
quantity
for week_day
in(sales_mon, sales_tue, sales_wed, sales_thu, sales_fri)
);
select avg(salary),sum(salary),min(salary),max(salary)
from employees
where job_id like 'SA%';
select min(hire_date), max(hire_date)
from employees;
-- 날짜로 min,max가능
select min(first_name), max(last_name)
from employees;
select max(salary)
from employees;
2. COUNT
count(*)
count(expr)
select count(*) from employees;
select count(commission_pct) from employees;
3. STDDEV, VARIANCE
select sum(salary) as 합계,
round(avg(salary),2) as 평균,
round(stddev(salary),2) as 표준편차,
round(variance(salary),2) as 분산
from employees;
4. 그룹 함수와 NULL 값
- count(*)를 제외한 모든 그룹 함수는 열에 있는 NULL값을 연산에서 제외
select
round(sum(salary*commission_pct),2) as sum_bonus,
count(*) count,
round(avg(salary*commission_pct),2) as avg_bonus1,
round(sum(salary*commission_pct)/count(*),2) as avg_bonus2
from employees;
select avg(nvl(salary*commission_pct,0))
from employees;
GROUP BY
SELECT column, group_function(column)
FROM table
[WHERE condition(S)]
[GROUP BY group_by_expression]
[ORDER BY {column | expr[[ASC]|DESC],...};
1. GROUP BY 사용
select department_id, avg(salary)
from employees
group by department_id;
2. 하나 이상의 열로 그룹화
select department_id, job_id, sum(salary)
from employees
group by department_id, job_id;
3. 그룹 함수를 잘못 사용한 질의
- select 절의 그룹 함수가 아닌 모든 열이나 표현식은 group by 절에 있어야 한다.
- where 절을 사용해 그룹을 제한할 수 없다. having 사용
HAVING
- 그룹 제한하기 위해 사용
SELECT coulmn, group_function(column)
FROM table
[WHERE condition(s)]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY {column|expr[[ASC]|DESC]]}];
1. HAVING 사용
select department_id, avg(salary)
from employees
group by department_id
having avg(salary) > 2000;
select job_id, avg(salary) payroll
from employees
where job_id not like 'SA%'
group by job_id
having avg(salary) > 8000
order by avg(salary) desc;
GROUPING SETS
SELECT coulmn, group_function(column)
FROM table
[WHERE condition(s)]
[GROUP BY [GROUPING SETS] (group_by_expression)]
[HAVING group_condition]
[ORDER BY {column|expr[[ASC]|DESC]]}];
select department_id, job_id, round(avg(salary),2)
from employees
group by grouping sets(department_id, job_id)
order by department_id, job_id;
select department_id, job_id, manager_id, round(avg(salary),2) as avg_Sal
from employees
group by grouping sets((department_id, job_id),
(job_id, manager_id))
order by department_id, job_id, manager_id;
ROLLUP, CUBE
1. ROLLUP, CUBE사용
select department_id,job_id, round(Avg(salary),2), count(*)
from employees
group by department_id, job_id
order by department_id, job_id;
--각 부서의 직업별 평균만 알 수 있다.
--각 부서의 전체 평균을 알고싶다면 rollup사용
select department_id,job_id, round(Avg(salary),2), count(*)
from employees
group by rollup(department_id, job_id)
order by department_id, job_id;
- 여기에 직무별 평균과 사원의 수도 출력하고 싶으면 cube 사용
select department_id,job_id, round(Avg(salary),2), count(*)
from employees
group by cube(department_id, job_id)
order by department_id, job_id;
GROUPING
- ROLLUP, CUBE, GROUPING SETS 등 GROUP BY 확장 연산으로 소계의 일부로 생성된 null값일 경우 1을 반환
- 저장된 null값이거나 다른 값일 경우 0 반환
select
decode(grouping(department_id), 1, '소계',
to_Char(department_id)) as 부서,
decode(grouping(job_id),1,'소계',job_id) as 직무,
round(avg(salary),2) as 평균,
count(*) as 사원의수
from employees
group by cube(department_id, job_id)
order by 부서, 직무;
GROUPING_ID
- GROUPING_ID는 행과 연관된 GROUPING 비트 벡터에 해당하는 숫자를 반환
- GROUPING_ID를 사용하면 그룹화 수준 식별 가능
GROUPING_ID(column, column2,...)
select
decode(grouping_id(department_id, job_id),
2, '소계', 3, '합계',
to_char(department_id)) as 부서번호,
decode(grouping_id(department_id, job_id),
1,'소계',3,'합계',
job_id) as 직무,
grouping_id(department_id, job_id) as gid,
round(avg(salary),2) as 평균,
count(*) as 사원수
from employees
group by cube(department_id, job_id)
order by 부서번호, 직무;
