Query data starting and ending with vowels using SQL LOWER(), LENGTH() and SUBSTRING() functions

Write a SQL query to get all cities starting and ending with vowels from the STATION table using LOWER(), LENGTH() and SUBSTRING() functions. This is the solution of HackerRank's Weather Observation Station 8 SQL problem.

Table name: STATION

Table Structure:

Fileds	Type
ID	NUMBER
CITY	VARCHAR
STATE	VARCHAR
LAT_N	NUMBER
LONG_W	NUMBER

 

SQL Query:

SELECT CITY FROM STATION
WHERE LOWER(SUBSTRING(CITY, LENGTH(CITY), 1)) IN ('a', 'e', 'i', 'o', 'u')
AND LOWER(SUBSTRING(CITY, 1, 1)) IN ('a', 'e', 'i', 'o', 'u')