[내일배움캠프]_달리기반 LV4

[단골고객 찾기]

1.) 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요

- 고객별 주문건수, 총 주문금액 조회
- 출력 컬럼 [고객이름(커스터머), 주문건수(오더), 총 주문금액(sum 오더)] 단, 주문안한 고객도 포함
select c.CustomerName,
        count(o.CustomerID) as OrderCount, 
        coalesce(sum(o.TotalAmount), 0) as TotalSpent
from customers c left join orders o on c.CustomerID = o.CustomerID 
group by c.CustomerName  
- coalesce를 사용해 주문을 안한 사람의 걘 null이 아닌 0으로 출력

 

2) 나라별로 총 주문금액이 가장 높은 고객의 이름과 그 고객의 총 주문금액을 조회

- 출력 컬럼[나라, 높은금액, 총 금액]

select c.Country,
       c.CustomerName as Top_Customer,
       sum(o.TotalAmount) as Top_Spent
from customers c left join orders o on o.CustomerID = c.CustomerID
group by 1,2
having Top_Spent = 
  (
          select max(sum_spent)
  from 
  (
  select sum(o2.TotalAmount) sum_spent
  from customers c2 left join orders o2 on c2.CustomerID=o2.CustomerID
  where c2.Country=c.Country 
  group by c2.CustomerName
    ) a
  )

[ 가장 높은 월급을 받는 직원은?]

1) 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

 

elect a.Name, a.Department, a.Salary, b.Top_Earner, b.Top_Salary
from
(
select e.Name ,e.Department ,e.Salary 
from employees e
) a join
(
select e2.Department, e2.Name as Top_Earner, e2.Salary as Top_Salary
from employees e2
where e2.Salary = (
                   select max(e3.Salary)
                   from employees e3
                   where e3.Department = e2.Department) 
                   -- e3는 급여의 최고값을 출력/e2의 급여와 같게 하여 e2의 출력들은 각 부서의 최고값을 출력
) b on a.Department = b.Department 
-- e2(b)와 e(a)를 부서로 결합시킨 후 e(a)의 이름,부서, 급여를 출력후 e2(b)의 이름(부서의 최고 급여자)과 급여(부서의 최고 급여자의 급여)를 출력
order by a.Name

 

2) 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

- 부서별 평균 구하기
- 평균 가장 높은 부서 구하기
- 그 부서의 평균월급 구하기

select Department,
   avg(salary) as Avg_Salary
from employees
group by 1
having avg(Salary) = (
          select max(Avg_Salary) 
          from 
          (
          select Department,
                 avg(Salary) as Avg_Salary
          from employees 
          group by 1
          ) a
) limit 1