Weather Observation Station 11 (SUBSTRING, NOT IN, LEFT, RIGHT)

 

[문제]

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.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

 

---

 

[나의 풀이]

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

 

출력결과

CITY

Addison

Agency

: :

Bass Harbor

 

---

 

[설명]

1. 문자열 추출하기

SELECT city, SUBSTRING(city, 1, 1), SUBSTRING(city, -1, 1)
    FROM station

 

출력결과

CITY	SUBSTRING(CITY, 1, 1)	SUBSTRING(CITY, -1, 1)
Kissee Mills	K	s
Loma Mar	L	r
: :	: :	: :

 

[문자열 자르기]

1. SUBSTR(문자열, 시작 위치, 가져올 길이)

    SUBSTRING(문자열, 시작 위치, 가져올 길이)

예) SUBSTRING('abcdefg', 3, 4)    > 'cdef'

 

2. LEFT(문자열, 길이)         

 예) LEFT('abcdefg', 3)    > 'abc'

 

3. RIGHT(문자열, 길이)

예) RIGHT('abcdefg', 3)    > 'efg'

 

 

 

 

2. 첫 문자, 마지막 문자에 모음이 없는 CITY명 추출하기

SELECT city
    FROM station
    WHERE SUBSTRING(city, 1, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
    OR SUBSTRING(city, -1, 1) NOT IN ('a', 'e', 'i', 'o', 'u')

 

출력결과

CITY

Kissee Mills

Loma Mar

: :

 

[IN 연산자]

WHERE Condition IN ('A','B')

= WHERE Condition = 'A'  OR  Condition = 'B'  

 

> Condition이 A OR B인지 확인

 

 

 

 

 

3. 중복 제거하기

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

 

출력결과

CITY

Kissee Mills

Loma Mar

: :

 

 

---

 

[다른 사람 풀이]

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

> 문자열 추출하기를 이용하여 작성하였다.

> LEFT, RIGHT 관련 설명은 [나의 풀이]의 1. 문자열 추출하기에 나와있다.