We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Select
  4. New Companies
  5. Discussions

New Companies

Sort by

recency

|

3166 Discussions

|

  • toviveknailwal
     + 1 comment

    can anyone explain if all columns are avilable in one table "Employee" then what iws the purpose of having seperate col for every col that has to be retrieved

    • anslemdcunha
       + 0 comments

      There might be a manager or lead_manager or senior_manager without anyone working under him

  • shrivastavkaran1
     + 0 comments

    SELECT c.company_code, c.founder, COUNT(DISTINCT lm.lead_manager_code) AS total_lead_managers, COUNT(DISTINCT sm.senior_manager_code) AS total_senior_managers, COUNT(DISTINCT m.manager_code) AS total_managers, COUNT(DISTINCT e.employee_code) AS total_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 GROUP BY c.company_code, c.founder ORDER BY c.company_code; *

  • shihaspk
     + 0 comments

    The schema in this problem is not properly normalized. Some tables have reduntant columns. For example, Since we already have lead_manager_code and company_code mappings in Lead_Manager_Table, there is no need of company_code details in Senior_Manager Table. 1st query should work even if we remove these reduntant columns and that is how a real database should be setup. In this particular problem, we can get correct output with using correlated query using single column as all tables have Company code column (used in 2nd query).

    Both are tested in MS SQL server

    1)

    select C.company_code,C.founder,count(distinct LM.lead_manager_code) LM_count,
count(distinct SM.senior_manager_code) SM_count,count(distinct M.manager_code) M_count,
count(distinct E.employee_code) E_count
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
group by C.company_code,founder
order by C.company_code;

    2)

    select C.company_code,C.founder,
(select count(distinct lead_manager_code) from Lead_Manager where company_code = C.company_code),
(select count(distinct senior_manager_code) from Senior_Manager where company_code = C.company_code),
(select count(distinct manager_code) from Manager where company_code = C.company_code),
(select count(distinct employee_code) from Employee where company_code = C.company_code)
from Company C
order by C.company_code;

  • sharadmrsingh
     + 1 comment

    what's wrong in this 

    select *, (select count(company_code) from lead_manager where company_code = company.company_code), (select count(company_code) from senior_manager where company_code = company.company_code), (select count(company_code) from manager where company_code = company.company_code), (select count(company_code) from employee where company_code = company.company_code) from company order by company_code;

    • eduvaibhav110
       + 0 comments

      Buddy, there is a liitle correction - select *, (select count( distinct lead_manager_code) from lead_manager where company_code = company.company_code), (select count(distinct senior_manager_code) from senior_manager where company_code = company.company_code), (select count(distinct manager_code) from manager where company_code = company.company_code), (select count(distinct employee_code) from employee where company_code = company.company_code) from company order by company_code;

  • anslemdcunha
     + 1 comment
    select e.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 Employee e
Join Company c
on e.company_code = c.company_code
group by e.company_code, c.founder
order by e.company_code

    • anslemdcunha
       + 1 comment

      Did not use the other tables. Using them is redundant

      • paras_sikka77
         + 1 comment

        This is working here, but it might miss this below case: If any manager doesn't have employee under him. He won't be present in the employee table and will get missed in the final count. What you think?

        • anslemdcunha
           + 2 comments

          Thats a great observation. I completely missed that. But most of the people here are left-joining the tables with the Employee table as the base. so in this case this manager will still get missed. How can we get around this ?

Join us

Create a HackerRank account

Be part of a 26 million-strong community of developers

Already have an account?Log in