My Dev Blog

SELECT MAX(CASE WHEN A.OCCUPATION = 'Doctor' THEN NAME END) AS 'Doctor'
     , MAX(CASE WHEN A.OCCUPATION = 'Professor' THEN NAME END) AS 'Professor'
     , MAX(CASE WHEN A.OCCUPATION = 'Singer' THEN NAME END) AS 'Singer'
     , MAX(CASE WHEN A.OCCUPATION = 'Actor' THEN NAME END) AS 'Actor'
  FROM (
         SELECT NAME
              , OCCUPATION
              , ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS RN
           FROM OCCUPATIONS
       ) A
GROUP BY A.RN

해커랭크 Occupations SQL

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