My Dev Blog

SELECT CONCAT(NAME, '(', SUBSTR(OCCUPATION, 1, 1), ')')
  FROM OCCUPATIONS
 ORDER BY NAME;

SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION), 's.') 
  FROM OCCUPATIONS
 GROUP BY OCCUPATION
 ORDER BY COUNT(OCCUPATION), OCCUPATION

해커랭크 The PADS SQL

SELECT CASE WHEN A = B AND B = C THEN 'Equilateral'
            WHEN A + B <= C OR B + C <= A OR C + A <= B THEN 'Not A Triangle'
            WHEN A = B OR B = C OR C = A THEN 'Isosceles'
            ELSE 'Scalene' END
  FROM TRIANGLES

A + B <= C OR B + C <= A OR C + A <= B THEN 'Not A Triangle' 조건이

A = B OR B = C OR C = A THEN 'Isosceles' 조건보다 먼저 나와야 하는 이유

A = B OR B = C OR C = A THEN 'Isosceles 조건이 먼저 나오게 되면

A = B = 1이고 C = 2인 경우 삼각형 조건이 성립하지 않지만,

Isosceles(이등변 삼각형)라는 값을 RETURN하기 때문이다.

해커랭크 Type of Triangle SQL

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 '%e' OR
        CITY LIKE '%i' OR
        CITY LIKE '%o' OR
        CITY LIKE '%u')

SELECT DISTINCT CITY
  FROM STATION
 WHERE CITY REGEXP '[aeiou]$'

[aeiou]$ : a 또는 e 또는 i 또는 o 또는 u로 끝나는 문자열

해커랭크 Weather Observation Station SQL

(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 LENGTH(CITY) DESC, CITY ASC
  LIMIT 1)

UNION ALL을 쓰지 않고 세미콜론(;)으로 이어줘도 된다.

해커랭크 Weather Observation Station SQL

<MySQL>

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, B.REVIEW_TEXT

<Oracle>

SELECT A.MEMBER_NAME
     , B.REVIEW_TEXT
     , TO_CHAR(B.REVIEW_DATE, 'YYYY-MM-DD') 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 MAX(COUNT(MEMBER_ID)) AS CNT
                                                      FROM REST_REVIEW
                                                  GROUP BY MEMBER_ID                 
                                                  )
                      )
 ORDER BY B.REVIEW_DATE, B.REVIEW_TEXT

프로그래머스 그룹별 조건에 맞는 식당 목록 출력하기 SQL

<MySQL & Oracle>

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

<MySQL>

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

<Oracle>

SELECT A.REST_ID
     , A.REST_NAME
     , A.FOOD_TYPE
     , A.FAVORITES
     , A.ADDRESS
     , ROUND(AVG(NVL(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, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS
 ORDER BY ROUND(AVG(NVL(B.REVIEW_SCORE, 0)), 2) DESC, A.FAVORITES DESC

평균 계산 함수 AVG 사용 시 주의할 점

SCORE 값이 NULL이거나 빈 값일 경우 0으로 평균 계산에 포함시키려면

<MySQL> : IFNULL(NULLIF(컬럼, ''), 컬럼 값이 NULL이거나 빈 값일 경우 보여줄 값)

=> AVG(IFNULL(NULLIF(SCORE, ''), 0))

<Oracle> : NVL(컬럼, 컬럼 값이 NULL이거나 빈 값일 경우 보여줄 값)

=> AVG(NVL(SCORE, 0))

프로그래머스 서울에 위치한 식당 목록 출력하기 SQL

<MySQL>

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-%m') = '2022-03'
       ) A
ORDER BY A.SALES_DATE, A.PRODUCT_ID, A.USER_ID

<Oracle>

SELECT A.SALES_DATE
     , A.PRODUCT_ID
     , A.USER_ID
     , A.SALES_AMOUNT
  FROM (
         SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
              , PRODUCT_ID
              , USER_ID
              , SALES_AMOUNT
           FROM ONLINE_SALE
          WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
      UNION ALL 
         SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE
              , PRODUCT_ID
              , NULL AS USER_ID
              , SALES_AMOUNT
           FROM OFFLINE_SALE
          WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
       ) A
ORDER BY A.SALES_DATE, A.PRODUCT_ID, A.USER_ID

<NULL 값을 먼저 또는 나중에 출력되도록 정렬 방법>

MySQL : ORDER BY 컬럼 IS NULL (ASC / DESC)

Oracle : ORDER BY 컬럼 (ASC / DESC) NULLS (FIRST / LAST)

프로그래머스 오프라인/온라인 판매 데이터 통합하기 SQL