[SQL] 해커랭크(HackerRank) - Easy3

1. Weather Observation Station 3

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.

SELECT DISTINCT CITY
FROM STATION
WHERE ID%2=0

ID값이 짝수인  Row의 결과값만을 반환하여 City 컬럼내의 값들을 호출해야한다.
- 짝수인 ID는 2를 나눴을 때 나머지값이 0

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

Weather Observation Station 3 | HackerRank

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.

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

전체 CITY갯수를 COUNT하고 중복을 제거한(DISTINCT) CITY의 수를 빼준다.

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

Weather Observation Station 4 | HackerRank

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.

(
	SELECT CITY, LENGTH(CITY)
	FROM STATION 
	ORDER BY LENGTH(CITY) ASC, CITY ASC
	LIMIT 1
)
UNION
(
	SELECT CITY, LENGTH(CITY)
	FROM STATION 
	ORDER BY LENGTH(CITY) DESC, CITY ASC
	LIMIT 1
)

City의 이름이 가장 짧은 것의 길이와, 가장 이름이 긴 것의 길이를 동시에 출력해야한다.
LENGTH로 길이를 확인하고 오름차순 ASC,내림차순 DESC 정렬을 각각 해주고
또한 길이가 같은 경우 Alphabetically first 인 것을 출력해야하므로 CITY이름으로 오름차순 정렬을 해준다.