뭐든지 다 알아보자

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

SELECT B.USER_ID , B.NICKNAME ,B.ADDRESS AS 전체주소 ,B.TLNO AS 전화번호  FROM
(SELECT WRITER_ID ,  COUNT(WRITER_ID) FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(WRITER_ID)>=3) A LEFT JOIN 
(SELECT USER_ID , NICKNAME , CITY ||' '|| STREET_ADDRESS1 || ' '|| STREET_ADDRESS2 AS ADDRESS, SUBSTR(TLNO,0,3)||'-'||SUBSTR(TLNO,4,4)||'-'||SUBSTR(TLNO,8,4)  AS TLNO  FROM USED_GOODS_USER) B
ON A.WRITER_ID = B.USER_ID
ORDER BY B.USER_ID DESC

==> SUBSTR(열 , 시작번호, 끝번호)

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

SELECT B.FILE_PATH FROM 
(SELECT A.BOARD_ID, A.WRITER_ID, A.VIEWS FROM USED_GOODS_BOARD A FULL OUTER JOIN

USED_GOODS_FILE B
ON A.BOARD_ID = B.BOARD_ID
GROUP BY A.BOARD_ID, A.WRITER_ID, A.VIEWS
ORDER BY A.VIEWS DESC
FETCH FIRST 1 ROWS ONLY) A LEFT JOIN


(SELECT BOARD_ID, FILE_ID,  '/home/grep/src/'||BOARD_ID ||'/'|| FILE_ID || FILE_NAME || FILE_EXT AS FILE_PATH
FROM USED_GOODS_FILE) B

ON A.BOARD_ID = B.BOARD_ID

==> 글자를 입력하고 싶으면 || 하기

==> FETCH FIRST 1 ROWS ONLY

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

SELECT CAR_ID , ROUND(AVG(END_DATE - START_DATE+1 ),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING ROUND(AVG(END_DATE - START_DATE+1 ),1) >=7
ORDER BY AVERAGE_DURATION DESC , CAR_ID DESC

==> 날짜간 뺄셈은 1을 더해야 한다!

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

SELECT A.CAR_ID  FROM

(SELECT * FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = '세단') A INNER JOIN

(SELECT DISTINCT(CAR_ID) FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE TO_CHAR(START_DATE , 'MM') = 10) B

ON A.CAR_ID = B.CAR_ID
ORDER BY A.CAR_ID DESC

==> INNER JOIN으로 교집합 하기, DISTINCT() 중복 제거!

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

SELECT BOARD_ID, WRITER_ID , TITLE, PRICE,  CASE
    WHEN STATUS = 'SALE' THEN '판매중'
    WHEN STATUS = 'DONE' THEN '거래완료'
    ELSE '예약중' END AS STATUS FROM USED_GOODS_BOARD 
    WHERE TO_CHAR(CREATED_DATE, 'YYYY-MM-DD') = '2022-10-05'
    ORDER BY BOARD_ID DESC

==> CASE WHEN THEN 활용하기!!!!

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

SELECT A.ANIMAL_ID , A.ANIMAL_TYPE , A.NAME FROM ANIMAL_INS A LEFT JOIN ANIMAL_OUTS B
ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE '%Intact%' AND (B.SEX_UPON_OUTCOME LIKE '%Spayed%' or B.SEX_UPON_OUTCOME LIKE '%Neutered%')
ORDER BY A.ANIMAL_ID

==> INNER JOIN 해도 되고, LEFT JOIN 해도 된다

==> LIKE 문은 리스트 안에 있는 값을 바탕으로 추출이 힘들다.

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

SELECT A.PRODUCT_ID, B.PRODUCT_NAME , SUM(A.AMOUNT * B.PRICE) AS TOTAL_SALES FROM (
(SELECT PRODUCT_ID , PRODUCE_DATE ,SUM(AMOUNT) AS AMOUNT 
    FROM FOOD_ORDER
        GROUP BY PRODUCT_ID , PRODUCE_DATE HAVING TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05' ORDER BY PRODUCT_ID) A INNER JOIN FOOD_PRODUCT B
        ON A.PRODUCT_ID = B.PRODUCT_ID)
GROUP BY A.PRODUCT_ID , B.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC , A.PRODUCT_ID ASC

==> FOOD_PRODUCT 에 다있는게 아니더라!!! ==> INNER JOIN을 통해 교집합 생성하는것이 좋다!!!

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

SELECT A.BOOK_ID AS BOOK_ID, B.AUTHOR_NAME AS AUTHOR_NAME , TO_CHAR(A.PUBLISHED_DATE,'YYYY-MM-DD')  AS PUBLISHED_DATE
    FROM BOOK A LEFT JOIN AUTHOR B 
        ON A.AUTHOR_ID = B.AUTHOR_ID 
    WHERE A.CATEGORY = '경제'
    ORDER BY A.PUBLISHED_DATE ASC