[SQL문제풀기] Weather Observation Station 12

문제

Weather Observation Station 11 | HackerRank

Query a list of CITY names not starting or ending with vowels.

https://www.hackerrank.com/challenges/weather-observation-station-11/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%')
    or (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]'
    or city not regexp '[aeiou]$';

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

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

내가 작성한 정답4 - left & right

select distinct city
from station
where left(city,1) not in ('a','e','i','o','u')
    or 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%')
    or (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]') 
    or not regexp_like(city,'[aeiou]$');

내가 작성한 정답3 - substr

select distinct city
from station
where substr(city,1,1) not in ('A','E','I','O','U')
    or 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%')
    or (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]%'
    or city not like '%[aeiou]';

내가 작성한 정답3 - left & right

select distinct city
from station
where left(lower(city),1) not in ('a','e','i','o','u')
    or 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%')
    or (city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u');

정리!