[프로그래머스] Oracle SQL_1(Lv3)

1. 조건별로 분류하여 주문상태 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/131113?language=oracle

SELECT 
    ORDER_ID,
    PRODUCT_ID,
    TO_CHAR(OUT_DATE,'YYYY-MM-DD') AS "OUT_DATE",
    CASE
        WHEN TO_CHAR(OUT_DATE,'YYYY-MM-DD') <= '2022-05-01' THEN '출고완료'
        WHEN TO_CHAR(OUT_DATE,'YYYY-MM-DD') > '2022-05-01' THEN '출고대기'
        ELSE '출고미정'
    END AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID;

 

2. 카테고리 별 도서 판매량 집계하기

https://school.programmers.co.kr/learn/courses/30/lessons/144855?language=oracle

SELECT
    B.CATEGORY,
    SUM(S.SALES) AS "TOTAL_SALES"
FROM BOOK_SALES S
    JOIN BOOK B ON S.BOOK_ID = B.BOOK_ID
WHERE TO_CHAR(S.SALES_DATE, 'YYYY-MM') = '2022-01'
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY;

 

3. 오랜 기간 보호한 동물(1)

https://school.programmers.co.kr/learn/courses/30/lessons/59044?language=oracle

SELECT 
    NAME,
    DATETIME
FROM ANIMAL_INS 
WHERE ANIMAL_ID NOT IN (
    SELECT ANIMAL_ID
    FROM ANIMAL_OUTS
)
ORDER BY DATETIME
FETCH FIRST 3 ROWS ONLY;

 

4. 대여 기록이 존재하는 자동차 리스트 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157341?language=oracle

SELECT 
	DISTINCT H.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    JOIN CAR_RENTAL_COMPANY_CAR INFO ON H.CAR_ID = INFO.CAR_ID
WHERE INFO.CAR_TYPE = '세단'
    AND TO_CHAR(H.START_DATE, 'MM') = '10'
ORDER BY CAR_ID DESC;

 

5. 조건에 맞는 사용자와 총 거래금액 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/164668?language=oracle

SELECT 
    U.USER_ID,
    U.NICKNAME,
    SUM(B.PRICE) AS "TOTAL_SALES"
FROM USED_GOODS_BOARD B
    JOIN USED_GOODS_USER U ON B.WRITER_ID= U.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY 
    U.USER_ID,
    U.NICKNAME
HAVING SUM(B.PRICE) >= 700000
ORDER BY "TOTAL_SALES";