본문 바로가기

SQL56

[SQL] 해커랭크 Weather Observation Station 5 (SELECT CITY, LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION) ORDER BY CITY LIMIT 1) UNION ALL (SELECT CITY, LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY LIMIT 1) (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC, CITY ASC LIMIT 1); (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGT.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 그룹별 조건에 맞는 식당 목록 출력하기 SELECT A.MEMBER_NAME , B.REVIEW_TEXT , DATE_FORMAT(B.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE FROM MEMBER_PROFILE A , REST_REVIEW B WHERE A.MEMBER_ID = B.MEMBER_ID AND B.MEMBER_ID IN ( SELECT MEMBER_ID FROM REST_REVIEW GROUP BY MEMBER_ID HAVING COUNT(MEMBER_ID) = ( SELECT COUNT(MEMBER_ID) AS CNT FROM REST_REVIEW GROUP BY MEMBER_ID ORDER BY COUNT(MEMBER_ID) DESC LIMIT 1 ) ) ORDER BY B.REVIEW_DATE,.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 식품분류별 가장 비싼 식품의 정보 조회하기 SELECT A.CATEGORY , A.PRICE AS MAX_PRICE , A.PRODUCT_NAME FROM ( SELECT CATEGORY , PRICE , PRODUCT_NAME , RANK() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS PRICE_RANK FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') ) A WHERE A.PRICE_RANK = 1 ORDER BY A.PRICE DESC 프로그래머스 식품분류별 가장 비싼 식품의 정보 조회하기 SQL 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 서울에 위치한 식당 목록 출력하기 SELECT A.REST_ID , A.REST_NAME , A.FOOD_TYPE , A.FAVORITES , A.ADDRESS , ROUND(AVG(IFNULL(NULLIF(B.REVIEW_SCORE, ''), 0)), 2) AS SCORE FROM REST_INFO A , REST_REVIEW B WHERE A.REST_ID = B.REST_ID AND A.ADDRESS LIKE '서울%' GROUP BY A.REST_ID ORDER BY ROUND(AVG(IFNULL(NULLIF(B.REVIEW_SCORE, ''), 0)), 2) DESC, A.FAVORITES DESC SELECT A.REST_ID , A.REST_NAME , A.FOOD_TYPE , A.FAVORITES , A.ADDRESS , .. 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 오프라인/온라인 판매 데이터 통합하기 SELECT A.SALES_DATE , A.PRODUCT_ID , A.USER_ID , A.SALES_AMOUNT FROM ( SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , USER_ID , SALES_AMOUNT FROM ONLINE_SALE WHERE DATE_FORMAT(SALES_DATE, '%Y-%m') = '2022-03' UNION ALL SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE , PRODUCT_ID , NULL AS USER_ID , SALES_AMOUNT FROM OFFLINE_SALE WHERE DATE_FORMAT(SALES_DATE, '%Y.. 2022. 12. 2.
[SQL] 프로그래머스 [Level-4] 취소되지 않은 진료 예약 조회하기 SELECT A.APNT_NO , B.PT_NAME , B.PT_NO , A.MCDP_CD , C.DR_NAME , A.APNT_YMD FROM APPOINTMENT A , PATIENT B , DOCTOR C WHERE A.PT_NO = B.PT_NO AND A.MDDR_ID = C.DR_ID AND DATE_FORMAT(A.APNT_YMD, '%Y-%m-%d') = '2022-04-13' AND A.APNT_CNCL_YN = 'N' AND A.MCDP_CD = 'CS' ORDER BY A.APNT_YMD SELECT A.APNT_NO , B.PT_NAME , B.PT_NO , A.MCDP_CD , C.DR_NAME , A.APNT_YMD FROM APPOINTMENT A , PATIENT B , DO.. 2022. 12. 2.

티스토리툴바