Weather Observation Station 5

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.

Weather Observation Station 5 | HackerRankHackerRank

Solution:

(SELECT city, LENGTH(city) AS len FROM station WHERE LENGTH(city) = ( SELECT MIN(LENGTH(city)) FROM station ) order by city asc LIMIT 1)

UNION ALL

(SELECT city, LENGTH(city) AS len FROM station WHERE LENGTH(city) = ( SELECT MAX(LENGTH(city)) FROM station ) order by city asc LIMIT 1);

Explanation:

1. SELECT city, LENGTH(city) AS len
   FROM station
   WHERE LENGTH(city) = (SELECT MIN(LENGTH(city)) FROM station)
   ORDER BY city ASC
   LIMIT 1;

2. This part of the query selects the city names and their lengths from the STATION table where the length of the city name is equal to the minimum length of city names in the STATION table. It orders the results by city name in ascending order and limits the output to only one row.

3. UNION ALL

4. This is used to combine the results of two separate queries into a single result set.

5. SELECT city, LENGTH(city) AS len
   FROM station
   WHERE LENGTH(city) = (SELECT MAX(LENGTH(city)) FROM station)
   ORDER BY city ASC
   LIMIT 1;

6. This part of the query selects the city names and their lengths from the STATION table where the length of the city name is equal to the maximum length of city names in the STATION table. It orders the results by city name in ascending order and limits the output to only one row.

7. So, the entire query combines the results of two separate queries using UNION ALL. The first query selects the city with the shortest name, and the second query selects the city with the longest name from the STATION table.