JOIN 유형 모음 [프로그래머스].oracle (SQL 고득점 키트)

프로그래머스 SQL 문제 중 다시 한번 풀어볼 만한 JOIN 유형을 모아 둔 글입니다.

 

상품 별 오프라인 매출 구하기 

SELECT PRODUCT_CODE ,SALES
FROM (SELECT a.PRODUCT_CODE, SUM(a.PRICE * b.SALES_AMOUNT) AS SALES
      FROM PRODUCT a JOIN OFFLINE_SALE b on a.PRODUCT_ID = b.PRODUCT_ID
      GROUP BY a.PRODUCT_CODE
     )
ORDER BY SALES DESC, PRODUCT_CODE

 

SELECT P.PRODUCT_CODE
,      SUM(P.PRICE * OS.SALES_AMOUNT) AS SALES
FROM PRODUCT P JOIN OFFLINE_SALE OS ON P.PRODUCT_ID = OS.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, P.PRODUCT_CODE;

 

Q. 위 코드와 아래 코드 중 어느 코드가 더 효율적인 것일까? 

문제 출처 

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

 

없어진 기록 찾기

• 문제 출처 https://school.programmers.co.kr/learn/courses/30/lessons/59042

---

주문량이 많은 아이스크림들 조회하기 oracle

이런식으로 더하면 되는구나 ! 

SELECT T.FLAVOR 
FROM (
    SELECT A.FLAVOR, SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER) AS TOTAL
    FROM FIRST_HALF A JOIN JULY B ON A.FLAVOR = B.FLAVOR
    GROUP BY A.FLAVOR
    ORDER BY TOTAL DESC
) T
WHERE ROWNUM <= 3

 

문제 출처 

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

 

5월 식품들의 총매출 조회하기

 

grup by 2 개 이상 잘 모르겠네 

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT ) AS TOTAL_SALES
FROM FOOD_PRODUCT P JOIN FOOD_ORDER O ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE TO_CHAR(O.PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY P.PRODUCT_ID, P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID

 

문제 출처 

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