[Hacker Rank] 231206

# 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 A.city
from station A
where mod(A.id, 2) = 0
;

 

 

# 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.

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.

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

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

 

 

# Weather Observation Station 8

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 A.city
from station A
where substring(city, 1, 1) in ('a', 'e', 'i', 'o', 'u')
and right(city, 1) in ('a', 'e', 'i', 'o', 'u')
;

REGEXP_LIKE : LIKE를 이용한 검색과 달리 Regular Expression(정규 표현식)을 이용해 검색할 수 있다. 복잡한 문자열 조건을 걸어서 데이터를 검색한다.

select distinct city
from station
where regexp_like(city, '^[aeiou]')
    and regexp_like(city, '[aeiou]$')
;