[HackerRank] Basic SELECT S2

문제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 CITY
     , LENGTH(CITY)
 FROM ( SELECT CITY
             , LENGTH(CITY)
          FROM STATION
          ORDER BY LENGTH(CITY), CITY)
 WHERE ROWNUM =1
UNION
SELECT CITY
     , LENGTH(CITY)
 FROM ( SELECT CITY
             , LENGTH(CITY)
          FROM STATION
          ORDER BY LENGTH(CITY) DESC, CITY)
 WHERE ROWNUM =1;

• MAX(), MIN() 함수를 써서 풀려고 했다가 꽤 많이 헤맸던 문제였다.
• 결국 이 문제는 정렬을 해서 하나만 가져와야하는 문제였다.

문제2

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from

2_1

SELECT CITY
 FROM STATION
 WHERE (CITY LIKE 'A%'
         OR CITY LIKE 'E%'
         OR CITY LIKE 'I%'
         OR CITY LIKE 'O%'
         OR CITY LIKE 'U%')
 GROUP BY CITY;

2_2

SELECT CITY
 FROM STATION
 WHERE REGEXP_LIKE(CITY, '^A|^E|^I|^O|^U')
 GROUP BY CITY;

• 정규식을 사용한 예제인데, 정규식을 처음 사용해봤지만 이걸 사용해야 통과된다...
• ^ : 문자열의 시작을 의미

문제3

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

3_1

SELECT CITY
 FROM STATION
 WHERE (CITY LIKE '%a'
        OR CITY LIKE '%e'
        OR CITY LIKE '%i'
        OR CITY LIKE '%o'
        OR CITY LIKE '%u')
 GROUP BY CITY;

3_2

SELECT CITY
 FROM STATION
 WHERE REGEXP_LIKE(CITY, 'a$|e$|i$|o$|u$')
 GROUP BY CITY;

• & : 문자열의 끝을 의미