Hackerrank - SQL - Weather Observation Station 3

#sql #learning

Repo

Problem Description

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.

Input Format

The STATION table is described as follows:

Field	Type
ID	NUMBER
CITY	VARCHAR2(21)
STATE	VARCHAR2(2)
LAT_N	NUMBER
LONG_W	NUMBER

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

Solution Approach

Use a SELECT statement with the DISTINCT keyword to retrieve unique CITY names
Apply a WHERE clause to filter for cities with even ID numbers (ID % 2 = 0)
Add additional conditions to ensure we only consider valid entries

Step-by-Step Explanation

Start with the SELECT statement using DISTINCT to get unique city names:

   SELECT DISTINCT CITY

Specify the table to query from:

   FROM STATION

Add the WHERE clause with conditions:
- Check for valid latitude and longitude values
- Filter for even ID numbers using the modulo operator (ID % 2 = 0)

   WHERE 
       (LAT_N IS NOT NULL) AND
       (LONG_W IS NOT NULL) AND
       (ID % 2 = 0)

The final query:

   SELECT DISTINCT
       CITY
   FROM
       STATION
   WHERE 
       (LAT_N IS NOT NULL) AND
       (LONG_W IS NOT NULL) AND
       (ID % 2 = 0)
   ;