# 문제 [ New Companies ]

write a query to print the 
company_code, 
founder name, 
total number of lead managers, 
total number of senior managers, 
total number of managers, 
total number of employees.

1차 답

select com.company_code, com.founder, count(sm.lead_manager_code), count(sm.senior_manager_code), count(m.senior_manager_code), count(e.employee_code)
from Company as com
left join Senior_Manager as sm
on com.company_code = sm.company_code
left join Manager as m
on sm.company_code = m.company_code
left join Employee as e
on m.company_code = e.company_code
group by com.company_code, com.founder

오류 원인 : 중복됨을 알 수 있다.

수정된 답

select com.company_code, com.founder, count(distinct sm.lead_manager_code), count(distinct sm.senior_manager_code), count(distinct m.manager_code), count(distinct e.employee_code)
from Company as com
right join Senior_Manager as sm
on com.company_code = sm.company_code
right join Manager as m
on sm.company_code = m.company_code
right join Employee as e
on m.company_code = e.company_code
group by com.company_code, com.founder
order by com.company_code asc;

2차 수정된 답 ( 1차 답은 join 연산이 많아서 그런지 시간이 오래 걸림 // 걍 해커랭커 서버가 느린듯 하다. )

select com.company_code, com.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 as com
-- right join Senior_Manager as sm
-- on com.company_code = sm.company_code
-- right join Manager as m
-- on sm.company_code = m.company_code
right join Employee as e
on com.company_code = e.company_code
group by com.company_code, com.founder
order by com.company_code asc;

문제 [ Top Earners ]

 Write a query to find the maximum total earnings 
 for all employees as well as the total number of employees 
 who have maximum total earnings

정답

select E.months * E.salary as earn , count(*)
from Employee as E
group by earn
order by earn desc
limit 1

내가 했던 실수 or 어려웠던 점

1. E.months * E.salary as earn 부분을 괄호처리 하니 syntax 에러
2. E.months * E.salary as earn 라고 써도 될까? 했는데 됐다.

문제 [ Weather Observation Station 2 ]

Query the following two values from the STATION table:

The sum of all values in LAT_N rounded to a scale of 2 decimal places.
The sum of all values in LONG_W rounded to a scale of 2 decimal places.

정답

select round(sum(LAT_N),2), round(sum(LONG_W),2)
from STATION

문제 [ Weather Observation Station 13 ]

Query the sum of Northern Latitudes (LAT_N) from STATION 
having values greater than  and less than . 
Truncate your answer to  decimal places.

정답 ( 처음에 group by & having 쓰려고 했는데 필요가 없없다 )

select round(sum(LAT_N),4)
from STATION
-- group by LAT_N

WHERE 1=1
AND LAT_N > 38.7880
AND LAT_N < 137.2345

문제 [ Placements ] ( 재밌게 풂 )

Write a query to output the names of those students 
whose best friends got offered a higher salary than them. 
Names must be ordered by the salary amount offered to the best friends. 
It is guaranteed that no two students got same salary offer.

정답

select s.Name -- , p.Salary, f.Friend_ID,friend_salary.f_salary
from Students s 
left join Packages p
on s.ID = p.ID
left join Friends f
on f.ID = s.ID
-- 
left join (
select F.Friend_ID as f_id , P.Salary as f_salary
    from Friends as F
    left join Packages as P 
    on F.Friend_ID = P.ID
) friend_salary
on f.Friend_ID = friend_salary.f_id

where p.Salary < friend_salary.f_salary
order by friend_salary.f_salary

헷갈렸던 포인트

1. 서브쿼리의 select F.Friend_ID as f_id 부분에서 F.ID 로 해서 에러의 원인이었다.
2. 사실 서브쿼리를 짤 필요는 없는데, 생각하기 쉬워서 서브쿼리로 진행했다.

다음엔
걍 중급 이상으로 필터 걸고 문제 풀어야 겠다. 한 3~4 문제가 남아 있다.