Hackerrank SQL - Advanced Select

select 
company.company_code,
company.founder,
count(distinct lead_manager.lead_manager_code),
count(distinct senior_manager.senior_manager_code), 
count(distinct manager.manager_code),
count(distinct employee.employee_code)
from company
left join 
    lead_manager on company.company_code = lead_manager.company_code
left join 
    senior_manager on company.company_code = senior_manager.company_code and
    lead_manager.lead_manager_code = senior_manager.lead_manager_code
left join 
    manager on company.company_code = manager.company_code and
    lead_manager.lead_manager_code = manager.lead_manager_code and 
    senior_manager.senior_manager_code = manager.senior_manager_code
left join 
    employee on company.company_code = employee.company_code and
    lead_manager.lead_manager_code = employee.lead_manager_code and 
    senior_manager.senior_manager_code = employee.senior_manager_code and 
    manager.manager_code = employee.manager_code
group by company.company_code, company.founder
order by company.company_code

select N,
CASE
    WHEN N in (select N from BST where P is NUll) then "Root"
    WHEN N in (select P from BST) then "Inner"
    Else "Leaf"
end as flag 
from BST
order by N

select doc_name,prof_name,singer_name,actor_name from(
(select 
 (row_number() over (order by name)) as doc_rank, 
 name as doc_name 
 from occupations 
 where occupation = "Doctor"
) as t1
full outer join 
(select 
 (row_number() over (order by name)) as prof_rank, 
 name as prof_name 
 from occupations 
 where occupation = "Professor") as t2
on t1.doc_rank = t2.prof_rank
full outer join (
    select 
 (row_number() over (order by name)) as actor_rank, 
 name as actor_name 
 from occupations 
 where occupation = "Actor") as t3
on t2.prof_rank = t3.actor_rank
full outer join (
    select 
 (row_number() over (order by name)) as singer_rank, 
 name as singer_name 
 from occupations 
 where occupation = "Singer") as t4
on t3.actor_rank = t4.singer_rank)

select name+"("+left(occupation,1)+")" from occupations order by name, occupation

select "There are a total of "+cast(count(name) as varchar )+" "+lower(occupation)+"s." from occupations group by occupation 
order by count(name), occupation

select 
case 
    when a+b > c and b+c > a and c+a > b then 
        case 
            when a = b and b = c then 'Equilateral' 
            when a = b or b = c or a = c then 'Isosceles' 
            else 'Scalene' 
        end
    else 'Not A Triangle' 
end 
from triangles;