[프로그래머스] Oracle - (SQL 고득점 Kit) SELECT Level 2,3,4 문제 풀이

3월에 태어난 여성 회원 목록 출력하기

SELECT MEMBER_ID, MEMBER_NAME, GENDER, TO_CHAR(DATE_OF_BIRTH, 'YYYY-mm-dd') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE 
WHERE TO_CHAR(DATE_OF_BIRTH, 'mm') = 03 AND GENDER = 'W' AND TLNO IS NOT NULL
ORDER BY 1

 

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

SELECT info.REST_ID, info.REST_NAME, info.FOOD_TYPE, info.FAVORITES, info.ADDRESS, ROUND(avg(review.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO info JOIN REST_REVIEW review ON info.REST_ID = review.REST_ID 
    AND info.ADDRESS LIKE '서울%'
GROUP BY info.REST_ID
ORDER BY 6 DESC, 4 DESC

 

재구매가 일어난 상품과 회원 리스트 구하기

SELECT USER_ID, PRODUCT_ID
FROM
    (SELECT USER_ID, PRODUCT_ID
     FROM ONLINE_SALE 
     GROUP BY USER_ID, PRODUCT_ID
     HAVING count(USER_ID) > 1)
ORDER BY 1, 2 DESC

 

오프라인/온라인 판매 데이터 통합하기

SELECT TO_CHAR(SALES_DATE, 'YYYY-mm-dd') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM (
    SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE 
    UNION ALL 
    SELECT SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT
    FROM OFFLINE_SALE
)
WHERE TO_CHAR(SALES_DATE, 'YYYYmm') = 202203
ORDER BY 1, 2, 3