[SQL문제풀기] Weather Observation Station 9

문제

Weather Observation Station 9 | HackerRank

Query an alphabetically ordered list of CITY names not starting with vowels.

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

MySQL

내가 작성한 정답1 - like

select distinct city
from station
where city not like 'I%' and city not like 'E%' and city not like 'A%' 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 - left

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

Oracle

내가 작성한 정답1 - like

select distinct city
from station
where city not like 'I%'
  and city not like 'E%' 
  and city not like 'A%' 
  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(lower(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 'I%' and city not like 'E%' and city not like 'A%' and city not like 'O%' and city not like 'U%';

내가 작성한 정답2 - like2

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

내가 작성한 정답3 - substring

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

내가 작성한 정답4 - left

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

DB2 - Oracle과 유사

내가 작성한 정답1 - like

select distinct city
from station
where city not like 'I%' and city not like 'E%' and city not like 'A%' and city not like 'O%' and city not like 'U%';

내가 작성한 정답2 - substr

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

정리!