[공부로그] SQL HackerRank 문제풀이 (Basic Select)

Q. Revising the Select Query I

SELECT *
FROM CITY
WHERE COUNTRYCODE = 'USA' AND POPULATION > 100000

sql은 ==을 안쓴다!

Q. Revising the Select Query II

SELECT NAME 
FROM CITY
WHERE COUNTRYCODE = 'USA' AND POPULATION > 120000

Q. Select All

SELECT *
FROM CITY

Q. Select By ID

SELECT *
FROM CITY 
WHERE ID = 1661

Q. Japanese Cities' Attributes

SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN'

Q. Japanese Cities' Names

SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN'

Q. Weather Observation Station 1

SELECT CITY, STATE
FROM STATION

Q. Weather Observation Station 3

SELECT DISTINCT CITY 
FROM STATION
WHERE ID % 2 = 0

SQL에서 나머지 구하는 법 '%'

Q. Weather Observation Station 4

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

Q. Weather Observation Station 5

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

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

LIMIT 쓰는 것에 익숙해지자!

Q. Weather Observation Station 6

SELECT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) IN ('a','e','i','o','u')

substr함수와 1부터 시작한다는 거 기억하기

Q. Weather Observation Station 7

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,-1,1) IN ('a','e','i','o','u')

Q. Weather Observation Station 8

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) IN ('a','e','i','o','u') AND SUBSTR(CITY,-1,1) IN ('a','e','i','o','u')

Q. Weather Observation Station 9

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) NOT IN ('a','e','i','o','u')

Q. Weather Observation Station 10

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,-1,1) NOT IN ('a','e','i','o','u')

Q. Weather Observation Station 11

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) NOT IN ('a','e','i','o','u') OR SUBSTR(CITY,-1,1) NOT IN ('a','e','i','o','u')

Q. Weather Observation Station 12

SELECT DISTINCT CITY
FROM STATION
WHERE SUBSTR(CITY,1,1) NOT IN ('a','e','i','o','u') AND SUBSTR(CITY,-1,1) NOT IN ('a','e','i','o','u')

Q. Higher Than 75 Marks

SELECT NAME
FROM STUDENTS
WHERE MARKS > 75
ORDER BY SUBSTR(NAME, -3, 3), ID

RIGHT 함수를 사용해도 된다. RIGHT(NAME,3)

Q. Employee Names

SELECT NAME
FROM EMPLOYEE
ORDER BY NAME

Q. Employee Salaries

SELECT NAME
FROM EMPLOYEE
WHERE SALARY > 2000 AND MONTHS < 10
ORDER BY EMPLOYEE_ID