문제

https://www.hackerrank.com/challenges/weather-observation-station-5/problem?isFullScreen=true

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.
The STATION table is described as follows:

ID int YES NULL
CITY varchar(21) YES NULL
STATE varchar(2) YES NULL
LAT_N decimal(21,10) YES NULL
LONG_W decimal(21,10) YES NULL

예시 답안

ABC 3
PQRS 4

풀이

  1. 가장 짧은 CITY 이름 찾기

    • LENGTH(CITY)가 최소인 도시
    • 같은 길이가 여러 개면 알파벳 순으로 첫 번째

  2. 가장 긴 CITY 이름 찾기

    • LENGTH(CITY)가 최대인 도시
    • 같은 길이가 여러 개면 알파벳 순으로 첫 번째

  3. 출력 형식: 도시 이름과 길이를 함께 출력

SELECT city, LENGTH(city) as city_length
FROM station
ORDER BY city_length ASC, city ASC
LIMIT 1;

SELECT city, LENGTH(city) as city_length
FROM station
ORDER BY city_length DESC, city ASC
LIMIT 1;
LinkedIn