웅지의 개발새발

시퀀스

- 자동으로 유일한 번호 생성

- 공유 가능한 객체

- 주로 기본키에 저장될 값을 만드는데 사용

- 애플리케이션 코드 대체

- 메모리에 캐쉬되면 시퀀스 값을 사용하는 효율성 향상시킴.

1. 시퀀스 생성

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;

뷰(View)

- 뷰(View)는 테이블 또는 다른 뷰를 기초로 하는 논리적 테이블이다.

1. 뷰의 사용 목적

- 접근제어를 통한 자동 보안이 제공되므로 데이터베이스 액세스를 제한하기 위해 사용

- 복잡한 질의를 쉽게 만들어주기 때문에 사용자의 데이터 관리를 간단하게 해줌

- 같은 데이터를 다른 뷰로 나타낼 수 있으므로 같은 데이터에 대해 동시에 여러 사용자의 다른 응용프로그램 요구를 지원해줌.

2. 단순 뷰와 복합 뷰

*뷰에 관한 정보

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;

제약조건이란?

- 테이블의 해당 열에 사용자가 원치 않은 데이터가 입력/수정/삭제되는 것을 방지하기 위한 조건

- 제약조건에 종속성이 존재할 경우 테이블의 삭제를 방지

select * from user_constraints;

 1. 제약조건 정의

- 테이블 레벨 제약조건을 정의할 수 있음

- 열 레벨 제약조건을 정의할 수 있음

CREATE TABLE [schema.] table(
	column datatype [DEFUALT expr] [column_constraint],
    ...,
    [table_constraint]
);

2. 열 레벨 제약조건

- 열별로 제약 조건 정의

- 무결성 제약조건의 어떤 유형도 정의 가능.

column datatype [DEFUALT expr]
[CONSTARINT constraint_name] constraint_type,

3. 테이블 레벨 제약조건

- 하나 이상의 열을 참조하고, 테이블의 열 정의와는 개별적으로 정의

- not null은 열레벨 제약조건으로만 가능

제약조건 종류

1. NOT NULL

- null 값을 가질 수 없음

- 열 레벨 제약조건으로만 설정 가능

2. PRIMARY KEY

- 값들이 유일해야 함

- null값을 가질 수 없음

3. UNIQUE

- 값들이 유일해야 함

- null값 가질 수 있음

4. FOREIGN KEY

- 테이블간의 관계 설정

5. CHECK

- 각 행을 만족해야 하는 조건 정의

제약조건 관리

- 제약 조건의 추가 또는 삭제는 가능.

- 수정을 불가능

- 제약조건의 활서화/비활성화 가능

- MODIFY 절을 사용해 not null조건 추가

1. 제약조건 추가

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] type(column);

2. 제약조건 조회

- 모든 제약조건 정의와 이름을 보기 위해 user_constraints 테이블을 질의

- user_cons_columns 뷰에서 제약조건 이름과 관련된 열 보기 가능

3. 제약조건 삭제

ALTER TABLE table_name
DROP PRIMARY KEY
| UNIQUE (column)
| CONSTRAINT constraint_name
[CASCADE];

4. 제약조건 비활성화

- 무결성 제약조건을 비활성화하기 위해 alter table문장의 disable 절을 실행

- 종속적인 무결성 제약조건을 비활성화하기 위해 cascade 옵션 적용

ALTER TABLE table_name
DISABLE [NOVALIDATE | VALIDATE]
CONSTRAINT constraint_name [CASCADE];

5. 제약조건 활성화

- 무결성 제약조건 활성화

ALTER TABLE table_name
ENABLE [NOVALIDATE | VALIDATE]
CONSTRAINT constraint_name;

테이블 생성

1. 이름 규칙

- 테이블 이름과 열 이름은 문자로 시작

- 영어 대/소문자, 숫자, _ , $(보통 뷰에 사용), # 문자만 포함 가능

- 사용자에 의해 소유된 다른 객체(테이블, 뷰, 인덱스 등) 의 이름과 중복은 불가능

- 오라클 예약어 불가능

- 대소문자 구분X

2. 데이터 타입

3. CREATE TABLE

CREATE TABLE[schema.] table_name(
	column_name data_type [DEFAULT expr],
    ...
);

4. 서브쿼리를 사용한 테이블 생성

CREATE TABLE table_name AS SELECT statement;

테이블 구조 변경

1. ALTER TABLE

- ADD / MODIFY 로 열을 변경할 수 있다.

ALTER TABLE table_name
ADD ( column data_type [DEFAULT expr]
	[, column data_type] ...);
    
ALTER TABLE table_name
MODIFY (column data_type [DEFAULT expr]
	[, column data_type] ...);

create table emp_dept50
as select employee_id, first_name, salary*12 as ann_sal, hire_Date
from employees
where department_id=50;

2. 열 추가

- ADD 절을 사용해 열을 추가

- 새로운 열이 마지막 열이 됨

ALTER TABLE emp_dept50
ADD (job VARCHAR2(10));

3. 열 수정

- MODIFY 절을 사용해 열을 ㅅ줭

- 기존의 데이터를 손상되게 크기를 조정할 수는 없음

alter table emp_dept50
modify(first_name varchar2(10));

4. 열 삭제

- ALTER TABLE문을 DROP COLUMN 절과 함께 사용하여 테이블에서 열 삭제 가능

- ORACLE 8i 부터 가능

ALTER TABLE table_name
DROP COLUMN column_name;

alter table emp_dept50
add(job_id varchar2(10));

alter table emp_dept50
drop column job_id;

5. 열 이름 변경

- RENAME COLUMN 절을 사용하여 테이블의 열 이름을 변경할 수 있습니다.

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

alter table emp_dept50
rename column job to job_id;

6. SET UNUSED 옵션과 DROP UNUSED 옵션

- SET UNUSED 옵션을 사용하여 하나 이상의 열을 "사용되지 않았음"으로 표시

- DROP UNUSED COLUMNS 옵션을 사용해 UNUSED로 표시된 열을 제거

ALTER TABLE table
SET UNUSED [COLUMN] (column);

ALTER TABLE table
DROP UNUSED COLUMNS;

7. 객체 이름 변경

RENAME old_name TO new_name;

rename emp_dept50 to employees_dept50;
--테이블 이름이 변경되었습니다.

테이블 삭제

- 테이블의 모든 데이터와 구조가 삭제됩니다.

- 어떤 결정되지 않은 트랜잭션이 커밋됩니다.

- 모든 인덱스가 삭제됩니다.

- 이 문장은 롤백할 수 없습니다.

DROP TABLE table_name [ CASCADE CONSTRAINTS]

drop table employees_dept50;
--Table EMPLOYEES_DEPT50이(가) 삭제되었습니다.

테이블 데이터 비우기(TRUNCATE)

- TRUNCATE TABLE 문장으로 테이블의 모든 행을 삭제합니다.

- 해당 테이블에 사용된 기억공간을 해제합니다.

- TRUNCATE를 사용하여 삭제한 행을 롤백할 수 없습니다.

- TRUNCATE 대안적으로, DELETE 문장을 사용하여 행을 삭제합니다.

TRUNCATE TABLE table_name;

truncate table emp2;
--Table EMP2이(가) 잘렸습니다.

트랜잭션

- 트랜잭션 : 논리적인 작업의 단위

- 분리되어서는 안 될 작업의단위

- 실행 가능한 첫번째 sql문장이 실행될 때 시작

- commit/rollback으로 명시적으로 종료하거나 DDL/DCL 로 자동커밋되어 종료될 수 있음

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;

DML(Data Manipulation Language)

DML 문장 실행 상황

- 테이블에 새로운 행 추가

- 테이블에 있는 기존의 행을 변경

- 테이블로부터 기존의 행 제거

DML 종류

- INSERT

- UPDATE

- DELETE

예시

- 은행 DB. 저축성 예금 -> 당좌예금으로 전달

- 저축성 예금 감소

- 당좌 예금 증가

- 트랜잭션 일지에 트랜잭션 기록

=> 하나라도 실패시 모두 실패해야함.

=> 트랜잭션 보장 필요

CTAS(CREATE TABLE AS SELECT)

CREATE TABLE table AS SELECT statment

현재 있는 테이블 statement와 똑같은 구조를 갖는 tale 생

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문장이다.

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개 행 이상일 경우

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 ;

조인이란?

- 하나 이상의 테이블로부터 데이터를 질의하기 위해 조인을 사용

- WHERE 절에 조인 조건 사용

- 하나 이상의 테이블에 똑같은 열 이름이 있을 때 열 이름 앞에 테이블 이름을 붙임

- 오라클 조인과 ANSI JOIN이 있음.

오라클 JOIN

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;

3) FULL OUTER JOIN

- 왼쪽 오른쪽 모두 기준

분석 함수

- 개발 생산성 높임-빨리 응답

- SQL 튜닝으로 성능을 높임

- 쿼리를 간단하게 하여 가독성 높임

- DW 분야에 사용

- SELECT절에서 사용

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

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

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

select first_name, salary,
round(ratio_to_report(salary) over(), 4) as salary_ratio
from employees
where job_id ='IT_PROG';

4. 분배 함수 - NTILE

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

- 윈도우 구간 지정

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)

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
    	}
    }

1. 윈도우 절 사용

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;

선형 회귀 함수

{REGR_SLOPE | REGR_INTERCEPT | REGR_COUNT | REGR_R2 |
REGR_AVGX | REGR_AVGY | REGR_SXX | REGR_SYY | REGR_SXY}
(y,x)
	[OVER (analytic_clause)]

1. REGR_AVGX(y,x), REGR_AVGY(y,x)

- 각 함수의 첫 번째 인수인 x,y가 null이면 계산에 포함하지 않는다.

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;

4. REGR_R2

- 회귀 분석에 대한 결정 계수(얼마나 잘 표현했냐)를 반환

- 적합도

- 종속 변수와 독립변수 사이에 상관관계가 높을수록 1에 가까워짐.

* 반환값

- VAR_POP(x) = 0 이면 null

- VAR_POP(y) =0 이고 VAR_POP(x) !=0이면 1

- VAR_POP(y) > 0 이고 VAR(x) !=0 이면 POWER(CORR(y,x),2)

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;

create table sales(
employee_id number(6),
week_id number(2),
sales_mon number(8,2),
sales_tue number(8,2),
sales_wed number(8,2),
sales_thu number(8,2),
sales_fri number(8,2)
);

insert into sales values(1101,4,100,150,80,60,120);
insert into sales values(1102,5,300,300,230,120,150);
commit;
select * from sales;

2. PIVOT

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

그룹함수

1. AVG, SUM, MIN, MAX(평균, 합, 최소, 최대값)

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 부서번호, 직무;