here's the query for SQL SERVER considering that null values are present in some tables. WITH LeadManagerCounts AS ( SELECT company_code, COUNT(DISTINCT lead_manager_code) AS lead_manager_count FROM Lead_Manager GROUP BY company_code ), SeniorManagerCounts AS ( SELECT company_code, COUNT(DISTINCT senior_manager_code) AS senior_manager_count FROM Senior_Manager GROUP BY company_code ), ManagerCounts AS ( SELECT company_code, COUNT(DISTINCT manager_code) AS manager_count FROM Manager GROUP BY company_code ), EmployeeCounts AS ( SELECT company_code, COUNT(DISTINCT employee_code) AS employee_count FROM Employee GROUP BY company_code ) SELECT c.company_code, c.founder, lc.lead_manager_count , sc.senior_manager_count, mc.manager_count, ec.employee_count FROM company c LEFT JOIN LeadManagerCounts lc ON c.company_code = lc.company_code LEFT JOIN SeniorManagerCounts sc ON c.company_code = sc.company_code LEFT JOIN ManagerCounts mc ON c.company_code = mc.company_code LEFT JOIN EmployeeCounts ec ON c.company_code = ec.company_code ORDER BY c.company_code ASC;

SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code), COUNT(DISTINCT e.senior_manager_code), COUNT(DISTINCT e.manager_code), COUNT(DISTINCT e.employee_code) 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

Select c.company_code, c.founder,count(distinct lm.lead_manager_code),count(distinct sm.senior_manager_code),count(distinct m.manager_code),count(distinct e.employee_code) from Company c join employee e ON e.company_code = c.company_code join Manager m ON m.manager_code = e.manager_code join senior_manager sm ON sm.senior_manager_code = m.senior_manager_code join Lead_manager lm ON lm.lead_manager_code = e.lead_manager_code GROUP BY c.company_code, c.founder ORDER BY c.company_code\

-- sol 1

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT lm.lead_manager_code),
    COUNT(DISTINCT sm.senior_manager_code),
    COUNT(DISTINCT m.manager_code),
    COUNT(DISTINCT e.employee_code)
FROM Company c
JOIN Lead_Manager lm ON c.company_code = lm.company_code 
JOIN Senior_Manager sm ON lm.lead_manager_code = sm.lead_manager_code
JOIN Manager m ON sm.senior_manager_code = m.senior_manager_code
JOIN Employee e ON m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code
-- A little trick for ORDER if you want the order like c1, c2, c3, ... You should use ORDER BY CAST(SUBSTRING(c.company_code, 2, LENGTH(c.company_code)) AS DECIMAL);

-- sol 2

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT e.lead_manager_code),
    COUNT(DISTINCT e.senior_manager_code),
    COUNT(DISTINCT e.manager_code),
    COUNT(DISTINCT e.employee_code)
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;

-- sol 3

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT lm.lead_manager_code),
    COUNT(DISTINCT sm.senior_manager_code),
    COUNT(DISTINCT m.manager_code),
    COUNT(DISTINCT e.employee_code)
FROM Company c
JOIN Lead_Manager lm ON c.company_code = lm.company_code
JOIN Senior_Manager sm ON lm.company_code = sm.company_code
JOIN Manager m ON sm.company_code = m.company_code
JOIN Employee e ON m.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

select employee.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 inner join employee on company.company_code=employee.company_code group by employee.company_code, company.founder;