[SQL문제풀기] Weather Observation Station 10

문제

Weather Observation Station 10 | HackerRank

Query a list of CITY names not ending in vowels.

https://www.hackerrank.com/challenges/weather-observation-station-10/problem?isFullScreen=true

MySQL

내가 작성한 정답1 - like

select distinct city
from station
where city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u';

내가 작성한 정답2 - 정규표현식

select distinct city
from station
where city not regexp '[aeiou]$';

내가 작성한 정답3 - substring, substr 둘 다 사용가능

select distinct city
from station
where substr(city,-1,1) not in ('a','e','i','o','u');
    
select distinct city
from station
where substring(city,-1,1) not in ('a','e','i','o','u');

내가 작성한 정답4 - right

select distinct city
from station
where right(city,1) not in ('a','e','i','o','u');

Oracle

내가 작성한 정답1 - like

select distinct city
from station
where city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u';

내가 작성한 정답2 - 정규표현식 regexp_like

select distinct city
from station
where not regexp_like(lower(city),'[aeiou]$');

내가 작성한 정답3 - substr

select distinct city
from station
where substr(city,-1,1) not in ('a','e','i','o','u');

MS SQL Server - MySQL과 유사

내가 작성한 정답1 - like

select distinct city
from station
where city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u';

내가 작성한 정답2 - like2

select distinct city
from station
where city not like '%[aeiou]';

내가 작성한 정답3 - right

select distinct city
from station
where right(city,1) not in ('a','e','i','o','u');

DB2 - Oracle과 유사

내가 작성한 정답 - like

select distinct city
from station
where city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u';

정리!