HackerRank SQL: Weather Observation Station 6~8

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

Weather Observation Station 6:

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Oracle

select distinct city
from station
where (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 regexp_like (city,'^A|^E|^I|^O|^U');

Weather Observation Station 7:

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

select distinct city
from station
where (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 regexp_like (upper(city),'A$|E$|I$|O$|U$');

Weather Observation Station 8:

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

select distinct city
from station
where (city like 'A%' or city like 'E%' or city like 'I%' 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');

select distinct city
from station
where regexp_like (city,'^A|^E|^I|^O|^U')
AND regexp_like (upper(city),'A$|E$|I$|O$|U$');