[SQL 공부-프로그래머스] Lv.2 모음 - 3

 

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

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

프로그래머스

SELECT     A.PRODUCT_CODE,
           SUM(B.SALES_AMOUNT)*A.PRICE AS SALES
FROM       PRODUCT AS A
INNER JOIN OFFLINE_SALE AS B ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY   A.PRODUCT_CODE
ORDER BY   SALES DESC, PRODUCT_CODE ASC

 

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

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

프로그래머스

SELECT   USER_ID,
         PRODUCT_ID
FROM     ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1 
ORDER BY USER_ID ASC, PRODUCT_ID DESC

 

진료과별 총 예약 횟수 출력하기

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

프로그래머스

SELECT   MCDP_CD AS 진료과코드,
         COUNT(*) AS 5월예약건수
FROM     APPOINTMENT
WHERE    SUBSTR(APNT_YMD,1,7) = '2022-05'
GROUP BY MCDP_CD
ORDER BY 5월예약건수 ASC, 진료과코드 ASC

 

성분으로 구분한 아이스크림 총 주문량

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

프로그래머스

SELECT     B.INGREDIENT_TYPE,
           SUM(A.TOTAL_ORDER) AS TOTAL_ORDER
FROM       FIRST_HALF AS A
INNER JOIN ICECREAM_INFO AS B ON A.FLAVOR = B.FLAVOR
GROUP BY   B.INGREDIENT_TYPE
ORDER BY   TOTAL_ORDER ASC

 

조건에 맞는 도서와 저자 리스트 출력하기

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

프로그래머스

SELECT     BOOK_ID,
           AUTHOR_NAME,
           DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM       BOOK AS BO
INNER JOIN AUTHOR AS AU ON BO.AUTHOR_ID = AU.AUTHOR_ID
WHERE      BO.CATEGORY = '경제'
ORDER BY   BO.PUBLISHED_DATE ASC

 

자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

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

프로그래머스

SELECT   CAR_TYPE,
         COUNT(*) AS CARS
FROM     CAR_RENTAL_COMPANY_CAR
WHERE    OPTIONS LIKE '%통풍시트%' 
         OR OPTIONS LIKE '%열선시트%' 
         OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC

 

자동차 평균 대여 기간 구하기

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

프로그래머스

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

 

조건에 부합하는 중고거래 상태 조회하기

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

프로그래머스

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

 

조건에 맞는 아이템들의 가격의 총합 구하기

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

프로그래머스

SELECT SUM(PRICE) AS TOTAL_PRICE
FROM   ITEM_INFO
WHERE  RARITY LIKE '%LEGEND%'