[HackerRank][SQL(Oracle)] New Companies

 

※  The following tables contain company data:

• Company: The company_code is the code of the company and founder is the founder of the company. 
  
• Lead_Manager: The lead_manager_code is the code of the lead manager, and the company_code is the code of the working company. 
  
• Senior_Manager: The senior_manager_code is the code of the senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
  
• Manager: The manager_code is the code of the manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
  
• Employee: The employee_code is the code of the employee, the manager_code is the code of its manager, the senior_manager_code is the code of its senior manager, the lead_manager_code is the code of its lead manager, and the company_code is the code of the working company.
  

 

■  [Advanced Select]  New Companies

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

Q.

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.

A.

SELECT		C.COMPANY_CODE, C.FOUNDER, L_FL.CNT_LEAD_MANAGER, S_FL.CNT_SENIOR_MANAGER, M_FL.CNT_MANAGER, E_FL.CNT_EMPLOYEE
FROM		COMPANY C
			,(
				SELECT		COMPANY_CODE, COUNT(DISTINCT LEAD_MANAGER_CODE) AS CNT_LEAD_MANAGER
				FROM 		LEAD_MANAGER
				GROUP BY 	COMPANY_CODE
			) L_FL
			,(
				SELECT 		COMPANY_CODE, COUNT(DISTINCT SENIOR_MANAGER_CODE) AS CNT_SENIOR_MANAGER
				FROM 		SENIOR_MANAGER
				GROUP BY 	COMPANY_CODE
			) S_FL
			,(
				SELECT 		COMPANY_CODE, COUNT(DISTINCT MANAGER_CODE) AS CNT_MANAGER
				FROM 		MANAGER
				GROUP BY 	COMPANY_CODE
			) M_FL
			,(
				SELECT 		COMPANY_CODE, COUNT(DISTINCT EMPLOYEE_CODE) AS CNT_EMPLOYEE
				FROM 		EMPLOYEE
				GROUP BY 	COMPANY_CODE
			) E_FL
WHERE 		C.COMPANY_CODE = L_FL.COMPANY_CODE AND
			C.COMPANY_CODE = S_FL.COMPANY_CODE AND
			C.COMPANY_CODE = M_FL.COMPANY_CODE AND
			C.COMPANY_CODE = E_FL.COMPANY_CODE
ORDER BY 	C.COMPANY_CODE;
-- ORDER BY CAST(SUBSTR(C.COMPANY_CODE,2) AS INT) => 숫자 기준 정렬 (ORDERING NUMERIC STRINGS)

SELECT 		C.COMPANY_CODE, C.FOUNDER, L_FL.CNT_LEAD_MANAGER, S_FL.CNT_SENIOR_MANAGER, M_FL.CNT_MANAGER, E_FL.CNT_EMPLOYEE
FROM	 	COMPANY C
			JOIN (
				SELECT 		COMPANY_CODE, COUNT(DISTINCT LEAD_MANAGER_CODE) AS CNT_LEAD_MANAGER
				FROM  		LEAD_MANAGER
				GROUP BY 	COMPANY_CODE
			) L_FL	ON 	C.COMPANY_CODE = L_FL.COMPANY_CODE
			JOIN (
				SELECT 		COMPANY_CODE, COUNT(DISTINCT SENIOR_MANAGER_CODE) AS CNT_SENIOR_MANAGER
				FROM 		SENIOR_MANAGER
				GROUP BY 	COMPANY_CODE
			) S_FL	ON 	C.COMPANY_CODE = S_FL.COMPANY_CODE
			JOIN (
				SELECT 		COMPANY_CODE, COUNT(DISTINCT MANAGER_CODE) AS CNT_MANAGER
				FROM 		MANAGER
				GROUP BY 	COMPANY_CODE
			) M_FL	ON 	C.COMPANY_CODE = M_FL.COMPANY_CODE
			JOIN (
				SELECT 		COMPANY_CODE, COUNT(DISTINCT EMPLOYEE_CODE) AS CNT_EMPLOYEE
				FROM 		EMPLOYEE
				GROUP BY 	COMPANY_CODE
			) E_FL	ON 	C.COMPANY_CODE = E_FL.COMPANY_CODE
ORDER BY 	C.COMPANY_CODE;

SELECT 		COMPANY_CODE
			,FOUNDER
			,(SELECT COUNT(DISTINCT LEAD_MANAGER_CODE) FROM LEAD_MANAGER WHERE COMPANY_CODE = C.COMPANY_CODE) AS CNT_LEAD_MANAGER
			,(SELECT COUNT(DISTINCT SENIOR_MANAGER_CODE) FROM SENIOR_MANAGER WHERE COMPANY_CODE = C.COMPANY_CODE) AS CNT_SENIOR_MANAGER
			,(SELECT COUNT(DISTINCT MANAGER_CODE) FROM MANAGER WHERE COMPANY_CODE = C.COMPANY_CODE) AS CNT_MANAGER
			,(SELECT COUNT(DISTINCT EMPLOYEE_CODE) FROM EMPLOYEE WHERE COMPANY_CODE = C.COMPANY_CODE) AS CNT_EMPLOYEE
FROM 		COMPANY C
ORDER BY 	COMPANY_CODE;