HackerRank(해커랭크) Japan Population, Weather Observation Station 2, Weather Observation Station 18, New Companies

Japan Population

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE='JPN'

Weather Observation Station 2

SELECT ROUND(SUM(LAT_N),2) AS lat
     , ROUND(SUM(LONG_W),2) AS lon
FROM STATION

The sum of all values in LAT_N rounded to a scale of 2 decimal places. -> 소숫점 둘째 자리까지 반올림

Weather Observation Station 18

-- a = MIN(LAT_N), b = MIN(LONG_W), c = MAX(LAT_N), d = MAX(LONG_W)
-- p1 at (a, b) and p2 at (c, d), it is |a - c| + |b - d|
-- ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W))

SELECT ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W)), 4)
FROM STATION

주어지는 수식을 그대로 가져와 차분히 대입하기

New Companies

   SELECT C.company_code
        , C.founder
        , COUNT(DISTINCT L.lead_manager_code)
        , COUNT(DISTINCT S.senior_manager_code)
        , COUNT(DISTINCT M.manager_code)
        , COUNT(DISTINCT E.employee_code)
     FROM Company C
LEFT JOIN Lead_Manager L
       ON C.company_code = L.company_code
LEFT JOIN Senior_Manager S
       ON L.lead_manager_code = S.lead_manager_code
LEFT JOIN Manager M
       ON S.senior_manager_code = M.senior_manager_code
LEFT JOIN Employee E
       ON M.manager_code = E.manager_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code

1. 가장 하위 테이블만 JOIN 할 경우 상위 테이블의 senior_manager_code가 하위 DATA를 포함하지 않아서 COUNT 시 누락된다.
   그러므로 모든 테이블을 Company 테이블을 기준으로 LEFT OUTER JOIN 해야한다.
2. company_code, founder name 별 총 count 수를 구하는것이므로 GROUP BY 사용,
   GROUP BY에 사용된 칼럼만 SELECT 절에서 사용 가능하다. SELECT 절에서 C.company_code, C.founder 칼럼을 사용하고 있으므로
   GROUP BY C.company_code, C.founder가 와야한다.

+) 추가 조건 : 조인을 사용하지 않고 풀이하여라.

SELECT COMPANY_CODE
           , FOUNDER
           , (
               SELECT COUNT(DISTINCT LEAD_MANAGER_CODE)
                 FROM Lead_Manager L
               WHERE C.COMPANY_CODE = L.COMPANY_CODE
              )
            , (
               SELECT COUNT(DISTINCT SENIOR_MANAGER_CODE)
                 FROM Senior_Manager S
               WHERE C.COMPANY_CODE = S.COMPANY_CODE
              )  
            , (
               SELECT COUNT(DISTINCT MANAGER_CODE)
                 FROM Manager M
               WHERE C.COMPANY_CODE = M.COMPANY_CODE
              )
            , (
               SELECT COUNT(DISTINCT EMPLOYEE_CODE)
                 FROM Employee E
               WHERE C.COMPANY_CODE = E.COMPANY_CODE
              )    
  FROM Company C
  ORDER BY COMPANY_CODE

select 절 서브쿼리 - 스칼라 서브쿼리를 이용하여 풀이하였다.