[Hacker Rank SQL 정복기] Basic Join

🥇 : Population Census

❓ Question

❗ Answer

SELECT SUM(c.population)
FROM city c
JOIN country u
ON c.countrycode = u.code
WHERE u.continent = 'Asia'

📌 Discussion

• country code로 JOIN 후 WHERE 문으로 'Asia' 대륙에 속한 city들의 population을 SUM 집계

🥇 : African Cities

❓ Question

❗ Answer

SELECT c.name
FROM city c, country y
WHERE c.countrycode = y.code AND y.continent = 'Africa'

📌 Discussion

• WHERE 문으로 join과 africa 대륙 조건을 모두 명시

🥇 : Average Population of Each Continent

❓ Question

❗ Answer

SELECT DISTINCT y.continent, floor(AVG(c.population))
FROM city c, country y
WHERE c.countrycode = y.code
GROUP BY y.continent

📌 Discussion

• WHERE 문으로 JOIN 후 GROUP BY로 대륙별로 묶어서 AVG 집계
• floor함수로 소수점 버림

🥇 : The Report

❓ Question

❗ Answer

SELECT (CASE
        WHEN marks >= 70 THEN name
        ELSE NULL
        END),(CASE
        WHEN marks BETWEEN 90 AND 100 THEN 10
        WHEN marks BETWEEN 80 AND 89 THEN 9
        WHEN marks BETWEEN 70 AND 79 THEN 8
        WHEN marks BETWEEN 60 AND 69 THEN 7
        WHEN marks BETWEEN 50 AND 59 THEN 6
        WHEN marks BETWEEN 40 AND 49 THEN 5
        WHEN marks BETWEEN 30 AND 39 THEN 4
        WHEN marks BETWEEN 20 AND 29 THEN 3
        WHEN marks BETWEEN 10 AND 19 THEN 2
        ELSE 1
        END) grade,
        marks
FROM students
ORDER BY grade DESC, name

📌 Discussion

• case when으로 점수에 따른 name 공개여부와 등급을 호출