인프런- 데이터 분석을 위한 고급 SQL 문제풀이 : 섹션 1 - New Companies(틀림)

본 내용은 데이터리안 '데이터 분석을 위한 고급 SQL 문제풀이'을 수강하며 작성한 내용입니다.

문제

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:

Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note:

The tables may contain duplicate records.
The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

풀이

• 나의 풀이과정
  : JOIN을 사용하지 말고 풀라고 했는데, 어떻게 풀지 감이 안 온다ㅜ

• 선생님 풀이

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 

배운점

• SELECT절에도 서브쿼리를 넣을 수 있다
• 서브쿼리에 Main 쿼리의 컬럼을 이용할 수 있다.