[HackerRank] Basic Select - 1

Weather Observation Station 3 ~ 12까지의 문제 및 MySQL 풀이 내용입니다.

 

 

Weather Observation Station 3

https://www.hackerrank.com/challenges/weather-observation-station-3/problem

Weather Observation Station 3 | HackerRank

 

SELECT DISTINCT CITY
FROM STATION
WHERE ID%2 = 0;

• ID number가 짝수인 city이므로, id를 2로 나눈 나머지가 0인 경우를 출력하도록 했다.
• 중복 제거를 위해 DISTINCT를 사용

 

Weather Observation Station 4

https://www.hackerrank.com/challenges/weather-observation-station-4/problem

Weather Observation Station 4 | HackerRank

 

SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION;

• count 문 안에서 distinct를 사용해, 중복여부에 따른 개수 차이를 출력

 

Weather Observation Station 5

https://www.hackerrank.com/challenges/weather-observation-station-5/problem

Weather Observation Station 5 | HackerRank

 

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

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

• Note를 기반으로 두 개의 쿼리로 최솟값, 최댓값을 각각 출력하였다.
• 길이, City name을 기준으로 정렬하되 최솟값은 오름차순(ASC, 기본값)으로, 최댓값은 내림차순(DESC)로 정렬한 뒤, LIMIT 1을 통해 첫값만 출력

 

Weather Observation Station 6

https://www.hackerrank.com/challenges/weather-observation-station-6/problem

Weather Observation Station 6 | HackerRank

 

# SUBSTR
SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY, 1, 1) IN ('A', 'E', 'I', 'O', 'U');

# LEFT
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U');

# LIKE
SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE 'A%'
    OR CITY LIKE 'E%'
    OR CITY LIKE 'I%'
    OR CITY LIKE 'O%'
    OR CITY LIKE 'U%';

• SUBSTR(문자, 시작 위치, 글자 수) 혹은 LEFT(문자, 글자 수)를 통해 첫 글자가 모음에 해당하는지 확인할 수 있다.
  • SUBSTR, SUBSTRING 혹은 MID의 경우 원하는 위치에서부터 원하는 수만큼의 글자를 추출할 수 있고,
    LEFT는 가장 왼쪽부터 원하는 수만큼, RIGHT는 오른쪽부터 원하는 수만큼의 글자를 추출할 수 있다.
• LIKE를 통해 문자열 패턴을 확인할 수도 있다. 와일드카드로 %, _ 사용 가능
  • % : 하나 이상의 문자열
  • _ : 하나의 문자

 

 

Weather Observation Station 7

https://www.hackerrank.com/challenges/weather-observation-station-7/problem

Weather Observation Station 7 | HackerRank

 

SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY, 1) IN ('A', 'E', 'I', 'O', 'U');

• Weather Observation Station 6번 문제와 유사하지만, 시작이 아닌 마지막 문자열을 확인하는 것이므로 RIGHT를 사용하였다.

 

Weather Observation Station 8

https://www.hackerrank.com/challenges/weather-observation-station-8/problem

Weather Observation Station 8 | HackerRank

 

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');

 

 

 

Weather Observation Station 9

https://www.hackerrank.com/challenges/weather-observation-station-9/problem

Weather Observation Station 9 | HackerRank

 

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) NOT IN ('A', 'E', 'I', 'O', 'U');

• 모음으로 시작하지 않는 경우이므로 NOT IN 사용

 

 

Weather Observation Station 10

https://www.hackerrank.com/challenges/weather-observation-station-10/problem

Weather Observation Station 10 | HackerRank

SELECT DISTINCT CITY 
FROM STATION
WHERE RIGHT(CITY, 1) NOT IN ('A', 'E', 'I', 'O', 'U');

 

 

 

Weather Observation Station 11

https://www.hackerrank.com/challenges/weather-observation-station-11/problem

Weather Observation Station 11 | HackerRank

 

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');

 

 

 

Weather Observation Station 12

https://www.hackerrank.com/challenges/weather-observation-station-12/problem

Weather Observation Station 12 | HackerRank

 

SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY, 1) NOT IN ('A', 'E', 'I', 'O', 'U')
    AND RIGHT(CITY, 1) NOT IN ('A', 'E', 'I', 'O', 'U');