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

SELECT C.company_code, C.founder, COUNT(DISTINCT LM.lead_manager_code) AS lead_managers, COUNT(DISTINCT SM.senior_manager_code) AS senior_managers, COUNT(DISTINCT M.manager_code) AS managers, COUNT(DISTINCT E.employee_code) AS employees FROM Company C LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code LEFT JOIN Employee E ON M.manager_code = E.manager_code AND SM.senior_manager_code = E.senior_manager_code AND LM.lead_manager_code = E.lead_manager_code AND C.company_code = E.company_code GROUP BY C.company_code, C.founder ORDER BY C.company_code;

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 left join company c on c.company_code = e.company_code group by e.company_code, c.founder order by e.company_code;

SELECT c.company_code, c.founder, COUNT(DISTINCT l.lead_manager_code) AS lead_managers, COUNT(DISTINCT s.senior_manager_code) AS senior_managers, COUNT(DISTINCT m.manager_code) AS managers, COUNT(DISTINCT e.employee_code) AS employees FROM Company c LEFT JOIN Lead_Manager l ON c.company_code = l.company_code LEFT JOIN Senior_Manager s ON l.lead_manager_code = s.lead_manager_code AND c.company_code = s.company_code LEFT JOIN Manager m ON s.senior_manager_code = m.senior_manager_code AND c.company_code = m.company_code LEFT JOIN Employee e ON m.manager_code = e.manager_code AND c.company_code = e.company_code GROUP BY c.company_code, c.founder ORDER BY c.company_code;

SELECT Company.company_code, Company.founder, COUNT(DISTINCT Lead_Manager.lead_manager_code) AS lead_manager_count, COUNT(DISTINCT Senior_Manager.senior_manager_code) AS senior_manager_count, COUNT(DISTINCT Manager.manager_code) AS manager_count, COUNT(DISTINCT Employee.employee_code) AS employee_count FROM Company JOIN Lead_Manager ON Company.company_code = Lead_Manager.company_code JOIN Senior_Manager ON Lead_Manager.company_code = Senior_Manager.company_code JOIN Manager ON Manager.lead_manager_code = Senior_Manager.lead_manager_code JOIN Employee ON Manager.manager_code = Employee.manager_code GROUP BY Company.company_code, Company.founder ORDER BY Company.company_code;