[SQL 코딩테스트] 해커랭크 Basic Select 정답

리트코드와 프로그래머스에 있는 SQL 문제를 다 풀고 마지막으로 해커랭크로 넘어왔다.

어떤 블로그에서 해커랭크가 문제는 제일 많지만 질이 좀 떨어지는 것 같다는 평을 봤는데, 어쨌든 그동안 해온 결실이 있게 나에게 문제가 더 쉽게 풀리기만 한다면 더 바랄게 없겠다.

 

---

Revising the Select Query I, II / Select All / Select By ID
/ Japanese Cities' Attributes / Japanese Cities' Names
⏰ 03:17

-- Revising the Select Query I
SELECT *
FROM CITY
WHERE POPULATION > 100000 AND COUNTRYCODE = 'USA'

-- Revising the Select Query II
SELECT NAME
FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = 'USA'

-- Select All
SELECT *
FROM CITY

-- Select By ID
SELECT *
FROM CITY
WHERE ID = '1661'

-- Japanese Cities' Attributes
SELECT *
FROM CITY
WHERE COUNTRYCODE = 'JPN'

-- Japanese Cities' Names
SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'JPN'

 

Weather Observation Station 1, 3, 4
⏰ 01:49

-- Weather Observation Station 1
SELECT CITY, STATE
FROM STATION

-- Weather Observation Station 3
SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID,2) = 0

-- Weather Observation Station 4
SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
FROM STATION

 

Weather Observation Station 5
⏰ 07:06

 

* UNION을 쓰면서 LIMIT을 같이 쓰고 싶을땐 꼭 괄호()를!

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

UNION ALL

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

 

Weather Observation Station 6~12
⏰ 02:16

--Weather Observation Station 6
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) IN ('a','e','i','o','u')

--Weather Observation Station 7
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) IN ('a','e','i','o','u')

--Weather Observation Station 8
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) IN ('a','e','i','o','u') AND
      LEFT(CITY,1) IN ('a','e','i','o','u')
      
--Weather Observation Station 9
SELECT DISTINCT CITY
FROM STATION
WHERE LEFT(CITY,1) NOT IN ('a','e','i','o','u')

--Weather Observation Station 10
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) NOT IN ('a','e','i','o','u')

--Weather Observation Station 11
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) NOT IN ('a','e','i','o','u') OR
      LEFT(CITY,1) NOT IN ('a','e','i','o','u')
      
--Weather Observation Station 12
SELECT DISTINCT CITY
FROM STATION
WHERE RIGHT(CITY,1) NOT IN ('a','e','i','o','u') AND
      LEFT(CITY,1) NOT IN ('a','e','i','o','u')

 

Higher Than 75 Marks
⏰ 1:48

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

 

Employee Names / Employee Salaries
⏰ 00:38, 01:06

-- Employee Names
SELECT NAME
FROM EMPLOYEE
ORDER BY NAME

-- Employee Salaries
SELECT NAME
FROM EMPLOYEE
WHERE SALARY > 2000 AND MONTHS < 10
ORDER BY employee_id

 

 

---

너무 쉬워서 좀 어이가 없었던 해커랭크... 리트코드랑 프로그래머스 풀다가 쫄아서 분량도 확 줄였는데 일주일치라고 나눠놓은 분량 1시간도 안되어서 어이없게 끝났다... 이러지마... 베이직이라 그럴거라 믿으며 다음주부터 매콤한맛 기대한다...!