우아 한의원 | 조재성 원장의 한의학, 의학통계, 프로그래밍

총 정리

ENUM

1. ENUM 생성 : CREATE TYPE 이넘명 AS ENUM( '순서범주1', '순서범주2', '순서범주3' ) 형식으로 생성
2. 테이블 생성시, datatype으로 지정해주기
3. INSERT INTO 시 ENUM에 속한 범주만 입력가능
4. WHERE 절에 특정범주에 부등호(< or >)를 통해 여러범주를 필터링할 수 있다.

NESTED SELECT

1. 집계하는 테이블에서는, 집계결과를 따로 칼럼을 보기 위해서는, GROUP BY 에 넣어주고 그 칼럼별 집계밖에 못보지만,
   집계결과를 따로 NESTED SELECT(SubQuery)로 취급하면 -> SELECT문에서 연산에 사용 or WHERE의 조건으로 걸 수 있다.
   
   cf) 서브쿼리는 줄을 바꿔주고 tab하나 넣는게 보통양식
   cf) SELECT 집계연산 FROM 테이블 WHERE 조건 ---> 조건까지 만족하는 테이블 이후 집계가됨
   cf) alias 칼럼명은 "" 쌍따옴표로 만들어주자.

ENUM

-- 1) ENUM 

--     (1) 새로운 dataTYPE을 만드는데 ENUM으로서 만들자. ENUM()안에 limited된 String value들을 정의해주면,
--        ***넣은 순서대로 순서를 가지는*** 순서형 범주(Ordinal <-> Nomial명목형) 데이터 타입이 된다.
 CREATE TYPE mood AS ENUM('extremely unhappy', 'unhappy', 'ok', 'happy', 'extremely happy');

--    (2) 새로 생성한 ENUM type의 mood를 칼럼의 type으로 지정해서 테이블을 생성하자.
 CREATE TABLE students (
name character varying(255),
current_mood mood 
 );

 SELECT * FROM students --mood type의 칼럼이 생김

INSERT INTO students
VALUES ('Moe', 'hapy') -- ENUM 범주에 없는 값을 입력시키면 에러가 난다.

INSERT INTO students
VALUES ('Moe', 'happy')

INSERT INTO students
VALUES ('Larry', 'happy')

INSERT INTO students
VALUES ('Rolf', 'extremely unhappy')

INSERT INTO students
VALUES ('Anne', 'extremely happy')

INSERT INTO students
VALUES ('Robert', 'unhappy')

INSERT INTO students
VALUES ('Jose', 'happy')



SELECT * FROM students



 -- (3) ENUM은 순서형 범주(Ordinal)로서, WHERE 절에서 value로 필터링 할 수 있다.
 -- enum을 이용하면, string형으로 순서를 줄 수 있어서, 회원등급을 메길 수 있고
 -- 설문score도 적용시킬 수 있다. 등등 
 SELECT * FROM students WHERE current_mood > 'ok';

NESTED SELECTD

 -- NESTED SELECT : 집계결과를, 집게테이블에서 WHERE문 or SELECT에 다  쓸 수 있다.
 -- 매우 유용함. 쿼리 안에 쿼리가 들어가는 구조

 -- 예제1) 보다 높은 가격의 item들 목록

 -- 내가 한 것)
 SELECT AVG(price) FROM items        -- 73.16125
 SELECT * FROM items WHERE price > 73.16125

 -- 좋은 것으나 안되는 것)
 SELECT * FROM items WHERE price > AVG(price)

 -- 또다른 조건을 줄때는 HAVING을 쓰나, Group by 된 상태만 된다.

 -- 정답1) NESETED SELECT를 이용해야만, 집계테이블에서, 집계결과를, WHERE문에 사용할 수 있다.
--    (1) 평균(집계)결과값을 사용할 부분을 제외한 WHERE문 작성
SELECT * FROM items WHERE price > 
();
--    (2) 집계결과를 bracket()에 작성
SELECT * FROM items WHERE price > 
(SELECT AVG(items.price) FROM items);


--    my) 이전에 했었던 아이템 목록중 가장 비싼 아이템 골라오기
SELECT * FROM items WHERE price = 
(SELECT MAX(price) FROM items);





-- 예제2 ) 아이템들의 가격이 <아이템 평균가격>보다 얼마나 비싸거나 쌀까?

-- 내가 한 것 )
SELECT * FROM items;
SELECT AVG(price) FROM items;
SELECT items.name, items.price, () FROM itesm;
SELECT items.name, items.price, (SELECT AVG(price) FROM items) FROM items;
SELECT items.name, items.price - (SELECT AVG(price) FROM items) FROM items;


 -- 정답 1)
--    (1) 평균집계값을 () 으로 비워두고, 집계테이블에서 필요한 칼럼들을 SELECT
--        가격의 비교를 위해서는 빼야한다. numeric은 SELECT 란에서 바로 (-)된다.
SELECT items.name, items.price - () FROM items;

--    (2) 서브쿼리의 기본 형식은 라인을 바꿔주고, tab을 넣는 것이다.
SELECT items.name, items.price - (
    SELECT AVG(price) FROM items
) FROM items;





-- 예제3 )  expensive_items아이템들의 가격이
--       과 얼마나 차이나는가?
--        를 VIEW로 만들기

-- 내가 한 것)
SELECT * FROM items;
SELECT * FROM items WHERE price > 80;

SELECT AVG(price) FROM items WHERE price > 80; -- 155.163 : FROM WHERE이후 --> WHERE절로 필터링
SELECT AVG(price) FROM items;    --73.16

SELECT items.name, items.price - () FROM items;

SELECT items.name, items.price - (
    SELECT AVG(price) FROM items WHERE price > 80
) FROM items;

-- 내가 catch못한 것 : 80달러보다 비싼 아이템의 평균값만 구하고, 
-- 그 이후 바깥의테이블에서는 80달러보다 비싸다는 WHERE를 안달아줘서, 모든 items들이 다 나왔음.
-- 문제를 잘못받아적음. 기본적으로 expensive 아이템가격들의 expensive아이템의 평균과의 차이 
-- ***FROM WHERE 필터링 이후--> SELECT의 집계연산이 이루어진다.


-- 정답3 )
--    (1) 80달러보다 비싼 아이템의 평균  = WHERE 절까지 거친 테이블에 집계함수가 일어남
SELECT AVG(items.price) FROM items WHERE price > 80;

--    (2) 다른칼럼들과 함께 + price - (1) 의 차이 SELECT하기 + WHERE 절 안빠트리기
SELECT *, items.price - (

) 
FROM items WHERE price > 80;

--    (3) NESTED SELECT 넣어주기
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) 
FROM items WHERE price > 80;

--    (4) VIEW로 만들기
CREATE VIEW expensive_items_diff AS 
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) 
FROM items WHERE price > 80;

--    (5) VIEW SELECT해보기
SELECT * FROM expensive_items_diff

--    (6) 칼럼이름이 없어서, 다시 DROP후 서브쿼리 ()끝나고 AS로 칼럼명 " "로 alias 정의해주기
DROP VIEW expensive_items_diff;

CREATE VIEW expensive_items_diff AS 
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) AS "average_diff"
FROM items WHERE price > 80;

SELECT * FROM expensive_items_diff;

총 정리

# TO_CHAR() : timestamp -> 문자열 or 영어로표현된 문자열로

1. 현재시간  : MSSQL : GETDATE() / PostgreSQL : NOW()
2. 날짜-> 문자열로 변환 : MSSQL : CONVERT( datatype, 시간(시간칼럼), date type number) / PostgreSQL : TO_CHAR( 시간(시간칼럼) , 'date type' )
3. TO_CHAR(  ,  )에 들어가는 datetype에서,  YYYY=yyyy, MM=mm, DD=dd, HH=hh 대소문자 구분안하고 년/월/일/시이다.
4. 분(minute)는 MI = mi
   
   --위는 TIMESTAMP 형식 : [ YYYY-MM-DD HH:MI:SS ]   
   
   --아래에서는 MM과 DD 대신 --> [  요일(FMDAy) - 일의 th형식   + 월의 영어표현(FMMonth) ] -----
      [ FMDay FMDDth FMMonth, YYYY HH:MI:SS]
   
   
5. 요일은 Day = day = DAY 가 있다. 그러나 공백을 제거하기 위해서는 FM을 붙힌 FMDay = FMDAY = FMday 
6. 일의 th 표현은 DDth = ddth이다.
7. 월의 영어표현은 FMMonth = FMMONTH = FMmonth 이다.

# TO_TIMESTAMP() : 문자열 -> timestamp 로 

1. TO_TIMESTAMP( '문자열로 표현된 시간' , ' 그 문자열의 DATE TYPE 형식' )을 넣어주면, timestamp로 변환

TIMESTAMP

-- DATE
-- date는 많은 문제가 있다. 
-- 1) datetime의 형태가 너무 많다. ex> 2019-01-30  or  01-30-2019 or 01. 30. 2019
-- 2) timezone개념이 붙어있다. ex> GMT, RUSSIAN, UTC 등등 장소에 따라 current time이 달라진다.

-- >> 추천하는 방법 :  SQL에는 TIMEZONE을 입력하지 않고, python 등 어플리케이션 단에서 TIMEZONE을 적용한다.

-- PostgreSQL의 메인 date 타입 4가지 : timestamp, date, time, interval

-- 1) timestamp
--    ISO 8601 standard date 타입 in DB.  YYYY-MM-DD HH:mm:ss 
--    pgSQL의 default 형식으로 많은 문제를 예방할 수 있음.
SELECT timestamp '2019-01-30 13:30:45';



-- 2) timestamp to string
--    (1) NOW()로 현재시간을 가져온다 <--> MSSQL의 GETDATE() or DATEDIFF( 간격, datetime, GETDATE())
--    (2) TO_CHAR( timestamp, 'DD' 등 날짜포맷 ); 으로 문자열로 전환 <--> MSSQL의 CONVERT( type, datetime, style)

SELECT NOW(); --MSSQL에서는 GETDATE() 였다! 

SELECT TO_CHAR( NOW(),'DD' ); -- MSSQL의 CONVERT( type, datetime, style) or DATEDIFF( 간격, datetime, GETDATE())
SELECT TO_CHAR( NOW(),'dd' );
-- text 30

SELECT TO_CHAR( NOW(),'MM' );
SELECT TO_CHAR( NOW(),'mm' );
-- text 01 

SELECT TO_CHAR( NOW(),'YYYY' );
SELECT TO_CHAR( NOW(),'yyyy' );
-- text 2019 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY')
-- text 30-01-2019  
SELECT TO_CHAR( NOW(), 'DD-MM-YY')
-- text 30-01-19   ==> confusing

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH')
-- text 30-01-2019  02 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH:MI'); -- pgSQL에서는 MM,mm month / MI :minute
-- text 30-01-2019  02:28 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH:MI:SS');
-- text 30-01-2019  02:28:08

SELECT TO_CHAR( NOW(), 'Day, DD-MM-YYYY HH:MI:SS');
-- text Wednesday, 30-01-2019  02:28:08
-- 만약 토요일이면, Saturday , 형식으로 빈칸이 하나 발생한다 그걸 제고 하고 싶다면 Day앞에 FM을 쓴다.

SELECT TO_CHAR( NOW(), 'FMDay, DD-MM-YYYY HH:MI:SS');
-- text Wednesday, 30-01-2019  02:28:08


SELECT TO_CHAR( NOW(), 'FMDay Month, DD-MM-YYYY HH:MI:SS');
-- text Wednesday January  , 30-01-2019  02:28:08

SELECT TO_CHAR( NOW(), 'FMDay FMMonth, DD-MM-YYYY HH:MI:SS');
-- text Wednesday January, 30-01-2019  02:28:08


--날짜를 3th 4th 형식으로도 줄 수 있다.
SELECT TO_CHAR( NOW(), 'FMDay DDth FMMonth, DD-MM-YYYY HH:MI:SS');
-- text Wednesday 30th January  , 30-01-2019  02:28:08


-- 이제 DD-MM 을 제거해서 보자.
SELECT TO_CHAR( NOW(), 'FMDay DDth FMMonth, YYYY HH:MI:SS');
-- text Wednesday 30th January, 2019 02:28:08



-- 3) 이제 TO_TIMESTAMP( '마지막에 작성한 양식의 string' , 'string과 똑같은 양식의 날짜포맷' );
--    를 통해 sting을 timestamp로 바꿀 수 있다.

SELECT TO_TIMESTAMP('Wednesday 30th January, 2019 02:28:08', 'FMDay DDth FMMonth, YYYY HH:MI:SS');

-- 4) string양식이 ISO8601 이었으면, 그 string에 맞게 양식을 적어주면, timestamp로 변한다
SELECT TO_TIMESTAMP('2019-01-30 02:28:08', 'YYYY-DD-MM HH:MI:SS');

리스트 속 요소들을 변환한 리스트를 반환받는 법

1. 변환될 빈 리스트 생성 -> for문 돌면서 처리 -> 각각 append
2. list comprehension
3. list ( map ( lambda x: , list ) )

리스트 속 요소들을 필터링한 리스트 반환받는 법

1. list comprehenshion + if
   1) [ x if 조건1 else 조건 2 for in ]
   2) [ x for in if 조건1 ]
2. list ( filter(lambda x: , list ) )

리스트 vs set

1. list() :  [1, 2, 3] or []  로 생성하면 append한다
2. set() :  {1, 2, 3} 로 생성하면 add한다.
3. { } 로 생성은 빈 딕셔너리다 ! set이 아니다! set의 양쪽 구성만 { }

리스트 속 요소들을 변환한 set을 반환받는 방법

1. { set comprehension for in 리스트 }

총정리

1. HAVING은 GROUP BY 끝난 쿼리문에서 조건을 추가적으로 달 수 있다.
2. HAVING은 1차 처리된 칼럼(COUNT()등)을 조건을 걸고 싶을 땐, alias사용이 안된다.

HAVING

-- 내장함수 & HAVING 

-- 내장함수의 종류 : 
-- COUNT(), SUM()
-- 예제 ) 고객별 구매횟수 ( 횟수, 건수 : 해당 테이블의 id를 카운트 해버리면 된다)
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id

-- AVG()
-- 예제 ) 아이템의 가격 평균
SELECT AVG(items.price) FROM items;

-- 예제 ) 구매된(join으로 교집합) 아이템  가격의 평균
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT AVG(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id;



-- 1) 구매된 아이템 중 가격 TOP 1 을 구하는 첫번째 : JOIN 구매된 아이템  -> ORDER BY -> LIMIT
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id

SELECT items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id
ORDER BY items.price DESC
LIMIT 1;


-- 2) 구매된 아이템 가격 TOP 1을 구하는 두번째 : JOIN 구매된 아이템 -> MAX() ? 
--    : 집계테이블이 되어, 같은 테이블 내 칼럼선택(items.name)을 못함

--    만약, 다른컬럼(items.name)도 같이보려고, 집계함수 앞에 SELECT 시
--        ~별 기준이 되어버려서, 각 items.name 별 최대값들이 여러 row로 나옴.

SELECT MAX(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id;    -- 집계테이블로서 1개 칼럼밖에 못봄

SELECT items.name, MAX(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id    
GROUP BY items.name;-- 하나더보려고 select + groupby에 넣으면 기준테이블이 되어 각 아이템별로 최대값이 되어버림


-- 3) HAVING = GROUP BY + 집계함수 결과에 filter링 (집계함수의 WHERE절)
--    (1) 고객별 구매횟수를 위에서 작성했떤 것 복사해오자.
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id

--    (2) GROUPBY  + 집계함수의 결과에서 HAVING을 이용하여 다시 3개이상의 구매자만 걸러내보자.
--        *** 이때, 집계한칼럼의  alias로는 HAVING에서 조건을 걸 수 없다.
--        *** 1차 작업물이 SELECT 문에 있을시 -> 이후 사용은 그 문장 그대로!
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING purchase_count > 2;     -- alias 사용시 오류

SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING COUNT(purchases.id) > 2;

-- 4) 다음에 같은작업(고객별 구매횟수가 가장 많은 사람 뽑기)할일이 있다면, VIEW로 만들어보자.

총 정리

1. DROP TABLE IF EXIST 테이블명
2. 종속관계가 있는 테이블은, 참조하고 있는 테이블부터 지워야 삭제 가능하나, 종속관계를 끊고 삭제하려면 DROP TABLE 테이블명 CASCADE;
   

VIEW정리

1. VIEW 생성 : CREATE VIEW 뷰명 AS  groupby복잡한 쿼리문     을 통해 updatable한 테이블을 생성할 수 있음.
2. VIEW 조회 : SELECT * FROM 뷰명  으로 테이블조회하듯이 조회한다.
3. VIEW 삭제 : DROP VIEW 뷰명
4. VIEW를 조회할 때 WHERE / ORDER BY달고 조회가능 / 
5. VIEW + WHERE 절 달아서 새로운 VIEW 생성가능
   
   
6. VIEW생성시 AS쿼리문에 1) * 모든칼럼 선택한상태 + 2) * group by 등 집계안한 상태 + 3)WHERE문 정도 라면 : INSERT INTO VIEW 로 값 대입 가능, 특별한 옵션이 없다면 WHERE조건 만족못해도 원본테이블에 들어감
7. WITH LOCAL CHECK OPTION으로 VIEW생성시, 해당WHERE절 조건만족못하는 것은 INSERT INTO VIEW로 대입 불가능
8. view1를 이용해 view2를 만든 상태에서
   (1) view1에 local옵션이 없고 & view2 local옵션만 있다면 ==> view2옵션만 체크한다.
   (2) view1에 local옵션이 있고 & view2 local옵션있다면     ==> view1, view2 둘다 체크한다. ( 각 view가 local있다면, 다 체크 )
   (3) view1에 local옵션이 없고 & view2 CASCADED 옵션이 있다면 ==> view1, view2 둘다체크한다. (마지막 view가 cascaded옵션있다면, 다 체크)

DROP TABLE 옵션 :  CASCADE vs CASCADED : VIEW를 포함한 VIEW에서 2개 모두 WHERE절 조건 체크 옵션

DROP

-- DROP TABLE

-- 1) videos테이블에서 users를 참조하고 있는 상황이므로 바로 삭제가 안된다.
DROP TABLE public.users;

-- 2) 강제로 삭제하는 방법은 뒤에 CASCADE를 붙혀 -> 다른테이블과의 관계를 끊는 것이다.
DROP TABLE public.users CASCADE;

-- 3) 관계는 끊어져도 기존에 연결된 값들은 유지되어있다.
--    videos 테이블의 정보를 보면, CONSTRAINT fkey정보가 업어져있다.
SELECT * FROM videos;


-- 4) 만약, videos테이블을 먼저 DROP한다면, uses테이블도 바로 DROP 될 것이다.
DROP TABLE public.videos;

-- 5) 없는 테이블을 삭제하는 오류를 줄이고 싶다면 DROP TABLE <--> 테이블명 사이에 IF EXISTS 를 준다.
--     테이블을 생성하기 전에 적어줘도 좋은 팁이 된다.
DROP TABLE IF EXISTS public.videos;

-- 6) DROP TABLE/DATABASE/SEQUENCE/VIEW 등이 다 DROP이 가능하다.

VIEW

-- VIEW & INSERT VIEW without groupby & WITH LOCAL/CASCADE CHECK OPTION

-- 1) view없이 고객들이 소비한 전체 돈 보기 by GROUP BY 
--     customers별 목록이 1 row로 나타나도록 id로 groupby + 기준테이블 칼럼들은 막 select +  집계테이블은 칼럼없이 sum()으로 1 row로 집계했다.
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

-- 2) 매번 groupby를 할게 아니라, VIEW를 이용해 해당 결과를 저장하자.
--    CREATE VIEW + view이름 + AS + 저장할 쿼리문 형태이다.
CREATE VIEW total_revenue_per_customer AS
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

-- 3) 마치 테이블처럼 SELECT할 수 있다.
SELECT * FROM total_revenue_per_customer

-- 4) DROP VIEW view이름으로 삭제할 수 있다.
DROP VIEW total_revenue_per_customer

-- 5) 다시 view를 만드는데, groupby 의 기준이된 customers.id도 같이 SELECT한 view를 만들자
--    새로운 테이블로 보는데, id칼럼이 없기 때문...?
--    이 아니라,, 새로운 구매정보를 늘릴 때 view는 어떻게 변하는지 보기 위해서다.
CREATE VIEW total_revenue_per_customer AS
SELECT customers.id, customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

SELECT * FROM total_revenue_per_customer;

-- 6) VIEW의 결과에 없는 customers 중에 한명이 item을 구매하도록 해보자.
--    구매정보를 늘이려면 purchases테이블에 추가해야한다.
SELECT * FROM total_revenue_per_customer;    --1,3,4,5는 구매했었음.
SELECT * FROM customers;            --남은건 2번 고객
SELECT * FROM items;                --아이템은 아무거나.. 여기서 6번 아이템


SELECT * FROM purchases;             -- 구매번호는 10번까지 차있구나.

INSERT INTO purchases                -- 구매번호 11, item_id 6번, customer_id 2번
VALUES (11, 6, 2);


-- 7) 기존에 저장한 VIEW를 다시 보자.
--    customers.id 2번이 추가되었다! / 만약 기존고객이 구매정보를 늘렸으면, sum이 증가할 것이다!
--    VIEW는 결과가 아니라 쿼리문 자체를 저장해서 updatable한 테이블을 생성해주는 것
SELECT * FROM total_revenue_per_customer;


-- 8) VIEW로 저장한 쿼리문에는 WHERE문도 달 수 있다.
SELECT * FROM total_revenue_per_customer WHERE sum > 150;

-- 9) VIEW에 WHERE문을 단 것 역시, VIEW로 만들 수 있다.
CREATE VIEW awesome_customer AS
SELECT * FROM total_revenue_per_customer WHERE sum > 150;

SELECT * FROM awesome_customer;

-- 10) VIEW를 table로 취급하여, ORDER BY 한 것도 가능하다.
SELECT * FROM total_revenue_per_customer WHERE sum > 100
ORDER BY sum DESC;



-- INSERT INTO VIEW를 통해 VIEW에 값 대입하기는 group by 절이 포함된 VIEW는 사용하지 못한다.
-- 위에서 만든  total_revenue_per_customer 같은 것들은 INSERT INTO에 view를 사용하지 못한다.

-- 1) group by가 없는 VIEW를 만들자.
CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100;

SELECT * FROM expensive_items

-- 2) 여기에 row를 대입해보자. ( 만약, SEQUENCE를 사용한 테이블이라면 해당값 입력x)
--    VIEW를 만든 테이블(items)에 값이 자동으로 삽입된다!
--     만약 일부칼럼만 가져온 VIEW라면?;; 
INSERT INTO expensive_items(id, name, price)
VALUES (9, 'DSLR',400.00);

SELECT * FROM expensive_items
SELECT * FROM items;            -- items테이블에도 추가되어있다.

-- 3) 만약, VIEW에 포함된 WHERE 조건절을 만족하지 않는 값을 추가한다면?
--     VIEW의 WHERE에는 100달러 이상 아이템만 가져오도록했는데, 5달러짜리를 추가해보자.
INSERT INTO expensive_items(id, name, price)
VALUES (10, 'DSLR', 5.00);

SELECT * FROM expensive_items;    --결과적으로, items테이블에는 추가되지만, VIEW에서는 안보이게 된다. 왜냐 살아있는 쿼리문이 저장되어있는 테이블이므로
SELECT * FROM items;    

-- 4) 그렇다면, INSERT INTO VIEW 를 이용해서, VIEW에 달린 WHERE 조건을 만족하는 값만 입력되도록 하려면?
--    PostgreSQL에만 있는 코드인 WITH LOCAL CHECK OPTION을 VIEW에 추가해줘야한다.
--    WITH LOCAL CHECK OPTION은 데이터를 insert나 update시 where조건절을 항상 체크한다.
DROP VIEW IF EXISTS expensive_items;

CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100
WITH LOCAL CHECK OPTION ;

INSERT INTO expensive_items(id, name, price)
VALUES (11, 'low camera', 5.00);        -- view의 조건을 위반했다고 뜬다.

INSERT INTO expensive_items(id, name, price)
VALUES (11, 'low camera', 101.00);

-- 5) 이번에는 WITH LOCAL CHECK OPTION 없이 만든 expensive_view(100달러이상)에 
--    FROM expensive_view  + WITH LOCAL CHECK OPTION 으로
--    10000달러는 이하인 non_luxury_items VIEW를 
DROP VIEW IF EXISTS expensive_items;

CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price >= 100;

CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH LOCAL CHECK OPTION;

-- 6) non_luxury_items VIEW 에다가 2달러짜리를 INSERT INTO 를 해보자.
--    LOCAL CHECK OPTION은 현재하고 있는 WHERE에 대해서만 체크를 한다고한다.

INSERT INTO non_luxury_items(id, name, price)
VALUES (12, 'Pencil', 2.00);
--    결과적으로 2달러도 입력이 된다.
--    직접적으로 달리지 않은 expensive_items에 대한 체크를 하지 않는다.
--    즉, non_luxury_items에 대한 check만 시행한다.


-- 7) LOCAL 이 아니라 종속관계까지 다 체크해주는 CASCADED CHECK 를 맨 마지막 VIEW에 달면,
--    내부의 VIEW(테이블)의 WHERE절까지 체크해준다.
DROP VIEW IF EXISTS non_luxury_items;

CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH CASCADED CHECK OPTION;

INSERT INTO non_luxury_items(id, name, price)
VALUES (13, 'PencilTwo', 90.00); -- 내부에 있는 VIEW인 expensive_item에 대한 WHERE절에 위반되고 있다고 경고창이 뜬다.