# SQL] Hackerrank quiz

분명 Easy 단계인데 오랜만에 sql 써보려니 헷갈리던 문제

## hackerrank - Weather Observation Station 5

Problem

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.

Sample Input

Let’s say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output

ABC 3
PQRS 4

• 러프한 답안
``````SELECT
city,
CHAR_LENGTH(city)
FROM STATION
WHERE city = (
SELECT
MIN(city)
FROM STATION
WHERE CHAR_LENGTH(city) = (
SELECT
MIN(CHAR_LENGTH(city))
FROM STATION
)
)
OR city = (
SELECT
MIN(city)
FROM STATION
WHERE CHAR_LENGTH(city) = (
SELECT
MAX(CHAR_LENGTH(city))
FROM STATION
)
);

``````

유저 지정 변수를 이용

• 데이터가 많지 않을 경우에는 오히려 더 오래 걸린다.
``````# find shortest city name
SET @MinCityLen = (SELECT MIN(CHAR_LENGTH(city)) FROM STATION);
# find longest city name
SET @MaxCityLen = (SELECT MAX(CHAR_LENGTH(city)) FROM STATION);

SELECT
city,
CHAR_LENGTH(city)
FROM
STATION
WHERE
# find shortest city name sorted alphabetically
city = (
SELECT
city
FROM STATION
WHERE CHAR_LENGTH(city) = @MinCityLen
ORDER BY city ASC
LIMIT 1
)
# find longest city name sorted alphabetically
OR city = (
SELECT
MIN(city)
FROM STATION
WHERE CHAR_LENGTH(city) = @MaxCityLen
ORDER BY city ASC
LIMIT 1
);
``````

쿼리를 두 번 날리는 답안

• 굳이 복잡하게 할 필요 없는 것 같다.
• 아마 출제자도 이 답안을 생각하고 내지 않았을까.

Note
You can write two separate queries to get the desired output. It need not be a single query.

``````SELECT name, LENGTH(name) FROM CITY ORDER BY LENGTH(name), name ASC LIMIT 1;
SELECT name, LENGTH(name) FROM CITY ORDER BY LENGTH(name) DESC LIMIT 1;
``````

태그:

카테고리:

업데이트: