https://github.com/mrpunkdasilva/hackerrank/tree/main/sql/basic/weather-observartion-station-7
Problem Description
Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.
Input Format
The STATION table is described as follows:
| Field | Type |
|---|---|
| ID | NUMBER |
| CITY | VARCHAR2(21) |
| STATE | VARCHAR2(2) |
| LAT_N | NUMBER |
| LONG_W | NUMBER |
where LAT_N is the northern latitude and LONG_W is the western longitude.
Solution Approach
- Use a SELECT statement with the DISTINCT keyword to retrieve unique CITY names
- Apply a WHERE clause with conditions:
- Ensure LAT_N and LONG_W are not NULL
- Check if the last character of the CITY name is a vowel using the SUBSTRING function and the IN operator
Step-by-Step Explanation
- Start with the SELECT statement using DISTINCT to get unique city names:
SELECT DISTINCT CITY
- Specify the table to query from:
FROM STATION
- Add the WHERE clause with conditions:
- Check for valid latitude and longitude values
- Use SUBSTRING with a negative index to extract the last character of each CITY name
- Convert it to lowercase with LOWER
- Check if it's a vowel using the IN operator
WHERE
(LAT_N IS NOT NULL) AND
(LONG_W IS NOT NULL) AND
LOWER(SUBSTRING(CITY, -1)) IN ('a', 'e', 'i', 'o', 'u')
- The final query:
SELECT
DISTINCT CITY
FROM
STATION
WHERE
(LAT_N IS NOT NULL) AND
(LONG_W IS NOT NULL) AND
LOWER(SUBSTRING(CITY, -1)) IN ('a', 'e', 'i', 'o', 'u')
;
Expected Output
The query will return a list of unique city names from the STATION table where the last character is a vowel (a, e, i, o, or u).