문제 출처: 해커랭크 SQL Basic

The STATION table is described as follows:

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.

Sample Output

ABC 3
PQRS 4

STATION 테이블에서 가장 짧은 도시명, 가장 긴 도시명을 오름차 순으로 길이와 함께 출력

풀이

LENGTH() 또는 CHAR_LENGTH() 함수를 이용해서 문자열의 길이를 구한다

코드

SELECT CITY, LENGTH(CITY) FROM STATION
ORDER BY 2 ASC, 1 ASC
LIMIT 1;

SELECT CITY, LENGTH(CITY) FROM STATION
ORDER BY 2 DESC, 1 ASC
LIMIT 1;

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.

풀이

1. 정규표현식을 이용해서 모음으로 시작하는 도시명을 조건에 넣어준다.
2. LEFT(문자열, 1)로 시작 문자를 찾는다.

코드

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP "^[aeiou].*";

*모음으로 끝나는 문자를 찾으려면 [aeiou]$

SELECT DISTINCT CITY FROM STATION 
WHERE LEFT(CITY,1) IN ('a','e','i','o','u');

*끝나는 문자를 찾으려면 RIGHT(CITY, 1) IN ('a','e','i','o','u')

시작/끝이 모두 모음인 도시 구하기

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP "^[aeiou].*[aeiou]$";

SELECT DISTINCT CITY FROM STATION 
WHERE LEFT(CITY,1) IN ('a','e','i','o','u') AND 
RIGHT(CITY,1) IN ('a','e','i','o','u');

모음으로 시작하지 않는/끝나지 않는 도시 구하기

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP "^[^aeiou].*";

SELECT DISTINCT CITY FROM STATION 
WHERE LEFT(CITY,1) NOT IN ('a','e','i','o','u');

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP "[^aeiou]$";

SELECT DISTINCT CITY FROM STATION 
WHERE RIGHT(CITY,1) NOT IN ('a','e','i','o','u');

모음으로 시작하지 않거나 끝나지 않는 도시 구하기

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE "^[^aieou]|.*[^aeiou]$"

모음으로 시작하지도 않고 끝나지도 않는 도시 구하기

SELECT DISTINCT CITY FROM STATION 
WHERE CITY RLIKE "^[^aieou].*[^aeiou]$"