[SQL문제풀기] Weather Observation Station 8

문제

Weather Observation Station 8 | HackerRank

Query CITY names that start AND end with vowels.

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

MySQL

내가 작성한 정답1 - like

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

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

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

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

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

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

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

Oracle

내가 작성한 정답1 - like

select distinct city
from station
where (city like 'I%' or city like 'E%' or city like 'A%' or city like 'O%' or city like 'U%')
  and (city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u');

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

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

내가 작성한 정답3 - substr

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

MS SQL Server - MySQL과 유사

내가 작성한 정답1 - like

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

내가 작성한 정답2 - like2

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

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

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

DB2 - Oracle과 유사

내가 작성한 정답 - like

select distinct city
from station
where (city like 'I%' or city like 'E%' or city like 'A%' or city like 'O%' or city like 'U%')
  and (city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u');

정리!