[HackerRank] SQL 문제

The STATION table is described as follows:

문제 1. weather-observation-station-3 link

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.
where LAT_N is the northern latitude and LONG_W is the western longitude.

 

풀이

중복되지 않는 결과를 output하기 위해서는 SELECT 뒤에 DISTINCT를 붙입니다.

SELECT DISTINCT CITY 
FROM STATION
WHERE ID % 2 = 0;

 

 

 

문제2. weather-observation-station-5  link

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:

 

풀이

CITY 길이의 최댓값과 최댓값은 ORDER BY로 내림차순, 오름차순 정렬하여 가장 위에있는 1개(LIMIT)를 고르도록 했고, 이때 정렬이 되는 기준은 1. CITY 길이 2. CITY 알파벳 순이었습니다.

 

먼저 위 방식으로 최댓값(혹은 최솟값) 1개를 조회되도록 select를 하여 테이블을 만들고,

그 테이블에서 문제의 output을 select하도록 했습니다.

SELECT a.CITY, LENGTH(a.CITY) 
FROM (
    SELECT CITY
    FROM STATION
    ORDER BY LENGTH(CITY), CITY
) a
LIMIT 1;

SELECT a.CITY, LENGTH(a.CITY) 
FROM (
    SELECT CITY
    FROM STATION
    ORDER BY LENGTH(CITY) DESC, CITY
) a
LIMIT 1;