#70. JOIN : 5월 식품들의 총매출 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(O.AMOUNT * P.PRICE) AS TOTAL_SALES FROM FOOD_PRODUCT AS P JOIN FOOD_ORDER AS O ON P.PRODUCT_ID = O.PRODUCT_ID WHERE O.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31' GROUP BY O.PRODUCT_ID ORDER BY TOTAL_SALES DESC, O.PRODUCT_ID ASC; 조건1. 생산일자가 2022년 5월인 식품 # 1. BETWEEN WHERE O.PRODUCE_DATE BETWEEN '2022-05-01' AND '2022-05-31' # 2. LIKE WHERE ..

• format_list_bulleted SQL
• · 2023. 8. 29.
• textsms

#69. JOIN : 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 SELECT CAR.CAR_ID, CAR.CAR_TYPE, ROUND((CAR.DAILY_FEE * ((100 - DISCOUNT.DISCOUNT_RATE)/100) * 30)) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS CAR JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS RENT ON CAR.CAR_ID = RENT.CAR_ID JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS DISCOUNT ON CAR.CAR_TYPE = DISCOUNT.CAR_TYPE WHERE CAR.CAR_ID NOT IN( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE EN..

• format_list_bulleted SQL
• · 2023. 8. 28.
• textsms

#68. JOIN : 주문량이 많은 아이스크림들 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. JOIN - ON SELECT FH.FLAVOR FROM FIRST_HALF AS FH JOIN JULY AS JUL ON FH.FLAVOR = JUL.FLAVOR GROUP BY FH.FLAVOR ORDER BY SUM(FH.TOTAL_ORDER) + SUM(JUL.TOTAL_ORDER) DESC LIMIT 3; 2. JOIN - USING SELECT FLAVOR FROM FIRST_HALF AS A JOIN JULY AS B USING (FLAVOR) GROUP BY FLAVOR ORDER BY SUM(A.TOTAL_ORDER + B.TOTAL_ORDER) DESC LIMIT 3; → 조인할 두 컬럼의 이름이 같은 경우에는 ON 대신에 USING을 사용할 수 있음 3. SUBQ..

• format_list_bulleted SQL
• · 2023. 8. 28.
• textsms

#51. GROUP BY : 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. MAX SELECT CAR_ID, MAX(CASE WHEN '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') AND DATE_FORMAT(END_DATE, '%Y-%m-%d') THEN '대여중' ELSE '대여 가능' END) AS 'AVAILABILITY' FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC; SELECT CAR_ID, MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN '대여중' ELSE '대여 가능' END) AS AVAILABILITY FROM CAR_RENTAL..

• format_list_bulleted SQL
• · 2023. 8. 14.
• textsms

#50. GROUP BY : 카테고리 별 도서 판매량 집계하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드 1. JOIN SELECT book.CATEGORY, SUM(sales.SALES) AS TOTAL_SALES FROM BOOK AS book JOIN BOOK_SALES AS sales ON book.BOOK_ID = sales.BOOK_ID WHERE sales.SALES_DATE LIKE "2022-01%" GROUP BY book.CATEGORY ORDER BY book.CATEGORY ASC; 2. WHERE SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES FROM BOOK AS B,BOOK_SALES AS S WHERE (B.BOOK_ID = S.BOOK_ID) AND (SALES_DATE LIKE "2022-01%") GROUP BY CATEGOR..

• format_list_bulleted SQL
• · 2023. 8. 14.
• textsms

#49. GROUP BY : 조건에 맞는 사용자와 총 거래금액 조회하기 | 프로그래머스 | SQL 고득점 Kit

문제 입력 코드1. WHERE SELECT USER.USER_ID, USER.NICKNAME, SUM(BOARD.PRICE) AS TOTAL_SALES FROM USED_GOODS_BOARD AS BOARD JOIN USED_GOODS_USER AS USER ON BOARD.WRITER_ID = USER.USER_ID WHERE BOARD.STATUS = 'DONE' GROUP BY BOARD.WRITER_ID HAVING TOTAL_SALES >= 700000 ORDER BY TOTAL_SALES ASC; 2. SUBQUERYSELECT U.USER_ID, U.NICKNAME, SUM(PRICE) AS TOTAL_SALES FROM ( SELECT * FROM USED_GOODS_BOARD WHERE ..

• format_list_bulleted SQL
• · 2023. 8. 13.
• textsms