테이블 구조

문제1

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 MIN(a01.city) AS city  -- 알파뱃 최소
      ,a01.len
FROM
(--도시, 도시글자수, 최대, 최소
SELECT city
      ,LENGTH(city) AS len
      ,MAX(length(city)) OVER() AS max_len
      ,MIN(length(city)) OVER() AS min_len
  FROM station
 ) a01
WHERE a01.len IN (a01.max_len, a01.min_len)
GROUP BY a01.len
             ;

1.over 문을 이용하여 최대 최소 구함.
2.min을 이용하여, 알파뱃 최솟값 출력.(a부터)

문제2

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

SELECT city
  FROM station
 WHERE SUBSTR(city,1,1) IN ('A', 'E', 'I', 'O','U')
 ;

1.SUBSTR 사용하여, 단어 짜르기

문제3

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

SELECT distinct city
  FROM station
 WHERE SUBSTR(city,-1,1) IN ('a','e','i','o','u');

Your result cannot contain duplicates.
→ 유니크 값을 구해라

문제4

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

SELECT DISTINCT city
  FROM station
 WHERE (
        SUBSTR(city,1,1) in ('A','E','I','O','U')
        AND SUBSTR(city,-1,1) in ('a','e','i','o','u')
        );

문제5

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

SELECT DISTINCT city
  FROM station
 WHERE SUBSTR(city,1,1) NOT IN ('A','E','I','O','U');

문제6

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT city
  FROM station
WHERE SUBSTR(city,-1,1) NOT IN ('a','e','i','o','u');

문제7

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT city
  FROM station
 WHERE (
         SUBSTR(city,1,1) NOT IN ('A','E','I','O','U')
         OR SUBSTR(city,-1,1) NOT IN ('a','e','i','o','u')
        );

문제8

Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT city
  FROM station
 WHERE (
         SUBSTR(city,1,1) NOT IN ('A','E','I','O','U')
        AND SUBSTR(city,-1,1) NOT IN ('a','e','i','o','u')
        );

문제9

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.

SELECT ROUND(MEDIAN(LAT_N,4)
FROM STATION;