NEW COMPANY HIERARCHIES SQL QUERY

PROBLEM STATEMENT:

one of the conglomerate corporations just acquired some new companies. Each of the companies follows this hierarchy.

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

The following tables contain company data (sample records):

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.

SOLUTION:

SELECT E.company_code,
C.founder,
Count(DISTINCT lead_manager_code),
Count(DISTINCT senior_manager_code),
Count(DISTINCT manager_code),
Count(DISTINCT employee_code)
FROM employee E
join company C
ON E.company_code = C.company_code
GROUP BY E.company_code,
C.founder
ORDER BY company_code;

Comments

comments