[해커랭크/MYSQL] - (25) New Companies

🔍 예상 검색어

# 물만날물고기

# SQL

# New Companies

# MYSQL

# HACKRRANK

# DB

# 해커랭크

---

해당 포스팅은 해커랭크 MYSQL "New Companies" 문제에 대한 풀이를 정리하였습니다.

 

▶ 문제

 

▶ 내 정답

-- 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.

-- Company
-- Lead_Manager
-- Senior_Manager
-- Manager
-- Employee

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 ASC;

 

▶ 다른 사람 정답 (1)

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, Lead_Manager l, Senior_Manager s, Manager m, Employee e 
where c.company_code = l.company_code 
    and l.lead_manager_code=s.lead_manager_code 
    and s.senior_manager_code=m.senior_manager_code 
    and m.manager_code=e.manager_code 
group by c.company_code order by c.company_code;

 

▶ 다른 사람 정답 (2)

select c.company_code, c.founder, count(distinct lm.lead_manager_code), 
count(distinct sm.senior_manager_code), count(distinct m.manager_code), 
count(distinct e.employee_code)


from Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
where c.company_code = lm.company_code
	and lm.lead_manager_code = sm.lead_manager_code
	and sm.senior_manager_code = m.senior_manager_code
	and m.manager_code = e.manager_code
group by c.company_code, c.founder
order by c.company_code

 

▶ 다른 사람 정답 (3)

select c.company_code, 
    c.founder, 
    count(distinct e.lead_manager_code), 
    count(distinct e.senior_manager_code), 
    count(distinct e.manager_code), 
    count(distinct e.employee_code)
from company c
    inner join employee e on e.company_code = c.company_code
group by c.company_code,c.founder
order by c.company_code;

 

▶ 총평

사실 이 문제는 다른 사람 정답 (3) 처럼 Employee 테이블만 join으로 연결해도 결과가 정답으로 출력되지만, 이렇게 할 경우 만약 Company 테이블에는 있지만 Employee에는 없는 company_code와 founder에 대한 결과를 출력할 수 없다는 문제가 있다. 왜냐면 Employee 항목들은 Null로 표시되더라도, 현재 Company 테이블 기준의 상태로 확인하고 싶을 수 있다는 점 때문이다. 

 

 COUNT(DISTINCT) 를 같이 사용하는 문법도 생소하기는 했지만 정석은 Company 테이블에 LEFT 조인으로 다른 테이블을 JOIN 시켜서 값을 구하는게 맞는 것 같다.!

 

 

 

 

▼ 참고자료

No.	내용	비고
1	[백문이불여일타] 데이터 분석을 위한 중급 SQL 문제풀이 | 학습 페이지	데이터리
2	-	-
3	-	-
4	-	-
5	-	-

▼ 복습/히스토리

No.	복습일	비고
1
2
3
4
5

- 작성코드

--