[SQL - HackerRank] Basic Select - 1

1. 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.
   ID가 홀수값인 경우에만 중복 없이 CITY 이름 조회하기
   

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

홀수 찾을 때 ID % 2 = 0 또는 MOD(ID,2) = 0

2. weather-observation-station-4
Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
전체 CITY 개수에서 서로 다른 CITY 개수 빼기

  SELECT COUNT(CITY) - COUNT(DISTINCT(CITY))
  FROM STATION;

3. 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.

가장 긴 city 이름과 가장 짧은 city 이름을 조회하라
각각의 길이도 조회하라
만약 한 개 이상이 나온다면 알파벳 순서로 정렬했을 때 첫 번째로 나오는 것을 골라라

(SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) = 
    (SELECT MIN(LENGTH(CITY))
     FROM STATION)
ORDER BY CITY ASC
LIMIT 1)

UNION

(SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) = 
    (SELECT MAX(LENGTH(CITY))
     FROM STATION)
ORDER BY CITY ASC
LIMIT 1)

• 서브 쿼리 이용
• UNION 이용하여 쿼리 데이터 합치기
  (UNION : 중복 제외, UNION ALL : 중복 포함)