HackerRank New Companies problem solution

In this HackerRank New Companies problem solution 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.

Problem solution MS SQL.

select a.company_code,a.founder,b.cnt,c.cnt,d.cnt,e.cnt
from company a,
(select count(1) cnt,company_code from lead_manager group by company_code) b,
(select count(1) cnt,company_code from senior_manager group by company_code) c,
(select count(1) cnt,company_code from manager group by company_code) d,
(select count(1) cnt,company_code from employee group by company_code) e
where a.company_code = b.company_code
and b.company_code = c.company_code
and c.company_code = d.company_code
and d.company_code = e.company_code
order by a.company_code;

Problem solution in Oracle.

select
    c.company_code,
    c.founder,
    lm.clm,
    sm.csm,
    m.cm,
    e.ce
from Company c
join (
    select company_code, count(*) as clm from Lead_Manager
    group by company_code
) lm on c.company_code = lm.company_code
join (
    select company_code, count(*) as csm from Senior_Manager
    group by company_code
) sm on c.company_code = sm.company_code
join (
    select company_code, count(*) as cm from Manager
    group by company_code
) m on c.company_code = m.company_code
join (
    select company_code, count(*) as ce from Employee
    group by company_code
) e on c.company_code = e.company_code
order by c.company_code asc;

Problem solution in DB2.

/*
    Enter your query here and follow these instructions:
    1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
    2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"
    3. Type your code immediately after comment. Don't leave any blank line.
*/  
SELECT Company.company_code, Company.founder, count(DISTINCT Employee.lead_manager_code), count(DISTINCT Employee.senior_manager_code), count(DISTINCT Employee.manager_code), count(DISTINCT Employee.employee_code) FROM Company JOIN Employee ON Company.company_code = Employee.company_code GROUP BY Company.company_code, Company.founder;