본문 바로가기

전체 글216

[SQL] 해커랭크 Type of Triangle SELECT CASE WHEN A = B AND B = C THEN 'Equilateral' WHEN A + B 2022. 12. 2.
[SQL] 해커랭크 Weather Observation Station 6~7 SELECT DISTINCT CITY FROM STATION WHERE (CITY LIKE 'a%' OR CITY LIKE 'e%' OR CITY LIKE 'i%' OR CITY LIKE 'o%' OR CITY LIKE 'u%') a로 시작하는 문자열을 검색 : 'a%' a로 시작하는 3글자의 문자열 검색 : 'a__' SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]' 정규 표현식 REGEXP : LIKE보다 복잡한 문자열 조건으로 검색 가능 ^[aeiou] : a 또는 e 또는 i 또는 o 또는 u로 시작하는 문자열 SELECT DISTINCT CITY FROM STATION WHERE (CITY LIKE '%a' OR CITY LIKE '.. 2022. 12. 2.
[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.

티스토리툴바