[SQL] review_HackerRank

 

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

⇒ city 컬럼 출력, city의 첫 글자와 마지막 글자가 모두 모음, 중복x

Field	Type
ID	NUMBER
CITY	VARCHAR2(21)
STATE	VARCHAR2(2)
LAT_N	NUMBER
LONG_W	NUMBER

 

Solution1)

SELECT DISTINCT city     -- 중복X
FROM station
WHERE city LIKE 'a%a'
OR city LIKE 'a%e'
OR city LIKE 'a%i'
OR city LIKE 'a%o'
OR city LIKE 'a%u'

OR city LIKE 'e%a'
OR city LIKE 'e%e'
OR city LIKE 'e%i'
OR city LIKE 'e%o'
OR city LIKE 'e%u'

OR city LIKE 'i%a'
OR city LIKE 'i%e'
OR city LIKE 'i%i'
OR city LIKE 'i%o'
OR city LIKE 'i%u'

OR city LIKE 'o%a'
OR city LIKE 'o%e'
OR city LIKE 'o%i'
OR city LIKE 'o%o'
OR city LIKE 'o%u'

OR city LIKE 'u%a'
OR city LIKE 'u%e'
OR city LIKE 'u%i'
OR city LIKE 'u%o'
OR city LIKE 'u%u'

→ 위와 같이 모든 경우의 수를 단순히 나열할 수 있지만 너무 길고, 효율이 떨어짐

Solution2)

SELECT DISTINCT city
FROM station
WHERE LEFT(city,1) IN ('a','e','i','o','u')     -- city의 첫 글자가 모음
AND RIGHT(city,1) IN ('a','e','i','o','u')     -- city의 마지막 글자가 모음

 

→ LEFT, RIGHT 함수를 이용해 간단하게 표현할 수 있음

 

 

 

 

[Weather Observation Station 9]

▶Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

⇒ city 컬럼 출력, city의 첫 글자가 모음이면 안됨, 중복x

Field	Type
ID	NUMBER
CITY	VARCHAR2(21)
STATE	VARCHAR2(2)
LAT_N	NUMBER
LONG_W	NUMBER

 

Solution1)

SELECT DISTINCT city     -- 중복X
FROM station
WHERE city NOT LIKE 'a%'         -- 모음이면 안되는 경우이므로, NOT LIKE 사용
AND city NOT LIKE 'e%'
AND city NOT LIKE 'i%'
AND city NOT LIKE 'o%'
AND city NOT LIKE 'u%'

 

Solution2)

SELECT DISTINCT city
FROM station
WHERE LEFT(city, 1) NOT IN ('a','e','i','o','u')     -- city의 첫 글자가 모음X

→ 마찬가지로, LEFT 함수를 이용하면 간단하게 표현할 수 있다.

 

 

 

 

[Weather Observation Station 10]

▶ Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

⇒ city 컬럼 출력, city의 마지막 글자가 모음이면 안됨, 중복x

Field	Type
ID	NUMBER
CITY	VARCHAR2(21)
STATE	VARCHAR2(2)
LAT_N	NUMBER
LONG_W	NUMBER

 

SELECT DISTINCT city     -- 중복X
FROM station
WHERE RIGHT(city,1) NOT IN ('a','e','i','o','u')     -- city의 마지막 글자가 모음X

 

 

 

 

[Weather Observation Station 11]

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

⇒ city 컬럼 출력, city의 첫 글자와 마지막 글자 모두 모음이면 안됨, 중복x

Field	Type
ID	NUMBER
CITY	VARCHAR2(21)
STATE	VARCHAR2(2)
LAT_N	NUMBER
LONG_W	NUMBER

 

SELECT DISTINCT city     -- 중복X
FROM station
WHERE LEFT(city,1) NOT IN ('a','e','i','o','u')     -- city의 첫 글자가 모음X
OR RIGHT(city,1) NOT IN ('a','e','i','o','u')     -- city의 마지막 글자가 모음X

 

 

 

---

 

• LIKE를 사용할 수도 있지만, LEFT/RIGHT를 사용하면 훨씬 간단하게 나타낼 수 있다.
• sql 강의 중급반 완강한 후, 오늘 풀었던 문제 다른 방법으로 접근해보기!!
• sql 강의 초급반 완강 :D