SQL — 102 (Basics)

Introduction

This series is the revision of SQL concepts. We are doing the basic questions, then intermediate, and so on.

Previous Posts

1. SQL 101

Question 1

[Ref: Hackerrank]

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

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

Solution:

SELECT DISTINCT(CITY) FROM STATION WHERE CITY REGEXP “^[aeiou]” AND LAT_N > 0 and LONG_W >0;

Explanation:

1. To match the start of the string, we use the Caret (^) that matches the position before the first character in the string. [Ref]
2. The lines of latitude and longitude divide Earth into four hemispheres: Northern, Southern, Eastern, and Western. The United States lies in both the Northern Hemisphere and the Western Hemisphere. That’s because it lies west of the prime meridian or 0° of longitude, and north of the equator, or 0° of latitude. [Ref]

Question 2

[Ref: Hackerrank]

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
The STATION table is described as follows:

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

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS, and WXY.

Sample Output

ABC 3
PQRS 4

Explanation

Get Ravi raj’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths and. The longest name is PQRS, but there are options for the shortest named city. Choose ABC, because it comes first alphabetically.

Note
You can write two separate queries to get the desired output. It need not be a single query.

Solution:

SELECT CITY, LENGTH(CITY) as CITY_LENGTH FROM STATION WHERE LAT_N > 0 and LONG_W >0 ORDER BY LENGTH(CITY), CITY ASC LIMIT 1;
SELECT CITY, LENGTH(CITY) as CITY_LENGTH FROM STATION WHERE LAT_N > 0 and LONG_W >0 ORDER BY LENGTH(CITY) DESC LIMIT 1;

Question 3

[Ref: Hackerrank]

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:

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

Solution:

SELECT DISTINCT(CITY) FROM STATION WHERE CITY REGEXP “[aeiou]$” AND LAT_N > 0 and LONG_W >0;

Explanation:

1. To match the end of the string, we use the Dollar ($) that matches the position right after the last character in the string. [Ref]

Question 4

[Ref: Hackerrank]

Query the list of CITY names from STATION which has 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:

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

Solution:

SELECT DISTINCT(CITY) FROM STATION WHERE CITY REGEXP “^[aeiou].*[aeiou]$” AND LAT_N > 0 and LONG_W >0;

Explanation:

1. Caret (^) will match the first character, (.*) means zero or more characters, and at the end Dollar ($) will match the last character.