Introduction

The HackerRank SQL challenge "Weather Observation Station 7" is an excellent problem to practice string pattern matching using SQL's LIKE operator. This exercise helps improve your filtering skills using conditions that involve text endings.

Problem Statement

Query all distinct city names from the STATION table where the city name ends with a vowel. The vowels are A, E, I, O, and U.

SQL Query Solution


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';

Explanation

  • The % symbol is a wildcard in SQL that matches any sequence of characters.
  • LIKE '%a' matches cities that end with the letter 'a'.
  • This pattern is repeated for all vowels: 'e', 'i', 'o', and 'u'.
  • The DISTINCT keyword ensures that duplicate city names are removed from the output.
  • If your SQL environment is case-sensitive, you might need to use LOWER(city) to ensure proper matching.

Example Test Cases

Test Case 1

Input Table:

CITY
---------
Lima
Helsinki
Chicago
Oslo
Zurich

Expected Output:


Lima
Helsinki
Chicago
Oslo

Explanation: All cities except "Zurich" end with vowels.

Test Case 2

Input Table:

CITY
---------
Boston
Rome
Beijing
Montevideo
Abu Dhabi

Expected Output:


Rome
Montevideo
Abu Dhabi

Explanation: These three cities end with 'e', 'o', and 'i', which are vowels.

Conclusion

This problem is a great introduction to filtering strings based on their last character in SQL. By mastering these basic string functions, you'll be better equipped to handle more complex SQL queries that involve text patterns and data validation.

Related Problems for Practice