230706

--서브쿼리
select *
from employees
where salary < any( select salary from employees where hire_date>'08/01/01');
--()안의 데이터중에서 하나라도 만족하는 월급 값보다 작은 salary를 가진 사람들을 출력 , 즉()안의  가장 높은 salary값보다 작으면 다 출력된다는 이야기.

select *
from employees
where salary < all( select salary from employees where hire_date>'08/01/01');
--()안의 값을 고려하여 이 값들을 다 포함하는 애들보다 작은것을 찾는것이기 때문에 여기 월급의 최저값 2200보다 작은 값을 출력한다.\

select * 
from employees
where salary > any (select max(salary) from employees group by department_id);
--()안의 최소 월급 4400의 직원보다 큰 값들만 출력

select * 
from employees
where salary > ALL (select max(salary) from employees group by department_id);
-- () 안의 최대월급 24000보다 큰 값이 출력

--인라인 뷰(Inline View)
--FROM절에 있는 서브쿼리가 인라인 뷰를 생성
--FROM 절에 직접 기술하여 효율적인 검색 가능
--FROM 절에 있는 서브쿼리에는 자주 별칭을 사용
SELECT *
FROM employees E,(SELECT department_id
                    FROM departments
                    WHERE department_name='IT') D
WHERE E.department_id = D.department_id ;   

SELECT E.last_name, E.salary, D.avg_sal
FROM employees E, ( SELECT department_id, AVG(salary) avg_sal
                    FROM employees 
                    GROUP BY department_id) D
WHERE E.department_id = D.department_id
AND E.salary > D.avg_sal;

SELECT department_name,(SELECT AVG(salary)
                        FROM employees
                        GROUP BY department_name)
FROM departments;

SELECT ROWNUM, salary
FROM (SELECT salary
        FROM employees
        ORDER BY salary DESC)
WHERE ROWNUM <=10; --rank에서 top 10 같은것을 구할때 자를때 주로 사용한다.

--결과적으로 서브쿼리는 select 절에도 from 절에도 where 절에도 사용이 가능하다.!!

----연습

SELECT E.first_name, E.last_name, E.job_id, E.salary
FROM employees E , (SELECT department_id
                    FROM departments
                    WHERE department_name ='IT'
                    ) D
WHERE E.department_id=D.department_id;

--답
SELECT first_name,last_name,job_id,salary
FROM employees
WHERE department_id=(SELECT department_id
                    FROM departments
                    WHERE department_name ='IT'
                    );


SELECT D.department_id, D.department_name
FROM departments D ,( SELECT location_id
                        FROM locations
                        WHERE state_province='California'
                     ) L
WHERE D.location_id=L.location_id;

--답

SELECT department_id,department_name
FROM departments 
WHERE location_id=( SELECT location_id
                    FROM locations
                    WHERE state_province='California'
                    );     

--답!!!(VVV)
--countries 테이블에서 region_id가 3인 country_id가 포함된
--locations 테이블의 city, state_province, street_address를 조회
--다중행인 경우 다중행 연산자로 처리 NOT IN 등등.
SELECT city,state_province,street_address
FROM locations 
WHERE country_id IN (SELECT country_id FROM countries WHERE region_id=3);

--JOIN은 왠만하면 잘 하지 말고, 
SELECT first_name,last_name,job_id,salary
FROM employees 
WHERE department_id IN (SELECT department_id
                 FROM departments
                WHERE manager_id IS NOT NULL);  

SELECT department_id,department_name
FROM departments 
WHERE location_id NOT IN (SELECT location_id
                        FROM locations
                        WHERE city LIKE 'Seattle');
                        
SELECT  L.city, L.state_province, L.street_address
FROM locations L,(SELECT country_id 
                    FROM regions R,countries C
                    WHERE region_name='Europe' 
                        AND R.region_id=C.region_id ) RC
WHERE L.country_id=RC.country_id;

--다른 답.
SELECT city,state_province, street_address
FROM locations 
WHERE country_id IN (SELECT country_id
                    FROM countries
                    WHERE region_id=(SELECT region_id
                                    FROM regions
                                    WHERE region_name='Europe')) ;


--CRUD 여러개
--여러번 인서트를 실행하고 커밋을 하는것이 좋다. (커밋은 고비용에 해당하는 연산)
SELECT * FROM countries;

INSERT INTO countries
VALUES ('KR', 'South Korea', 3);

INSERT INTO countries (country_id, country_name, region_id)
VALUES ('KP', 'North Korea', 3);

SELECT * FROM departments;

INSERT INTO departments
VALUES (280, 'Testing', 200, 1700);

INSERT INTO departments (department_id, department_name,location_id)
VALUES (290, 'Data Analytics', 1700);

SELECT * FROM jobs;
INSERT INTO jobs
VALUES ('IT DS', 'Data Scientist', 10000, 20000);
INSERT INTO jobs (job_id, job_title, min_salary, max_salary)
VALUES ('IT DA', 'Data Analyst', 10000, 20000);

----업데이트
--UPDATE 테이블
--SET 열=값, 열=값,..
--WHERE 조건식
UPDATE countries
SET country_name = 'Democratic People''s Republic of Korea' --이때  ''을 써주면 '안의 '값이 입력된다.
WHERE country_id = 'KP'; -- where을 써주지않으면 전체가  update 되는점 주의

UPDATE countries
SET country_name = 'Republic of Korea'
WHERE country_id = 'KR';

----삭제
--DELETE (FROM) 테이블
--WHERE 조건식
----조건식 사용 필수, FROM은 생략가능.

DELETE
FROM countries
WHERE country_id = 'KP';


DELETE
FROM departments
WHERE department_id = 280;

DELETE
FROM jobs
WHERE job_id = 'IT DA';

--다중 insert
INSERT INTO departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
SELECT 300,'Advertising11',100,1700 FROM DUAL
UNION ALL
SELECT  310,'Sales Support11 ',100,1700 FROM DUAL;

Insert all 
    Into departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values( 280,'Advertising',100,1700)
    Into departments (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values(  290,'Sales Support ',100,1700)
    select * from dual;
    

--여러개 업데이트 시,
--먼저 셀렉트를 해서 잘 선택했는지 체크,
select * 
from departments
where department_id in (300,310);

--업데이트실행 하면 출력한 행의 정보가 수정하고자 하는 값으로 바뀐다.
update departments
set manager_id=102, location_id=2000
where department_id in (300,310);

--삭제
DELETE FROM countries 
WHERE COUNTRY_ID IN ('AT','NO','ID','TR');

--데이터 무결성(Data Integrity)
--    데이터의 정확성, 유효성, 일관성을 유지하고 보증
--    데이터베이스나 RDBMS 시스템의 중요한 기능
--    데이터의 잘못된 입력, 수정, 삭제로부터 보호
--    입력한 데이터와 데이터베이스에 저장된 데이터 일치
--
--데이터 무결성의 종류
--    개체무결성 : 기본키(PK) 열은 고유
--               NULL값을 가질 수 없음
--    참조무결성 : 왜래키(FK)가 있는 테이블은 기본키와의 관계 유지
--                참조하는 외래키가 존재하면 기본키 변경이나 행 삭제 불가
--    영역무결성 : 데이터 형태, 범위, 기본값, 유일성에 대한 제한
--                주어진 속성 값은 정의된 도메인에 속한 값
--    비즈니스 무결성 : 사용자의 업무의 특성과 규칙에 따른 제약 조건
--                    제약조건,DEFAULT, TRIGGER 등의 사용자 정의
--제약조건 : 정해진 규칙에 적합한 데이터만 입력가능, 그외는 거부하여 무결성을 유지한다.
--    제약조건의 종류
--        기본 키 제약 조건 : UNIQUE 와 NOT NULL 조건 만족
--                         테이블에서 각 행을 유일하게 식별하는 값
--        왜래 키 제약 조건 : 외래 키가 참조 열의 값을 반드시 참조해야 함.
--                         참조되는 열은 UNIQUE 하거나 기본키
--        유일 키 제약 조건 : 중복된 값을 허용하지 않음. 유일한 값으로 존재 (Null값 허용 가능)
--        NOT NULL 제약 조건 : null을 허용하지않으며 반드시 값을 입력해야함.
--        CHECK 제약 조건: 범위나 조건 등 지정된 값만 허용.

--* NN:NOTNULL PK:primary key FK:foreign key UK:unique key MIN:최솟값지정        
SELECT *
FROM regions;

INSERT INTO regions
VALUES(3,'Asia'); 
--무결성 제약 조건(HR.REG_ID_PK)에 위배됩니다

INSERT INTO countries
VALUES ('AR', 'Argentina', 2); 
--무결성 제약 조건(HR.COUNTRY_C_ID_PK)에 위배됩니다

INSERT INTO locations
VALUES (1000, 'Street', 12345, 'Korea', null, 'KR');
--무결성 제약 조건(HR.LOC_ID_PK)에 위배됩니다

INSERT INTO departments
VALUES (10, 'Admin', 200, 1700);
--무결성 제약 조건(HR.DEPT_ID_PK)에 위배됩니다

INSERT INTO employees
VALUES (100, 'Suan' , 'Lee', 'SUAN', '515.123.4567', '21/01/01',
'IT_PROG', 10000, null, null, null);
--무결성 제약 조건(HR.EMP_EMP_ID_PK)에 위배됩니다

INSERT INTO countries
VALUES ('KR', 'South Korea', 5);
--무결성 제약조건(HR.COUNTR_REG_FK)이 위배되었습니다- 부모 키가 없습니다

INSERT INTO locations
VALUES (3300, 'Street', 12345, 'Seoul', null, 'KR');
-- 무결성 제약조건(HR.LOC_C_ID_FK)이 위배되었습니다- 부모 키가 없습니다 --'KR'은 countries에 없다.

INSERT INTO departments
VALUES (280, 'Testing', null, 3300);
--무결성 제약조건(HR.DEPT_LOC_FK)이 위배되었습니다- 부모 키가 없습니다 --locations_id가 3300 인 값은 없다.

INSERT INTO employees
VALUES (207, 'Suan' , 'Lee', 'SUAN', '010.123.1234', '21/01/01',
'IT_QA', 10000, null, null, null);
-- 무결성 제약조건(HR.EMP_JOB_FK)이 위배되었습니다- 부모 키가 없습니다

INSERT INTO job_history
VALUES (300, '21/01/01', '21/10/01', 'IT_PROG', 300);
--무결성 제약조건(HR.JHIST_DEPT_FK)이 위배되었습니다- 부모 키가 없습니다
--많은 위반조건이 있지만 가장 먼저 '300'이 없어서 오류가 먼저 뜸.


INSERT INTO employees
VALUES (207, 'Suan' , 'Lee', 'SKING', '515.123.4567', '21/01/01',
'IT_PROG', 10000, null, null, null); --이메일을 이미 있는 이메일로 적었을경우 (참고 이메일은 NN제약이걸려있음)
--무결성 제약 조건(HR.EMP_EMAIL_UK)에 위배됩니다

INSERT INTO locations
VALUES (3300, 'Street', 12345, null, null, 'US');
--NULL을 ("HR"."LOCATIONS"."CITY") 안에 삽입할 수 없습니다
--Cause:    An attempt was made to insert NULL into previously listed objects.
--Action:   These objects cannot accept NULL values.

INSERT INTO departments
VALUES (280, null, null, 3300);
-- NULL을 ("HR"."DEPARTMENTS"."DEPARTMENT_NAME") 안에 삽입할 수 없습니다

--제약조건 : 시작날짜<끝날짜 조건이 등록되어있다.
INSERT INTO job_history
VALUES (200, '21/10/01', '21/01/01', 'IT_PROG', 200);
-- 체크 제약조건(HR.JHIST_DATE_INTERVAL)이 위배되었습니다

INSERT INTO employees
VALUES (207, 'Suan' , 'Lee', 'SUAN', '123.123.1234', '21/01/01',
'IT_PROG', 0, null, null, null);
--체크 제약조건(HR.EMP_SALARY_MIN)이 위배되었습니다 (salary는 0보다 커야한다)

/*
=====[ 트랜잭션(Transaction) ]========================
1. 정의
-데이터의 무결성이 보장되는 상태에서 DML작업을 완수하기위한 논리적 작업단위
- DML 실행과 동시성 제어를 위한 중요한 개념
- 데이터 처리시 정상종료, 사용자 프로세스 실패, 시스템 실패와 같은 비정상 종료에 대한 데이터 신뢰성과 일관성 보장.
- DML의 INSERT UPDATE DELETE를 실행하여 COMMIT 또는  ROLLBACK을 실행하는 과정까지를 트랜잭션이라 부른다.

2. 트랜잭션의 특징(중요)
원자성: 트랜잭션의 처리가 완전히 끝나지 않은 경우, 전혀 실행되지 않은것과 같아야 함.
일관성: 트랜잭션 실행이 성공적으로 완료되면, 데이터베이스는 모순 없이 일관성이 보존된 상태여야 함
고립성: 어떤 트랜잭션도 다른 트랜잭션의 부분적 실행 결과를 볼 수 없음
지속성: 트랜잭션이 성공하면 트랜잭션의 결과를 영구적으로 보장해야 함

3. 트랜잭션의 수행 단계별 상태 변화 과정
활성 : 트랜잭션 실행 ---> 부분완료: 트랙잭션 명령 실행 후 상태 ---> (커밋) --> 완료:트랜잭션 성공완료
└---> 실패: 더이상 정상적인 실행불가 --->(롤백)--->철회: 트랜잭션 복원으로 수행 이전상태로 복귀

커밋 이전 커밋이후
-데이터 변경 이전 상태로 복구 가능 -데이터를 영구적으로 변경/적용
-현재 사용자만 작업 결과 확인 가능 -기존 데이터 상실
-다른 사용자는 확인 불가 -모든 사용자가 결과 확인 가능
-변경중인 행은 접근 제어 -접근 제어 해지
-다른 사용자가 변경 불가 -다른 사용자가 변경 가능

4.동시성 제어 (Concurrency Control)
-다중 사용자 환경을 지원하는 데이터베이스 시스템에서 여러 트랜잭션들이 성공적으로 동시에 실행될수있도록 지원
-다중 사용자 환경을 지원하는 데이터베이스 시스템의 경우 필수적으로 지원해야하는 기능(병행제어)
-트랜잭션의 직렬화 수행 보장되는
-정확한 접근 제어가 안되면 부정확한 데이터 발생
-동시성 제어 실패 시, 데이터 불일치, 갱신 손실 등의 오류 발생

1)발생할 수 있는 오류
a) 갱신 손실
-하나의 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어쓰게 되어 갱신이 무효화
- 두개 이상의 트랜잭션이 한 개의 데이터를 동시에 갱신할 때 발생
b) 현황 파악 오류
-읽기 작업을 하는 트랜잭션 1이 쓰기 작업을 하는 트랜잭션2가  작업한 중간 데이터를 읽으며 발생할 수 있다.
-작업중인 트랜잭션 2가 작업을 롤백한 경우 트랜잭션 1은 무효가 된 데이터를 읽어 잘못된 결과를 도출
c) 모순성
-다른 트랜잭션들이 해당 항목 값을 갱신하는 동안 한 트랜잭션이 두 개의 항목 값 중 어떤 것은 갱신되기 이전의 값을 읽고
 다른 것은 갱신된 후의 값을 읽게 되어 데이터 불일치 발생
d) 연쇄 복귀
- 두 트랜잭션이 동일한 데이터 내용에 접근할 때 발생
- 한 트랜잭션이 데이터를 갱신한 다음 실패하여 롤백 연산을 수행하는 과정에서 갱신과 롤백 연산을 실행하고 있는 사이에
  해당 데이터를 읽어서 사용할 때 발생.
2) 동시성 제어 기법
현재는 MVCC를 많이 쓴다고 함 (2021년 기준)
a) Locking(shared Lock) : 데이터 항목에 대해 읽기(read)만 가능
b) Locking(Exclusive Lock) : 데이터 항모겡 대해 읽기와 기록 모두 불가능
c) 2 Pharse Locking : 모든 트랜잭션들이 lock 과  unlock 연산을 확장 단계와 수축 단계로 구분하여 수행
d) Timestamp Ordering : 데이터 베이스 시스템에 들어오는 트랜잭션 순서대로 System Clock/Logical Counter 할당하고,
순서를 부여하여 동시성 제어의 기준으로 사용
e) Validation :  트랜잭션 수행 동안은 어떠한 검사도 하지 않고, 트랜잭션 종료 시 일괄적 검사 기법
f) MVCC(Multi-Version-Concurreny Control) : 트랜잭션의 타임스탬프와 접근 데이터의 여러 버젼 타임 스탬프를 비교하여 직렬 가능성이 보장되는 버전 선택


*/




/*
=====[ 데이터 정의 ]========================
1. 데이터 정의어(DDL) : 데이터베이스 객체들을 정의하는데 사용
ex) CREATE(생성) DROP(삭제) ALTER(수정) TRUNCATE(테이블 내 전체 데이터 삭제)
2. 데이터 베이스 객체 
Table, View, Index, Sequence, Synonym ..
3.DDL 명령어는 데이터 베이스에 즉각 반영되며 데이터 사전(DD)에 기록.

*/

--새로운 테이블 생성
--CREATE TABLE 테이블 이름
--    (   열 이름1 데이터 타입,
--        열 이름2 데이터 타입,...
--    );
--    - 테이블 이름, 열 이름 각각 중복 불가
--    - 데이터베이스 예약어 사용 불가 (SYS,SESSION,COUNT, SELECT....이런거)
--    - 1_30바이트 영어이름을 권장.

--1.CREATE TABLE
CREATE TABLE customers
(
    customer_id     number          NOT NULL    PRIMARY KEY,
    first_name      varchar2(10)    NOT NULL,
    last_name       varchar2(10)    NOT NULL,
        email       varchar2(10),   
    phone_number    varchar2(20),   
    regist_date     date
);

SELECT * FROM customers;
INSERT INTO customers
VALUES (1, 'Suan', 'Lee', 'suan', '010-1234-1234', '21/01/01');
INSERT INTO customers
VALUES (2, 'Elon', 'Musk', 'elon', '010-1111-2222', '21/05/01');
INSERT INTO customers
VALUES (3, 'Steve', 'Jobs', 'steve', '010-3333-4444', '21/10/01');
INSERT INTO customers
VALUES (4, 'Bill', 'Gates', 'bill', '010-5555-6666', '21/11/01');
INSERT INTO customers
VALUES (5, 'Mark', 'Zuckerberg', 'mark', '010-7777-8888','21/12/01');

--2.ALTER TABLE ADD
--1)테이블 열 추가 (기존열제거 불가능, 열 위치 지정 불가능, 새로운 열의 데이터값은 Null)
ALTER TABLE 테이블 이름
ADD ( 열 이름 데이터타입, ...);

ALTER TABLE customers
ADD( gender varchar2(10));
ALTER TABLE customers
ADD ( age number );
ALTER TABLE customers
ADD ( dob date );
UPDATE customers
SET gender = 'male', age = 20, dob = '09/01/01'
WHERE customer_id = 1;
UPDATE customers
SET gender = 'male', age = 40, dob = '89/01/01'
WHERE customer_id = 2;
UPDATE customers
SET gender = 'male', age = 30, dob = '99/01/01'
WHERE customer_id = 5;

--2)테이블의 제약조건 추가
ALTER TABLE 테이블이름
ADD CONSTRAINT 제약 조건이름
CHECK (조건지정);

ALTER TABLE customers
ADD CONSTRAINT AK_email
UNIQUE (email);

ALTER TABLE customers
ADD CONSTRAINT AK_phone
UNIQUE (phone_number);

ALTER TABLE customers
ADD CONSTRAINT CK_age
CHECK (age>=0);

--3.ALTER TABLE MODIFY :테이블 열 수정
ALTER TABLE 테이블 이름
MODIFY (열이름 데이터타입,...);

UPDATE customers
SET first_name ='Steven Paul'
WHERE customer_id=3;

ALTER TABLE customers
MODIFY (first_name varchar2(30));
ALTER TABLE customers
MODIFY (last_name varchar2(30));
ALTER TABLE customers
MODIFY (email varchar2(30));

ALTER TABLE customers
MODIFY age DEFAULT 0;

UPDATE customers
SET first_name='William Henry', gender='male'
WHERE customer_id=4;


INSERT INTO customers (customer_id,first_name,last_name,email,phone_number,regist_date)
VALUES (6, 'Jinki', 'Lee', 'onew', '010-1234-0525', '89/12/17');

--4.ALTER TABLE RENAME : 테이블 '열 이름' 변경
ALTER TABLE 테이블 이름
RENAME COLUMN 열 이름  TO 변경할 열 이름;

ALTER TABLE customers
RENAME COLUMN phone_number TO phone;
ALTER TABLE customers
RENAME COLUMN gender TO sex;
ALTER TABLE customers
RENAME COLUMN dob TO date_of_birth;

--5.DROP COLUMN : 테이블 열 또는 제약조건 삭제
ALTER TABLE 테이블이름
DROP COLUMN 열 이름; 
--제약조건삭제 시
    DROP CONSTRAINT 열이름;
    
ALTER TABLE customers
DROP COLUMN date_of_birth;

ALTER TABLE customers
DROP CONSTRAINT CK_age; --처음 제약조건 만들때 등록한 이름으로 작성

ALTER TABLE customers
DROP COLUMN sex;


--6. TRUNCATE TABLE : 테이블의 모든 데이터 삭제 (구조는 유지) 
-- 테이블 구조만 유지, 테이블 자체에 걸어두었던 제약 조건은 삭제 (SQL마다 차이가 있는것 같음)
-- 인덱스 등 모두 삭제, 테이블은 내버려 두고 데이터만 삭제, 복구 불가능 즉, CREATE TABLE을 했던 상태로 되돌아감.
-- 테이블이 사용했던 Storage중 최초 테이블 생성시 할당된 Storage만 남기고 Release 처리 된다.
-- 롤백이 불가능하기에, 롤백이 불필요한 경우 사용한다.
-- Oracle은 DROP ANY TABLE 명령을 사용할 수 있어야하고 이는 시스템 권한이 필요하다.
TRUNCATE TABLE 테이블 이름;
TRUNCATE TABLE customers;--Table CUSTOMERS이(가) 잘렸습니다.

--7. DROP TABLE : 테이블 완전 삭제 (구조도 제거, 인덱스와 제약조건도 삭제)
DROP TABLE 테이블 이름;
DROP TABLE customers;
select * from customers;--테이블 또는 뷰가 존재하지 않습니다

--연습
SELECT* FROM products; --확인
CREATE TABLE products
(   product_id      NUMBER      NOT NULL       PRIMARY KEY,
    product_name    VARCHAR2(10)    NOT NULL ,
    reg_date    DATE
);

INSERT INTO PRODUCTS(PRODUCT_ID,PRODUCT_NAME,REG_DATE)
SELECT 1,'Computer','21/01/01' FROM DUAL
UNION ALL
SELECT 2,'Smartphone','21/02/01' FROM DUAL 
UNION ALL
SELECT 3,'Television','21/03/01' FROM DUAL;


ALTER TABLE products
ADD ( weight NUMBER );

ALTER TABLE products
ADD CONSTRAINT ck_weight
CHECK (weight>=0);

ALTER TABLE products
ADD ( price NUMBER  );

ALTER TABLE products
ADD CONSTRAINT ck_price
CHECK (price>=0);

UPDATE products 
SET weight=10, price=1600000
WHERE product_id=1;

UPDATE products
SET weight=0.2, price=1000000
WHERE product_id=2;

UPDATE products
SET weight= 20, price=2000000
WHERE product_id=3;

ALTER TABLE products
MODIFY(product_name VARCHAR2(30));

ALTER TABLE products
RENAME COLUMN reg_date TO regist_date; 


--삭제
ALTER TABLE products
DROP COLUMN product_id;

ALTER TABLE products
DROP COLUMN product_name;

ALTER TABLE products
DROP COLUMN regist_date;

ALTER TABLE products
DROP COLUMN price;

TRUNCATE TABLE products;

DROP TABLE products;

/*
=====[ 뷰(View) ]========================
1. 정의 
- 사용자에게 접근이 허용된 데이터만 제한적으로 제공
- 하나 이상의 테이블로부터 유도 된 '가상 테이블'
- 뷰에 대한 질의 실행 시 정의된 테이블로 대체되어 실행
- 임시 작업을 위한 용도로 활용되고 사용상의 편의성을 최대화 함.

2. 특징
- 테이블에서 유도 되었기 때문에 구조가 같음
- 가상 테이블이라 물리적으로 구현되지 않음
- 데이터의 논리적 독립성 제공
- 뷰로 필요한 데이터만 처리하므로 관리 용이
- 여러 테이블을 조인하여 뷰 생성 가능
- 뷰에 나타나지 않은 데이터를 안전하게 보호 (사용자별로 접근 권한 설정)
- 테이블의 기본키를 포함하여 뷰를 구성하면 삽입,삭제,갱신 가능

3. 뷰의 장 단점
장점
 - 논리적 테이터 독립성 제공
 - 동일 데이터에 대해 동시에 여러 사용자 요구 지원
 - 사용자의 데이터 관리 편의성 제공
 - 접근 제어를 통한 보안 제공

단점
 - 독립적 인덱스 생성 불가
 - 뷰의 정의 변경 불가
 - 삽입, 수정, 삭제 연산에 제약

4. 뷰의 종류
- 단순 뷰(Simple View) : 하나의 테이블에서 뷰 생성
- 복합 뷰(Complex View) : 두개 이상의 테이블을 조인하여 뷰 생성
- 인라인 뷰( Inline View) : SELECT 문의  FROM 절에 기술된 SELECT 문 

*/

--1. 뷰 생성 및 변경 
CREATE OR REPLACE VIEW 뷰 이름
AS
    SELECT 질의;
CREATE OR REPLACE VIEW new_employee_view
AS
    SELECT employee_id, first_name, last_name,
           email, hire_date, job_id
    FROM employees
    WHERE employee_id > 206;
    
-- 생성한 뷰에 값 입력
INSERT INTO new_employee_view
VALUES (207, 'Lihyun', 'KIM', 'sunho', '21/02/11', 'IT_PROG');
--이때 view가 기존 테이블의 제약조건에 맞지 않는다면 insert 되지 않는다 (not null등등..)

SELECT *
FROM new_employee_view;

DROP VIEW new_employee_view; -- 뷰를 제거해도 등록한 데이터는 유지된다.

--2.읽기 전용 뷰 생성
CREATE OR REPLACE VIEW 뷰 이름
AS
    SELECT 질의
    WITH READ ONLY;

CREATE OR REPLACE VIEW salary_order_view
AS
    SELECT first_name, last_name, job_id, salary
    FROM employees
    ORDER BY salary DESC
    WITH READ ONLY;
    
SELECT *
FROM salary_order_view; --이때 개별조회는 가능하지만,

INSERT INTO salary_order_view
VALUES ('Suan', 'Lee', 'IT PROG', 10000); 
--등록은 되지않는다.log:읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

CREATE OR REPLACE VIEW job_salary_view
AS
    SELECT job_id, SUM(salary) sum_salary,
            MIN(salary) min_salary, MAX(salary) max_salary
    FROM employees
    GROUP BY job_id
    ORDER BY SUM(salary)
    WITH READ ONLY;
    
SELECT *
FROM job_salary_view;
-- 뷰 제거
DROP VIEW salary_order_view;
DROP VIEW job_salary_view;


--3. 구체화된 뷰 생성  MATERIALIZED VIEW 라고 더 많이 불림.줄여서 MVIEW
--뷰는 원래 실체가 없이 SELECT 문으로만 존재하며 데이터는 없지만, 
--구체화 시켜서 실제 데이터가 존재하도록 만들 수 있다.
CREATE MATERIALIZED VIEW 뷰 이름
    [ BUILD { IMMEDIATE | DEFERRED }
    REFRESH { ON COMMIT | ON DEMAND } { FAST | COMPLETE | FORCE | NAVER }
    ENABLE QUERY REWRITE ]
AS
SELECT 질의;
--옵션들이 매우 많음..
--*옵션설명*
--    BUILD IMMEDIATE : MVIEW생성후 동시에 구체화된 내부에 데이터가 채워짐.
--    BUILD DEFERRED : 뷰 내부에 데이터가 나중에 채워짐.
--    REFRESH ON COMMIT :  원본 테이블에 커밋이 발생될 때 마다 구체화 된 뷰의 내용이 변경
--    REFRESH ON DEMAND : 직접 DBMS_MVIEW 패키지를 실행해서 구체화된 뷰의 내용을 변경
--    FAST,FORCE : 원본 테이블에 변경된 데이터만 구체화된 뷰에 적용
--    COMPLETE :  원본 테이블이 변경되면 전체를 구체화된 뷰에 적용
--    NEVER : 원본 테이블이 변경되어도 구체화된 뷰에는 적용 안함.
--    ENABLE QUERY REWRITE: 질의 재작성 허용    
    
CREATE MATERIALIZED VIEW country_loaction_view
    BUILD DEFERRED
AS
    SELECT C.country_name, L.state_province, L.street_address
    FROM HR.countries C, HR.locations L
    WHERE C.Country_id=L.country_id;
    --권한이 불충분합니다 .oracle 계정(sys) 에서 해야한다.
    --oracle 계정에서 생성하면 등록이 되며, 구체화 된 뷰 에서 볼수 있다.
-- oracle 계정에서 실행한 부분
CREATE MATERIALIZED VIEW country_location_view
    BUILD DEFERRED
AS
    SELECT C.country_name, L.state_province, L.street_address
    FROM HR.countries C, HR.locations L
    WHERE C.Country_id=L.country_id;
    
    
SELECT *
FROM country_location_view; 
--테이블 값이 보이지 않음.(BUILD DEFERRED 때문)

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'country_location_view');
--직접 DBMS_MVIEW 패키지를 실행, VIEW에 값을 채워줌.

SELECT *
FROM country_location_view; 
-- 다시 실행하면 데이터값이 들어옴을 알 수 있다.   

DROP MATERIALIZED VIEW country_location_view; -- 구체화된 뷰 삭제

--분석용도로 주로 사용한다고함.

--------------------------------
CREATE MATERIALIZED VIEW country_location_view
    BUILD IMMEDIATE --즉시데이터생성
    REFRESH ON DEMAND COMPLETE  -- DBMS 패키지로 구체화된 뷰 내용 변경, --원본이변경되면 전체를 구체화된 뷰에 적용 
AS
    SELECT C.country_name, L.state_province, L.street_address
    FROM HR.countries C, HR.locations L
    WHERE C.Country_id=L.country_id;
    
SELECT *
FROM country_location_view;  

--기존 테이블의 데이터를 추가
INSERT INTO HR.countries
VALUES ('KR', 'Republic of Korea', 3);
INSERT INTO HR.locations
VALUES (3300, '1 Cheongwadae-ro', 03048, 'Seoul', 'Jongno-gu',
'KR');

--이때 다시 view 조회를 해보면 보이지 않음.REFRESH ON DEMAND 때문

EXECUTE DBMS_MVIEW.REFRESH(LIST =>'country_location_view');
--마찬가지로 DBMS_MVIEW 를 사용하여 변경된 값을 적용.
SELECT *
FROM country_location_view;   --다시 조회하면 반영됨을 알 수 있다.

DELETE HR.locations
WHERE location_id = 3300;

DELETE HR.countries
WHERE country_id = 'KR';

DROP MATERIALIZED VIEW country_location_view;-- 뷰 삭제 
--------------------------------------------------------

CREATE OR REPLACE VIEW employee_07_view
AS
    SELECT employee_id,first_name, last_name, email, hire_date, job_id
    FROM employees
    WHERE to_char (hire_date,'yy')='07';

SELECT * FROM employee_07_view;    

DROP VIEW employee_07_view;    


CREATE OR REPLACE VIEW  high_salary_view
AS 
    SELECT department_id, job_id, AVG(salary) salary_avg 
    FROM employees
    GROUP BY department_id, job_id
    HAVING AVG(salary) >9000
    ORDER BY salary_avg DESC
    WITH READ ONLY;

SELECT * 
FROM high_salary_view;

DROP VIEW high_salary_view;


CREATE OR REPLACE VIEW employee_manager_view
AS 
    SELECT A.department_id, A.first_name||' '||A.last_name as"사원명",
       B.first_name||' '||B.last_name as"매니저명"
    FROM employees A, employees B
    WHERE A.manager_id=B.employee_id
    ORDER BY A.department_id
    WITH READ ONLY;
    
SELECT * FROM employee_manager_view; 
DROP VIEW employee_manager_view;

--oracle--
CREATE MATERIALIZED VIEW company_view
    BUILD IMMEDIATE
    REFRESH ON DEMAND COMPLETE
AS 
    SELECT E.first_name,E.last_name,D.department_name,J.job_title, L.city
    FROM HR.employees E,HR.departments D,HR.jobs J,HR.locations L
    WHERE E.department_id=D.department_id
        AND E.job_id=J.job_id
        AND D.location_id=L.location_id; 

SELECT * FROM  company_view ;


EXECUTE DBMS_MVIEW.REFRESH(LIST=>'company_view');
DROP MATERIALIZED VIEW company_view;
-----------------------------------------
/*
=====[ 인덱스(Index) ]========================
1. 정의
- 데이터베이스에서 테이블 검색 속도를 향상시키기 위한 자료구조
- 별도의 추가 저장 공간을 활용하여 인덱스 생성
- 데이터와 데이터 위치를 포함한 자료 구조 생성
- 데이터를 빠르게 찾을 수 있기 때문에 디스크 엑세스 횟수 감소 
- DBMS 에서 인덱스를 자동으로 사용하며 유지 보수 수행
- 인덱스는 언제든지 생성 및 삭제가 가능하며 다른 테이블이나 인덱스에 영향을 주지않음
- 인덱스를 통해 데이터 조회를 위한 SELECT 뿐만 아니라 UPDATE와  DELETE의 성능도 향상

2. 인덱스 관리
- 데이터에 변경이 발생하면 최신 상태 유지를 위해 인덱스 관리 필요
- 인덱스가 적용된 컬럼은 최신 데이터를 정렬된 상태로 유지
- INSERT, UPDATE, DELETE 발생 시 인덱스 관리 수행
- INSERT :  새로운 데이터에 대한 인덱스 추가
- UPDATE :  기존의 인덱스는 사용하지 않도록 처리하고, 갱신된 데이터에 대한 인덱스 추가.
- DELETE :  삭제하는 데이터의 인덱스는 사용하지 않도록 처리

3. 인덱스의 장 단점 
1)장점
- 테이블 조회 속도 향상
- 전반적인 시스템 부하 감소
2)단점
- 인덱스 관리를 위한 추가 작업 필요
- 데이터 베이스의 약 10%정도의 추가 저장공간 필요
- 잘못된 인덱스 사용으로 성능이 저하 될 수도 있음
4. 인덱스 사용에 적합한 경우
- 규모가 큰 테이블
- 등록,수정,삭제가 자주발생하지 않는 컬럼
- JOIN 이나 WHERE , ORDER BY  에 자주 사용되는 컬럼
- 중복되는 데이터가 최소인 컬럼

5. 인덱스 자료구조
-트리형태의 자료구조를 주로 사용, 특히 B Tree 계열의 B*Tree, B+Tree 구조를 많이 사용.


*/

--인덱스 조회
SELECT *
FROM user_indexes;

SELECT *
FROM user_indexes
WHERE table_name='EMPLOYEES';--테이블명 대문자로작성 

SELECT *
FROM user_ind_columns --인덱스 컬럼 조회
WHERE table_name='EMPLOYEES'; -- 인덱스에 사용된 칼럼을 조회한다.

--index를 사용했는지 확인하는법
--실행아이콘 쪽 라인의 왼쪽에서 세번째 아이콘 클릭하면 사용여부 확인가능
SELECT * 
FROM employees
WHERE employee_id=100; --index를 사용했다고 뜨는것을 볼 수 있다,

SELECT *
FROM employees
WHERE  first_name = 'Steven';
--이름 또한 index로 지정되었기 때문에 index를 통해 검색한다.
--이 경우 데이터를 전체적으로 스캔하는 검색을 하지 않기 때문에 성능이 향상된다 (인덱스의 장점)

------------------------------------예제학습
CREATE TABLE customers
(
    customer_id     number      NOT NULL    PRIMARY KEY,
    first_name      varchar2(10) NOT NULL,
    last_name       varchar2(10) NOT NULL,
    email           varchar2(10),
    phone_number    varchar2(20),
    regist_date     date
);

alter table customers
modify ( phone_number varchar(20));

INSERT INTO customers(customer_id,first_name,last_name,email,phone_number,regist_date)
SELECT 1, 'Suan', 'Lee', 'suan', '010-1234-1234', '21/01/01' FROM DUAL
UNION ALL
SELECT 2, 'Elon', 'Musk', 'elon', '010-1111-2222', '21/05/01' FROM DUAL
UNION ALL
SELECT  3, 'Steve', 'Jobs', 'steve', '010-3333-4444', '21/10/01' FROM DUAL
UNION ALL
SELECT  4, 'Bill', 'Gates', 'bill', '010-5555-6666', '21/11/01' FROM DUAL
UNION ALL
SELECT  5, 'Mark', 'Zuckerberg', 'mark', '010-7777-8888','21/12/01'FROM DUAL;

--인덱스 조회 
--primary key로 등록한 값은 자동으로 index가 된다.
SELECT *
FROM user_indexes
WHERE table_name='CUSTOMERS';

--인덱스 생성
SELECT *
FROM customers
WHERE regist_date = '21/01/01'; 
--생성전 조회해보기 실행문 과정을 살펴보면 : Filter Predicates 를 하는데 즉,전체탐색을 하는것, 이경우 인덱스보다는 성능저하임.
CREATE INDEX regist_date_idx
ON customers (regist_date); --인덱스 생성후 다시 조회하면 INDEX를 사용함을 알 수 있다.

--다중속성 인덱스 생성
CREATE INDEX name_idx
ON customers (first_name, last_name);
--인덱스 생성방법과 별 다를게 없음.
--등록확인
SELECT *
FROM user_indexes
WHERE table_name = 'CUSTOMERS';

--검색 후 확인
SELECT *
FROM customers
WHERE first_name = 'Suan';

-- 고유 인덱스 생성
CREATE UNIQUE INDEX email_idx
ON customers (email);

SELECT *
FROM user_indexes
WHERE table_name = 'CUSTOMERS';

SELECT *
FROM customers
WHERE email = 'suan';

--인덱스 삭제
DROP INDEX regist_date_idx;
DROP INDEX email_idx;
DROP INDEX name_idx;

--테이블 삭제
DROP TABLE customers;