Introduction

The HackerRank problem "Weather Observation Station 6" is a beginner-level SQL challenge that focuses on string pattern matching using the LIKE clause. The objective is to select cities from the STATION table where the city name begins with a vowel.

Problem Statement

Retrieve all city names from the STATION table where the city name starts with a vowel (A, E, I, O, or U).

SQL Query Solution

SELECT CITY
FROM STATION
WHERE CITY LIKE 'A%' 
   OR CITY LIKE 'E%' 
   OR CITY LIKE 'I%' 
   OR CITY LIKE 'O%' 
   OR CITY LIKE 'U%';

Explanation

• LIKE 'A%' matches any city name that begins with 'A'.
• The same applies for the other vowels: 'E', 'I', 'O', and 'U'.
• The OR operator ensures that all conditions are considered in the filtering.
• We assume case-sensitive matching. If your SQL engine requires it, consider using UPPER() or LOWER().

Example Test Cases

Test Case 1

Input Table:

CITY
---------
Amsterdam
Boston
Oslo
Eugene
Zurich

Expected Output:

Amsterdam
Oslo
Eugene

Explanation: Only "Amsterdam", "Oslo", and "Eugene" begin with a vowel.

Test Case 2

Input Table:

CITY
---------
Delhi
Paris
Istanbul
Uppsala
Chicago

Expected Output:

Istanbul
Uppsala

Explanation: "Istanbul" and "Uppsala" start with vowels.

Conclusion

This SQL exercise tests your understanding of pattern matching using the LIKE operator. It’s essential in filtering data based on text patterns, especially when working with unstructured or semi-structured text fields. Mastering these basics prepares you for more complex string operations in SQL.

Related Problems for Practice

• Weather Observation Station 7 – Filter cities ending with vowels.
• Weather Observation Station 9 – Cities starting and ending with vowels.
• Select By ID – Simple record filtering by ID.