HackerRank SQL Problems and Solutions — 3

Problem 1

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:

Figure 1. Station table

Where LAT_N is the northern latitude and LONG_W is the western longitude.

Query

SELECT DISTINCT CITY
FROM STATION
WHERE 1=1
      AND CITY LIKE '[AEIOU]%[aeiou]'

Output

Acme
Aguanga
Alba
Aliso Viejo
Alpine
Amazonia
Amo
Andersonville
Archie
Arispe
Arkadelphia
Atlantic Mine
East China
East Irvine
Eastlake
Eleele
Elm Grove
Eriline
Ermine
Eskridge
Eufaula
Oconee
Ojai
Osborne
Oshtemo
Ozona
Upperco
Urbana

Problem 2

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

Query

SELECT DISTINCT CITY
FROM STATION
WHERE 1=1
      AND CITY NOT LIKE '[AEIOU]%';

Output

Baileyville
Bainbridge
Baker
Baldwin
Barrigada
Bass Harbor
Baton Rouge
Bayville
Beaufort
Beaver Island
Bellevue
Benedict
Bennington
Bentonville
Berryton
Bertha
Beverly
Biggsville
Bison
Blue River
Bono
Bowdon
Bowdon Junction
Bridgeport
Bridgton
Brighton
Brilliant
Bristol
Brownsdale
Brownstown {-truncated-}

Problem 3

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

Query

SELECT DISTINCT CITY
FROM STATION
WHERE TRUE
      AND CITY NOT LIKE '%[AEIOU]';

Output

Addison
Agency
Alanson
Albany
Albion
Algonac
Allerton
Alton
Andover
Anthony
Arlington
Arrowsmith
Athens
Auburn
Baker
Baldwin
Bass Harbor
Beaufort
Beaver Island
Benedict
Bennington
Berryton
Beverly {-truncated-}

Problem 4

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

Query