• 01. Japan Population (해커랭크)
• 02. Weather Observation Station 2
• 03. Weather Observation Station 18
• 04. New Companies

01. Japan Population (해커랭크)

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

-- 내 문제 풀이(정답)
SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN'

02. Weather Observation Station 2

Query the following two values from the STATION table:

1. The sum of all values in LAT_N rounded to a scale of decimal places.
2. The sum of all values in LONG_W rounded to a scale of decimal places.

-- 내 문제 풀이(정답)
SELECT ROUND(SUM(LAT_N),2) AS lat
	 , ROUND(SUM(LONG_W),2) AS lon
FROM STATION

03. Weather Observation Station 18

Consider P1(a,b) and P2(c,d) to be two points on a 2D plane.

• a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
• b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
• c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
• d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

Query the Manhattan Distance between points P1 and P2 and round it to a scale 4 of decimal places.

• 맨해튼 거리?

  • (P1,P2), (Q1,Q2) 사이이면

    P1-Q1

    +

    P2-Q2

-- 내 문제 풀이(정답)
SELECT ROUND(ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)),4)
FROM STATION

04. New Companies

• 조직도

• 테이블

  • Company

  

  • Lead_Manager

  

  • Senior_Manager

  

  • Manager

  

  • Employee

  

• Sample Output

  • 회사코드, 창립자명, 리드매니저 수, 시니어 매니저 수, 매니저 수, 직원 수

-- 내 문제 풀이(오답) 
-- 만약, 특정한 시니어 매니저가 아래 매니저를 두고 있지 않다면, 오류가 날 수 있다!!
SELECT E.company_code
     , C.founder
     , count(DISTINCT lead_manager_code)
     , count(DISTINCT senior_manager_code)
     , count(DISTINCT manager_code)
     , count(DISTINCT employee_code)
FROM Employee E LEFT JOIN Company C ON E.company_code = C.company_code 
GROUP BY E.company_code, C.founder
ORDER BY C.company.code

-- 만약의 조건도 충족시켜줄 수 있는 정답
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