Set 2 문제

Set 2 문제

 

 

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;

 

 

Weather Observation Station 18

 

SELECT ROUND((MAX(lat_n) - MIN(lat_n)) + (MAX(long_w) - MIN(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 AS C
     LEFT JOIN Lead_Manager AS L ON C.company_code = L.company_code
     LEFT JOIN Senior_Manager AS S ON L.lead_manager_code = S.lead_manager_code
     LEFT JOIN Manager AS M ON S.senior_manager_code = M.senior_manager_code
     LEFT JOIN Employee AS E ON M.manager_code = E.manager_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code;