ENUM* (순서를 가진 문자열범주 - ordinal) and NESTED SELECT*(집계결과는 따로받아, 해당테이블 SELECT 칼럼연산 or WHERE조건으로 사용)

총 정리

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;