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

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

Sample Output

ABC 3
PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths  and . The longest name is PQRS, but there are  options for shortest named city. Choose ABC, because it comes first alphabetically.

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

 

City name의 길이가 긴 것과 길이가 짧은거 출력, 알파벳 순서로 출력

1)

SELECT city, length(city)
FROM station
WHERE length(city) = max(length(city))

-> Rutime Error :(

length(city)의 길이가 큰 값을 추출 할려고 했는데 실행이 되지 않았다. 

 

2)

SELECT city, LENGTH(city)
FROM station
ORDER BY LENGTH(city) asc
LIMIT 1;

SELECT city, LENGTH(city)
FROM station
ORDER BY Length(city) desc
LIMIT 1;

out put

Roy 3

city LENGTH(city)

Marine On Saint Croix 21

 

-> 왜 중간에 city LENGTH(city) 가 나온지 모르겠다.

 

3)

새로 고침후 다시 시도 

3-1)

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

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city) DESC,city 
LIMIT 1;

• Amo 3 

• Marine On Saint Croix 21 

 

3-2)

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

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city) DESC 
LIMIT 1;

• Roy 3 

• Marine On Saint Croix 21 

3-2는 Output이 같게 나왔는데 오답으로 처리가 됬다

아무래도 알파벳 순서를 해줘야 되는 것 같다. LENGTH(city) 기준으로 각각 오름차순(내리차순)을 정렬 후 name 기준으로 오름차순으로 정렬을 해줘야 되는 것 같다. 문제상 문자 길이가 가장 긴, 가장 짧은 것이 두개만 나와서 크게 다를게 없어 보인다. 짧은 길이가 3개인 것이 몇 개 더 있어 그 것을 포함 시키고 실행을 해봤다.

 

4) 추가 학습

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city)
LIMIT 3;

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city) DESC 
LIMIT 1;

.

• Roy 3 

• Lee 3 

• Amo 3 

• Marine On Saint Croix 21 

문자 길이가 가장 짧은 3개의 이름 과 가장 큰 문자 길이를 출력을 했다. 여기서는 알파벳 순서대로 정렬이 되지 않았다.

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city), city
LIMIT 3;

SELECT city, LENGTH(city) 
FROM station 
ORDER BY LENGTH(city) DESC 
LIMIT 1;

• Amo 3 

• Lee 3 

• Roy 3 

• Marine On Saint Croix 21 

밑에 코드는 한개라 정렬을 진행하지 않고 위에 쿼리식에만 적용을 했다.