[ SQL ] HackerRank Basic - MySQL 답안 정리(2) - Weather Observation Station Questions

Weather Observation Station 01 ~ 05

// 01
SELECT CITY, STATE FROM STATION

// 02
SELECT DISTINCT CITY FROM STATION WHERE ID % 2 = 0

// 03 
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION

// 04
SELECT CITY, LENGTH(CITY) FROM STATION GROUP BY CITY ORDER BY LENGTH(CITY),CITY LIMIT 1;

// 05
SELECT CITY, LENGTH(CITY) FROM STATION GROUP BY CITY ORDER BY LENGTH(CITY) DESC, CITY LIMIT 1;

 

Weather Observation Station 06

// 방법 1 - Like 활용
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%'

// 방법 2 - REGEXP_LIKE 활용
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY, '^a|^e|^i|^o|^u')

// 방법 2 + 알파 - REGEXP_LIKE 표현 깔끔하게
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY, '^[aeiou]')

REGEXP_LIKE 는 LIKE와 달리 정규표현식을 활용해서 여러 조건을 동시에 만족하는 SQL문을 짤 수 있다. 확실히 코드만 봐도 이해하기 쉽고 깔끔하다. 하지만 SQL Injection에 취약할 수 있고, LIKE 보다 성능이 좋지 않아서 (=오래걸려서) 인덱싱을 쓰는 경우가 아니라면 LIKE를 사용하는게 더 낫다고 한다. (https://www.mysqltutorial.org/mysql-regular-expression-regexp.aspx/ 참고)

 

Weather Observation Station 07

// 방법 1
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY,'a$|e$|i$|o$|u$')

// 방법 2 
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY,'(a|e|i|o|u)$')

// 방법 3
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY,'[a|e|i|o|u]$')

// 방법 4
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY,'[a|e|i|o|u]$')

// 방법 5 - 제일 깔끔하고 이해하기 쉽다 :)
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY,'[aeiou]$')

 

Weather Observation Station 08

// 방법 1
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY, '^(a|e|i|o|u)') AND REGEXP_LIKE (CITY, '(a|e|i|o|u)$')

// 방법 2
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY, '^[aeiou].*[aeiou]$') 

// 주의 할 것: 브라켓([])을 사용하지 않고 괄호(()) ex- (aeiou) 를 쓰면 에러날 수 있음

이왕이면 브라켓( [ ] )을 사용하자

 

Weather Observation Station 09 - 10

// 9
SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE (CITY, '^[aeiou]')

// 10
SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE (CITY, '[aeiou]$')

 

Weather Observation Station 11 - 12

// 11
SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE (CITY, '^(a|e|i|o|u)') OR NOT REGEXP_LIKE (CITY, '(a|e|i|o|u)$')

// 12
SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE (CITY, '^(a|e|i|o|u)') AND NOT REGEXP_LIKE (CITY, '(a|e|i|o|u)$')