HackerRank: SQL 풀이(11~20)

11. Weather Observation Station 6

• SQL server에서는 REGEXP 이용한 매칭은 불가하고 아래와 같이 LIKE를 사용하여 매칭하여야 함.

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[AEIOUaeiou]%';

아니면 아래와 같이도 가능

SELECT DISTINCT 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%';

12. Weather Observation Station 7

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '%[aeiouAEIOU]';

13. Weather Observation Station 8

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[AEIOUaeiou]%' 
AND CITY LIKE '%[AEIOUaeiou]';

그런데 이렇게 하는 거 외에
PATINDEX를 사용해도 됨.

PAT: Pattern (패턴, 검색할 문자열 형태)
INDEX: Index (문자열 내 위치)

SELECT DISTINCT CITY
FROM STATION
WEHRE PATINDEX('[AEIOUaeiou]%', CITY) = 1
AND PATINDEX('%[AEIOUaeiou]', CITY) > 0;

14. Weather Observation Station 9

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[^AEIOUaeiou]%';

15. Weather Observation Station 10

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '%[^AEIOUaeiou]';

16. Weather Observation Station 11

SELECT
FROM STATION
WHERE CITY LIKE '[^AEIOUaeiou]%'
OR CITY LIKE '%[^AEIOUaeiou]';

아니면 PATINDEX 써서

SELECT DISTINCT CITY
FROM STATION
WHERE PATINDEX('%[^AEIOUaeiou]', CITY) > 0
OR PATINDEX('[^AEIOUaeiou]%', CITY) = 1;

17. Weather Observation Station 12

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE '[^AEIOUaeiou]%'
AND CITY LIKE '%[^AEIOUaeiou]';

18. Higher Than 75 Marks

SELECT NAME
FROM STUDENTS
WHERE Marks > 75
ORDER BY RIGHT(NAME, 3), ID ASC;

LEFT 함수:
왼쪽부터 지정한 길이만큼 추출
예시: LEFT('abcdef', 3) → 'abc'
RIGHT 함수:
오른쪽부터 지정한 길이만큼 추출
예시: RIGHT('abcdef', 3) → 'def'

19. Employee Names

SELECT NAME
FROM Employee
ORDER BY NAME ASC;

20. Employee Salaries

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