HackerRank SQL: Weather Observation Station 9~11

23 September 2021 - 2 mins read time
Tags: SQL TIL

Weather Observation Station 9:

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

Oracle

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

select distinct city
from STATION
where not regexp_like(city,'^[AEIOU]');

Weather Observation Station 10:

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

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

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

Weather Observation Station 11:

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

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

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