SQL 🐬

✏️ Programmers MySQL 최신 문제 풀이 1️⃣ 과일로 만든 아이스크림 고르기 (https://school.programmers.co.kr/learn/courses/30/lessons/133025) / 정답률 70% SELECT FH.FLAVOR FROM FIRST_HALF AS FH JOIN ICECREAM_INFO AS II USING (FLAVOR) WHERE TOTAL_ORDER > 3000 AND INGREDIENT_TYPE = 'fruit_based' ORDER BY TOTAL_ORDER DESC; 2️⃣ 취소되지 않은 진료 예약 조회하기 (https://school.programmers.co.kr/learn/courses/30/lessons/132204) / 정답률 65% SEL..

✏️ Programmers MySQL 정답률 37~54% 전체 풀이 1️⃣ 그룹별 조건에 맞는 식당 목록 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131124) SELECT MP.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATE FROM MEMBER_PROFILE AS MP JOIN ( SELECT RR.MEMBER_ID, REVIEW_TEXT, REVIEW_DATE FROM REST_REVIEW AS RR JOIN ( SELECT MEMBER_ID FROM REST_REVIEW GROUP BY MEMBER_ID ORDER BY COUNT(..

✏️ Programmers MySQL 정답률 55~65% 전체 풀이 1️⃣ 주문량이 많은 아이스크림들 조회하기 (https://school.programmers.co.kr/learn/courses/30/lessons/133027) SELECT FH.FLAVOR FROM FIRST_HALF AS FH INNER JOIN ( SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL FROM JULY GROUP BY FLAVOR ) AS SUB ON FH.FLAVOR = SUB.FLAVOR ORDER BY (TOTAL_ORDER + TOTAL) DESC LIMIT 3; SELECT FH.FLAVOR FROM FIRST_HALF AS FH JOIN JULY AS J ON FH.FLAVOR = J..

✏️ Programmers MySQL 정답률 66~68% 전체 풀이 1️⃣ 5월 식품들의 총매출 조회하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131117) SELECT FO.PRODUCT_ID, FP.PRODUCT_NAME, (FP.PRICE * SUM(FO.AMOUNT)) AS TOTAL_SALES FROM FOOD_PRODUCT AS FP JOIN ( SELECT * FROM FOOD_ORDER WHERE MONTH(PRODUCE_DATE) = 5 ) AS FO ON FP.PRODUCT_ID = FO.PRODUCT_ID GROUP BY FP.PRODUCT_NAME ORDER BY TOTAL_SALES DESC, FO.PRODUCT_..

✏️ Programmers MySQL 정답률 69% 전체 풀이 1️⃣ 재구매가 일어난 상품과 회원 리스트 구하기 SELECT USER_ID,PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID, PRODUCT_ID # USER_ID 기준 한번, PRODUCT_ID 기준 한번 그룹핑 HAVING COUNT(online_sale_id) > 1 ORDER BY USER_ID ASC, PRODUCT_ID DESC; 2️⃣ 헤비 유저가 소유한 장소 (https://school.programmers.co.kr/learn/courses/30/lessons/77487) # 방법 (1) : left join 사용 SELECT P.ID, P.NAME, P.HOST_ID FROM PLACES AS..

✏️ Programmers MySQL 정답률 70~72% 전체 풀이 1️⃣ 진료과별 총 예약 횟수 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/132202) SELECT MCDP_CD AS "진료과코드", COUNT(APNT_NO) AS "5월예약건수" FROM APPOINTMENT WHERE MONTH(APNT_YMD) = 5 GROUP BY MCDP_CD ORDER BY 2, 1; # ORDER BY 5월예약건수 ASC, 진료과코드 ASC; (SELECT index 활용) 2️⃣ 조건별로 분류하여 주문상태 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131113)..