[Hacker Rank SQL 정복기] Basic Select - ③

🥇 : Weather Observation Station 9

❓ Question

❗ Answer

SELECT DISTINCT city
FROM station
WHERE NOT left(city,1) IN ('A','E','I','O','U');

📌 Discussion

• IN 뒤의 그룹의 대소문자 구별에 유의
• NOT ~ IN ~ 과 ~ NOT IN ~ 모두 가능

🥇 : Weather Observation Station 10

❓ Question

❗ Answer

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

📌 Discussion

• LOWER(문자), UPPER(문자) 는 문자열을 모두 각각 소문자, 대문자로 변경해준다.

🥇 : Weather Observation Station 11

❓ Question

❗ Answer

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

📌 Discussion

• LEFT나 RIGHT 중 하나라도 모음이 아닌 것을 호출하므로 둘 모두 모음인 것을 제외한 나머지를 호출함

🥇 : Weather Observation Station 12

❓ Question

❗ Answer

SELECT DISTINCT city
FROM station
WHERE NOT (LEFT(city,1) IN ('A','E','I','O','U') OR RIGHT(city,1) IN ('A','E','I','O','U'));

📌 Discussion

• LEFT나 RIGHT 모두 모음이 아닌 것을 호출하므로 둘 중 하나라도 모음인 것을 제외한 나머지를 호출함

🥇 : Higher Than 75 Marks

❓ Question

❗ Answer

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

📌 Discussion

• ORDER BY 에도 slicing 함수 사용 가능

🥇 : Employee Names

❓ Question

❗ Answer

SELECT name
FROM employee
ORDER BY name;

📌 Discussion

• ORDER BY로 정렬하여 호출

🥇 : Employee Salaries

❓ Question

❗ Answer

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

📌 Discussion

• WHERE 절 조건 두개를 AND로 동시 만족