Solution to HackerRank SQL Practice Questions

HackerRank SQL Question 10

…

Difficulty Level — Easy to Medium

…

Question: 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.

Input Format

The STATION table is described as follows:

Solution 1: Using REGEXP Expression in SQL Server

SELECT DISTINCT CITY FROM STATION 
WHERE lower(CITY) REGEXP '^[aeiou].*[aeiou]$';

Solution 2: Using SUBSTRING and IN Functions in SQL Server

SELECT CITY FROM STATION 
WHERE SUBSTRING(LOWER(CITY),1,1) IN ('a','e','i','o','u') and 
SUBSTRING(LOWER(CITY),-1,1) in ('a','e','i','o','u');

Using SUBSTRING and IN Operators in SQL Server

Solution 3: Using Left and Right Functions in SQL Server

SELECT DISTINCT CITY FROM STATION 
WHERE LEFT(UPPER(CITY),1) IN ('A','E','I','O','U') 
AND RIGHT(UPPER(CITY),1) IN ('A','E','I','O','U');

Using Left and Right Functions in SQL Server

Solution 4: Using LIKE, OR, and AND Functions in SQL Server

SELECT CITY FROM STATION WHERE 
(CITY LIKE 'A%'
 OR CITY LIKE 'E%'
 OR CITY LIKE 'I%'
 OR CITY LIKE 'O%'
 OR CITY LIKE 'U%'
 )
 AND
 (CITY LIKE '%A'
 OR CITY LIKE '%E'
 OR CITY LIKE '%I'
 OR CITY LIKE '%O'
 OR CITY LIKE '%U'
       )

Using LIKE, OR, and AND Functions in SQL Server

…

PS — I’ve used the UPPER and LOWER functions for City names to create uniformity in data and to avoid any chances of error due to case sensitivity.