[코드카타 연습하기]코드카타 _16일차(137-144)

137. 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:

SELECT CITY, LEN
  FROM (
        SELECT CITY,
        LENGTH(CITY) LEN,
        ROW_NUMBER() OVER(ORDER BY LENGTH(CITY), CITY) as RN_MIN,
        ROW_NUMBER() OVER(ORDER BY LENGTH(CITY) DESC, CITY) as RN_MAX
        FROM STATION
       )A
 WHERE (RN_MIN = 1 OR RN_MAX = 1)
 ORDER BY LEN;

138. 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 DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^a|^e|^i|^o|^u';

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[AEIOU]'

139. 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 CITY REGEXP 'a$|e$|i$|o$|u$';

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[aeiou]$'

140. 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 CITY REGEXP '^[AEIOU].*[aeiou]$';

141. 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 CITY NOT REGEXP '^[AEIOU]';

142. 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 CITY NOT REGEXP '[aeiou]$';

143. 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 CITY NOT REGEXP '^[AEIOU].*[aeiou]$';

144. 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 CITY NOT REGEXP '^[AEIOU]'
AND CITY NOT REGEXP '[aeiou]$';

인사이트

정규식 함수의 활용.
정규식

규칙을 암기하지 않아, 구글링을 하며 풀어나가야했다.
모든 규칙을 알지는 못하지만, 기본적인 구조를 이해할 수 있는 시간이었다.