[SQL 연습 문제] 코딩 테스트 대비 해커랭크 easy 문제 풀이(2)

사용한 키워드

- SELECT, FROM, DISTINCT, WHERE, LIKE, LEFT, RIGHT, IN, NOT IN, ORDER BY, CASE WHEN 

 

 

1.  Weather Observation Station 6

Weather Observation Station 6 | HackerRank

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%'

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

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou].*';

정규표현식 사용

REGEXP (패턴)

ex) REGEXP('^[aeiou].*')

^:시작한다 / [aeiou]: [__] 목록에 있는 것중에 하나로 / .*: %와 비슷한 의미(0개 이상의 어떤 문자열이 와도 상관없음)

 

 

2. Weather Observation Station 7

Weather Observation Station 7 | HackerRank

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

 

 

3. Weather Observation Station 8

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

문자열 자르기

- LEFT(문자열, 길이)

- RIGHT(문자열, 길이)

 

 

4. Weather Observation Station 9

Weather Observation Station 9 | HackerRank

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

 

 

5. Weather Observation Station 10

Weather Observation Station 10 | HackerRank

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

 

 

6. Weather Observation Station 11

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

 

 

7. Weather Observation Station 12

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

 

 

8. Higher Than 75 Marks

Higher Than 75 Marks | HackerRank

SELECT name
FROM students
WHERE marks > 75
ORDER BY RIGHT(name,3), id;

 

 

9. Employee Names

Employee Names | HackerRank

SELECT name
FROM employee
ORDER BY name;

 

 

10. Employee Salaries

Employee Salaries | HackerRank

SELECT name
FROM employee
WHERE salary > 2000 
AND months < 10
ORDER BY employee_id;