I know there are simpler ways to solve this problem, but I want to figure it out using a non-correlated subquery, because I've just studied it.

My solution in MySQL:

SELECT
    c.company_code,
    c.founder,
    (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager l WHERE l.company_code = c.company_code) AS total_lead_managers,
    (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager s WHERE s.company_code = c.company_code) AS total_senior_managers,
    (SELECT COUNT(DISTINCT manager_code) FROM manager m WHERE m.company_code = c.company_code) AS total_managers,
    (SELECT COUNT(DISTINCT employee_code) FROM employee e WHERE e.company_code = c.company_code) AS total_employee
FROM company c 
ORDER BY c.company_code ASC 

there is no need to use all the tables. All the components are present in the employee table except founders names. so we can just use the company table and the employee table here's my code: SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code ) AS total_lm, COUNT(DISTINCT e.senior_manager_code ) AS total_sm, COUNT(DISTINCT e.manager_code ) AS total_m, COUNT(DISTINCT e.employee_code ) AS total_e FROM company c LEFT JOIN employee e ON c.company_code=e.company_code GROUP BY c.company_code, c.founder ORDER BY c.company_code;

My solution

SELECT company.company_code, company.founder, (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager WHERE lead_manager.company_code = company.company_code) AS total_lead_manager, (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager WHERE senior_manager.company_code = company.company_code) AS total_senior_manager, (SELECT COUNT(DISTINCT manager_code) FROM manager WHERE manager.company_code = company.company_code) AS total_manager, (SELECT COUNT(DISTINCT employee_code) FROM employee WHERE employee.company_code = company.company_code) AS total_employee FROM company ORDER BY company.company_code ASC;

Ms sql , No need to use all tables

select company.company_code , founder, 
count(distinct lead_manager_code) ,
count(distinct senior_manager_code),
count(distinct manager_code),

count(distinct employee_code)

 from company 
left join employee on employee.company_code = company.company_code
group by company.company_code ,founder

in MS sql

with cte_emp as ( select company_code,count(distinct lead_manager_code) as lmc,count(distinct senior_manager_code) as smc, count(distinct manager_code) as mc,count(distinct employee_code) as ec from employee group by company_code )select a.company_code,b.founder,a.lmc,a.smc,a.mc,a.ec from cte_emp a join company b on a.company_code = b.company_code order by a.company_code