Solution of Weather Observation Station 8
Problem Statement : 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.
where LAT_N is the northern latitude and LONG_W is the western longitude.
Solution :
For this challenge, we must:
Use a Distinct() function to get unique cities.
Restrict the selected rows using the WHERE clause so that only records where CITY names start with vowels and end with vowels are returned.
Query :
SELECT Distinct CITY
FROM STATION
WHERE lower(substr(CITY, 1, 1)) in ('a', 'e', 'i', 'o', 'u') and lower(substr(CITY, length(CITY), 1)) in ('a', 'e', 'i', 'o', 'u');
Watch below video for regular expression solution:
Output:
Hope you learn something !!!
If you have any questions let me know in the comment section.