뭐든지 다 알아보자

https://school.programmers.co.kr/learn/courses/30/lessons/59044

--아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
-- ANIMAL_INS 가 기준이 아닌 FULL OUTER 를 통해 NULL 값 출력하면 좋을거 같다.
SELECT  A.NAME , A.DATETIME FROM (
ANIMAL_INS A FULL OUTER JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID)
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME ASC
FETCH FIRST 3 ROWS ONLY

==> FETCH FIRST 3 ROWS ONLY ==> 상위 N개 레코드 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131533

SELECT PRODUCT_CODE , SALES FROM (
SELECT  B.PRODUCT_ID AS B_PRODUCT_ID, A.PRODUCT_CODE, SUM(A.PRICE*B.SALES_AMOUNT) AS SALES FROM (
(SELECT * FROM PRODUCT) A RIGHT JOIN (SELECT * FROM OFFLINE_SALE) B ON A.PRODUCT_ID = B.PRODUCT_ID)
GROUP BY B.PRODUCT_ID , A.PRODUCT_CODE)
ORDER BY SALES DESC , PRODUCT_CODE ASC 
-- PRODUCT 테이블은 상품들의 상품 정보를 담은 테이블 , 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 ==> OFFLINE 테이블 기준으로 PRODUCT_ID를 바탕으로 조인해야한다.

==> PRODUCT 테이블은 상품들의 상품 정보를 담은 테이블 , 오프라인 상품 판매 정보를 담은 OFFLINE_SALE 테이블 ==> OFFLINE 테이블 기준으로 PRODUCT_ID를 바탕으로 조인해야한다.

https://school.programmers.co.kr/learn/courses/30/lessons/59410

VERSION 2.0

SELECT ANIMAL_TYPE , CASE WHEN NAME IS NULL THEN 'No name' ELSE NAME END AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

==> CASE WHEN THEN 활용 

VERSION 1.0

SELECT ANIMAL_TYPE , NVL(NAME , 'No name') AS NAME , SEX_UPON_INTAKE FROM ANIMAL_INS
ORDER BY ANIMAL_ID

==> NVL 활용

https://school.programmers.co.kr/learn/courses/30/lessons/157340

SELECT CAR_ID , CASE WHEN AVAILABILITY = 1 THEN '대여중' ELSE '대여 가능' END AS AVAILABILTY FROM (
SELECT CAR_ID , SUM(YN) AS AVAILABILITY FROM (       
SELECT CAR_ID , START_DATE, END_DATE, CASE WHEN TO_DATE('20221016' , 'YYYY-MM-DD') BETWEEN START_DATE AND END_DATE THEN 1 ELSE 0 END AS YN FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)
GROUP BY CAR_ID)
ORDER BY CAR_ID DESC

==> CASE WHEN 조건 THEN '  ' ELSE ' ' END AS

https://school.programmers.co.kr/learn/courses/30/lessons/144856

SELECT  A.AUTHOR_ID , A.AUTHOR_NAME, A.CATEGORY  , SUM(A.PRICE * B.SALES) AS TOTAL_SALES FROM
(SELECT A.BOOK_ID AS BOOK_ID , A.AUTHOR_ID AS AUTHOR_ID , B.AUTHOR_NAME AS AUTHOR_NAME ,A.CATEGORY AS CATEGORY ,A.PRICE AS PRICE FROM BOOK A LEFT JOIN AUTHOR B 
ON A.AUTHOR_ID = B.AUTHOR_ID) A LEFT JOIN (SELECT * FROM BOOK_SALES WHERE TO_CHAR(SALES_DATE , 'YYYY-MM') = '2022-01') B ON A.BOOK_ID = B.BOOK_ID
GROUP BY A.AUTHOR_ID , A.AUTHOR_NAME, A.CATEGORY
ORDER BY A.AUTHOR_ID, A.CATEGORY DESC

==> SUM(A.PRICE * B.SALES)

https://school.programmers.co.kr/learn/courses/30/lessons/133026

SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF A LEFT JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC

==> LEFT JOIN 활용

https://school.programmers.co.kr/learn/courses/30/lessons/131116

VERSION 2.0

SELECT A.CATEGORY , A.MAX_PRICE AS MAX_PRICE , B.PRODUCT_NAME FROM 
(SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE FROM FOOD_PRODUCT
GROUP BY CATEGORY
HAVING CATEGORY IN ( '과자', '국', '김치', '식용유')) A LEFT JOIN
FOOD_PRODUCT B ON A.CATEGORY = B.CATEGORY AND A.MAX_PRICE = B.PRICE
ORDER BY MAX_PRICE DESC

VERSION 1.0

1. MAX()함수를 사용하여 CATEGORY 별로 MAX_PRICE를 추출했다

SELECT CATEGORY AS CATEGORY , MAX(PRICE) AS MAX_PRICE   FROM FOOD_PRODUCT 
GROUP BY CATEGORY 
HAVING CATEGORY IN ('과자', '국' , '김치' , '식용유')

2. SELF JOIN 의 LEFT JOIN 활용하여 MAX_PRICE열과 PRICE가 같고, CATEGORY 가 같은것에 한해 LEFT JOIN 실행한다.

SELECT A.* , B.PRODUCT_NAME FROM 
(SELECT CATEGORY AS CATEGORY , MAX(PRICE) AS MAX_PRICE   FROM FOOD_PRODUCT 
GROUP BY CATEGORY 
HAVING CATEGORY IN ('과자', '국' , '김치' , '식용유')) A LEFT JOIN 
(SELECT CATEGORY, PRODUCT_NAME , PRICE FROM FOOD_PRODUCT) B ON A.MAX_PRICE = B.PRICE AND  A.CATEGORY = B.CATEGORY
ORDER BY MAX_PRICE DESC

https://school.programmers.co.kr/learn/courses/30/lessons/131123

SELECT A.FOOD_TYPE ,B.REST_ID , B.REST_NAME, A.FAVORITES FROM
(SELECT FOOD_TYPE , MAX(FAVORITES) AS FAVORITES  FROM REST_INFO
GROUP BY FOOD_TYPE) A LEFT JOIN
REST_INFO B
ON A.FOOD_TYPE = B.FOOD_TYPE AND A.FAVORITES = B.FAVORITES
ORDER BY A.FOOD_TYPE DESC

==> ON 2개 , FROM 절 서브쿼리 할때 MAX() 처리