Hackerrank - SQL - Weather Observation Station 5
#sql#hackerrank#beginners#learning
Mr Punk da Silva

Mr Punk da Silva @mrpunkdasilva

About: I'm a Brazilian Software Engineer passionate about creating elegant solutions for complex problems. I specialize in full-stack development with a focus on clean code, solid architecture, and exception

Location:
Punk Domus
Joined:
Jul 19, 2023

Hackerrank - SQL - Weather Observation Station 5

Publish Date: Jul 25 '25
0 0

https://github.com/mrpunkdasilva/hackerrank/tree/main/sql/basic/weather-observartion-station-5

Problem Description

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.

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

This problem requires two separate queries:

  1. First query to find the city with the shortest name
  2. Second query to find the city with the longest name

For each query, we need to:

  • Select the CITY name and its length
  • Order the results appropriately
  • Use LIMIT to get only the first result

Step-by-Step Explanation

Query 1: Finding the city with the shortest name

  1. Select the CITY name and calculate its length: 
   SELECT CITY, LENGTH(CITY)
Enter fullscreen mode Exit fullscreen mode
  1. Specify the table to query from: 
   FROM STATION
Enter fullscreen mode Exit fullscreen mode
  1. Add a WHERE clause to ensure we only consider valid entries: 
   WHERE (LAT_N IS NOT NULL) AND (LONG_W IS NOT NULL)
Enter fullscreen mode Exit fullscreen mode
  1. Order the results by length (ascending) and then alphabetically: 
   ORDER BY LENGTH(CITY), CITY ASC
Enter fullscreen mode Exit fullscreen mode
  1. Limit the result to just the first row: 
   LIMIT 1
Enter fullscreen mode Exit fullscreen mode

Query 2: Finding the city with the longest name

  1. Select the CITY name and calculate its length: 
   SELECT CITY, LENGTH(CITY)
Enter fullscreen mode Exit fullscreen mode
  1. Specify the table to query from: 
   FROM STATION
Enter fullscreen mode Exit fullscreen mode
  1. Add a WHERE clause to ensure we only consider valid entries: 
   WHERE (LAT_N IS NOT NULL) AND (LONG_W IS NOT NULL)
Enter fullscreen mode Exit fullscreen mode
  1. Order the results by length (descending) and then alphabetically: 
   ORDER BY LENGTH(CITY) DESC, CITY DESC
Enter fullscreen mode Exit fullscreen mode
  1. Limit the result to just the first row: 
   LIMIT 1
Enter fullscreen mode Exit fullscreen mode

Expected Output

The output will consist of two rows:

  1. The first row will contain the name of the city with the shortest name and its length
  2. The second row will contain the name of the city with the longest name and its length

Comments 0 total

    Add comment