[해커랭크(HackerRank) SQL 풀이] - Weather Observation Station 8

해커 랭크 - https://www.hackerrank.com/
Prepare > SQL > Basic Select > Weather Observation Station 8

 

HackerRank - Online Coding Tests and Technical Interviews

 

 

STATION에서 첫 번째와 마지막 문자가 모음 (즉, a, e, i, o, u)로 끝나는 CITY 이름을 조회하세요.

결과에 중복된 값이 포함되어서는 안 됩니다.

즉, 첫 문자와 마지막 문자가 모음으로 동일한 CITY 이름을 검색하는 쿼리를 원하는 것입니다.

 

Input Format

STATION 테이블은 다음과 같이 설명됩니다.

​[출처] https://www.hackerrank.com/challenges/weather-observation-station-8/problem

 

여기서 LAT_N은 북위, LONG_W는 서경입니다.

 

반응형

 

MySQL

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

 

Oracle

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

 

MS SQL Server

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

 

---

• MySQL과 MSSQL의 경우 LIKE 조건 검색 시 대소문자 구분 따로 없어도 조회 가능.
• ORACLE의 경우 LIKE 조건 검색 시 대소문자 구분 필요함. 여기서는 소문자로 치환하여 조회함.

 

• 대문자로 치환하여 검색 시 : UPPER()
• 소문자로 치환하여 검색 시 : LOWER()

---

 

 

개인 공부를 위한 포스팅입니다.
모든 번역, 코드는 완벽하지 않을 수 있습니다.