문제

https://www.hackerrank.com/challenges/the-company/problem

풀이

• join 사용_where절
  (join 사용x이었지만 아무 생각없이 where절에 하는 건 괜찮겠지 싶었음; 이것도 join인데...)

SELECT Company.company_code
    , founder
    , COUNT(DISTINCT Lead_Manager.lead_manager_code)
    , COUNT(DISTINCT Senior_Manager.senior_manager_code)
    , COUNT(DISTINCT Manager.manager_code)
    , COUNT(DISTINCT Employee.employee_code)
FROM Company
    , Lead_Manager
    , Senior_Manager
    , Manager
    , Employee
WHERE Company.company_code = Lead_Manager.company_code
    AND Lead_Manager.lead_manager_code = Senior_Manager.lead_manager_code
    AND Senior_Manager.senior_manager_code = Manager.senior_manager_code
    AND Manager.manager_code = Employee.manager_code
GROUP BY Company.company_code, founder
ORDER BY Company.company_code

• 강의 풀의 확인
  select문에 서브쿼리 사용

SELECT c.company_code
    , c.founder
    , (SELECT COUNT(DISTINCT lead_manager_code)
       FROM Lead_Manager
       WHERE company_code = c.company_code)
    , (SELECT COUNT(DISTINCT senior_manager_code)
       FROM Senior_Manager
       WHERE company_code = c.company_code)
    , (SELECT COUNT(DISTINCT manager_code)
       FROM Manager
       WHERE company_code = c.company_code)
    , (SELECT COUNT(DISTINCT employee_code)
       FROM Employee
       WHERE company_code = c.company_code)
FROM Company c
ORDER BY company_code