[프로그래머스]Oracle SQL_3(Lv4)

1. 저자 별 카테고리 별 매출액 집계하기

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

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

 

2. 서울에 위치한 식당 목록 출력하기

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

SELECT 
    I.REST_ID,
    I.REST_NAME,
    I.FOOD_TYPE,
    I.FAVORITES AS "FAVORITES"	,
    I.ADDRESS,
    ROUND(AVG(R.REVIEW_SCORE),2) AS "SCORE"
FROM REST_INFO I
    JOIN REST_REVIEW R ON R.REST_ID = I.REST_ID
WHERE I.ADDRESS LIKE '서울%'
GROUP BY I.REST_ID, I.REST_NAME, I.FOOD_TYPE, I.FAVORITES, I.ADDRESS
ORDER BY "SCORE" DESC, "FAVORITES" DESC;

POINT!  주소지가 서울이기 때문에 맨앞에 '서울'이라는 키워드가 들어가야 정답 처리가 된다