SQL — 101

Introduction

I am starting the revision of SQL concepts. We will begin with the basic questions, then intermediate, and so on.

Question 1

[Ref: Hackerrank]

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.
The STATION table is described as follows:

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

Get Ravi raj’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Solution:

SELECT DISTINCT(CITY) FROM STATION WHERE mod(ID,2) = 0 and LAT_N > 0 and LONG_W >0;

Explanation:

1. To get the even number we are using mode(column_name, 2), column % 2 can also be used.
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]

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
The STATION table is described as follows:

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

For example, if there are three records in the table with CITY values ‘New York’, ‘New York’, ‘Bengaluru’, there are 2 different city names: ‘New York’ and ‘Bengaluru’.

Solution:

SELECT COUNT(CITY) - COUNT(DISTINCT(CITY)) FROM STATION WHERE LAT_N > 0 and LONG_W >0;