[SQL 코드카타] 165 ~ 168번 (HackerRank)

165번 population census (asian-population)

SELECT
    SUM(CI.POPULATION)
FROM
    CITY CI 
    JOIN COUNTRY CO
    ON CI.COUNTRYCODE = CO.CODE
WHERE 
    CO.CONTINENT = 'Asia'

 

166번 african-cities

SELECT
    CI.NAME
FROM
    CITY CI
    JOIN COUNTRY CO
    ON CI.COUNTRYCODE = CO.CODE
WHERE 
    CO.CONTINENT = 'Africa'

 

167번 average-population-of-each-continent

SELECT
    CO.CONTINENT, FLOOR(AVG(CI.POPULATION))
FROM
    CITY CI 
    JOIN COUNTRY CO
    ON CI.COUNTRYCODE = CO.CODE
GROUP BY
    CO.CONTINENT

 

168번. binary-search-tree-1

> in ( null ) 때문에.. 헤맸다

SELECT
    N,
    CASE WHEN P IS NULL THEN 'Root' -- 1) p값이 없을 때 root
         WHEN N IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Inner' -- 2) p값에 n이 있을 떄
         									-- 널 값처리
         ELSE 'Leaf' -- 3) p값에 n이 없을 때
         end type
FROM
    BST
ORDER BY
    N