SQL — 103 (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
2. SQL 102 (Basics)

All 4 questions use the regular expression concept. You can learn more about regular expressions here.

╔═════════╦══════════════════════════════════════════════════════╗
║ Pattern ║                  Matching Patterns                   ║
╠═════════╬══════════════════════════════════════════════════════╣
║ ^       ║ Beginning of string                                  ║
║ $       ║ End of string                                        ║
║ .       ║ Any single character                                 ║
║ *       ║ Zero or more instances of the preceding element      ║
║ [^...]  ║ Any character not listed between the square brackets ║
╚═════════╩══════════════════════════════════════════════════════╝

Question 1

[Ref: Hackerrank]

Query the list of CITY names from STATION that does not start with vowels. 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 NOT REGEXP "^[aeiou]" AND LAT_N > 0 and LONG_W >0;

Question 2

[Ref: Hackerrank]

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

Get Ravi raj’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

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

Note: The STATION table is the same as described in Question 1.

Solution:

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

Question 3

[Ref: Hackerrank]

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.

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

Note: The STATION table is the same as described in Question 1.

Solution:

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

Question 4

[Ref: Hackerrank]

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

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

Note: The STATION table is the same as described in Question 1.

Solution:

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

Explanation:

So, do not start with vowels mean: (^[^aeiou]), then zero or any number of characters (.*) and do not end with vowels ([^aeiou]$).