• 01. Population Density Difference(해커랭크)
• 02. Weather Observation Station 11(해커랭크)
• 03. Weather Observation Station 13(해커랭크)
• 04. Top Competitors(해커랭크)

01. Population Density Difference(해커랭크)

Query the difference between the maximum and minimum populations in CITY.

-- 내 문제 풀이(정답)
SELECT MAX(POPULATION) - MIN(POPULATION)
FROM CITY

02. Weather Observation Station 11(해커랭크)

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

• vowels = 모음 = (a,e,i,o,u)

-- 내 문제 풀이(오답)
SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE "a%" OR "e%" OR "i%" OR "o%" OR "u%" OR "%a" OR "%e" OR "%i" OR "%o" OR "%u"

-- LEFT, RIGHT, NOT IN, DISTINCT 활용
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')
      
-- NOT LIKE 사용하고 싶을 때 = 비효율적
SELECT DISTINCT city
FROM station 
WHERE (
       city NOT LIKE 'a%'
       AND city NOT LIKE 'e%'
       AND city NOT LIKE 'i%'
       AND city NOT LIKE 'o%'
       AND city NOT LIKE 'u%'
) OR (city NOT LIKE '%a'
           AND city NOT LIKE '%e'
           AND city NOT LIKE '%i'
           AND city NOT LIKE '%o'
           AND city NOT LIKE '%u'
)

03. Weather Observation Station 13(해커랭크)

Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880 and less than 137.2345. Truncate your answer to decimal places.

-- 내 문제 풀이(수정필요) -> TRUNCATE 해달라고 했다. 문제 잘 읽자
SELECT ROUND(SUM(LAT_N),4)
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345

-- 정답(TRUNCATE 사용)
SELECT TRUNCATE(SUM(LAT_N),4)
FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345

-- 주의(BETWEEN은 같은 값 포함) => 크거나 같음, 작거나 같음의 의미
SELECT TRUNCATE(SUM(LAT_N),4)
FROM STATION
WHERE BETWEEN LAT_N 38.7880 AND 137.2345

04. Top Competitors(해커랭크)

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

• Table 종류

  • Hackers

  

  • Difficulty

  

  • Challenges

  

  • Submissions

  

• full score?
  • difficulty level에 따른 score가 정의되어 있는데, 그 score 만점을 받은 것을 full score라고 하는 것.
• Challenge_id 별로 dfficulty_level 다르다.

-- 내 문제 풀이(정답)
SELECT S.hacker_id, H.name
FROM Submissions S LEFT JOIN Challenges C ON S.challenge_id = C.challenge_id
                   LEFT JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
                   LEFT JOIN Hackers H ON S.hacker_id = H.hacker_id
WHERE S.score = D.score
GROUP BY S.hacker_id, H.name
HAVING COUNT(S.hacker_id) >= 2
ORDER BY COUNT(S.Challenge_id) desc, S.hacker_id

-- 다른 풀이 (INNER JOIN 활용), 이미 where절에서 다 걸러지기 때문에 having 절에는 위처럼 써도 되는 듯 하다
SELECT S.hacker_id
	 , H.name
FROM Submissions S INNER JOIN Challenges C ON S.challenge_id = C.challenge_id
                   INNER JOIN Difficulty D ON C.difficulty_level = D.difficulty_level
                   INNER JOIN Hackers H ON S.hacker_id = H.hacker_id
WHERE S.score = D.score
GROUP BY H.hacker_id, H.name
HAVING COUNT(DISTINCT S.submission_id) > 1
ORDER BY COUNT(DISTINCT S.Challenge_id) DESC, H.hacker_id