#문제 풀이 : 달리기반
Lv3 ~ Lv5

---

📃 개념 정리

SELECT [DISTINCT | ALL] 컬럼 OR 그룹함수, ...
FROM 테이블
WHERE 조건
GROUP BY 그룹대상
HAVING <그룹 함수 포함 조건>
ORDER BY 정렬대상

HAVING

• GROUP BY절에 작성되지 않은 일반컬럼은 HAVING절에 올 수 없다
  -> where절을 사용

SELECT DNO, AVG(SAL)
FROM EMP
GROUP BY DNO
HAVING AVG(SAL) < 3000;

# 같은 코드
SELECT *
FROM (
      SELECT DNO, AVG(SAL) AVG_SAL
      FROM EMP
      GROUP BY DNO
      )
WHERE AVG_SAL < 3000 ;

---

🏃‍♀️ 달리기반

Lv3. 이용자의 포인트 조회하기

users 테이블에는 있으나 point_users 에는 없는 유저가 있어요. 이 유저들의 경우 point를 0으로 처리합시다.

내가 시도한 코드

select u.user_id, u.email,
	if(pu.point, pu.point , 0) point
from users u left join point_users pu on u.user_id = pu.user_id
order by pu.point desc

if문을 사용하여, point가 없으면 0으로 처리했다.

결과는 맞지만, 다른 방법으로 COALESCE(p.point,0)를 사용하면 된다.

최종 코드

select u.user_id, u.email,
	coalesce(pu.point, 0) point
from users u left join point_users pu on u.user_id = pu.user_id
order by pu.point desc

---

Lv4. 단골 고객님 찾기

1. 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.
2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요 ✨

내가 시도한 코드

-- 문제 1
select c.CustomerName, count(1) OrderCount, sum(o.TotalAmount) TotalSpent
from customers c left join orders o on c.CustomerID = o.CustomerID
group by 1

-- 문제 2 
select Country, CustomerName Top_Customer, max(TotalSpent) Top_Spent
from (
	select c.CustomerName,sum(o.TotalAmount) TotalSpent, c.Country
	from customers c left join orders o on c.CustomerID = o.CustomerID
	group by 1
) sub
group by 1
order by 2

• 문제 1
  총 주문 금액을 sum(o.TotalAmount)로 구했지만, 주문을 한 적이 없는 고객도 결과에 포함되어야 하는 조건으로, left join은 넣었지만, 실제 데이터에 주문을 한 적이 없는 고객이 없어, 미쳐 null 값 처리를 못했다.
  이를 위해 COALESCE(SUM(o.TotalAmount), 0)로 바꿔준다.

• 문제 2
  서브쿼리문과 max()를 사용하여 금액이 가장 높은 고객과 그 금액을 구했다.
  하지만, 제약사항을 반영하여 HAVING절을 사용해야 했다.
  또한 다음 문제를 풀고 나니, Top_Spent와 Top_Customer가 연결이 되어있지 않음을 확인했다.

  1. 서브쿼리문 : 나라, 고객 별 총 주문금액 출력
  2. HAVING절 : 본 나라와 서브쿼리문의 나라를 하나씩 비교하여, 그 중 높은 금액을 Top_Spent로 만든다.

최종 코드

-- 문제 1
select c.CustomerName, count(1) OrderCount, COALESCE(SUM(o.TotalAmount), 0) TotalSpent
from customers c left join orders o on c.CustomerID = o.CustomerID
group by 1

-- 문제 2
SELECT c.Country, c.CustomerName Top_Customer, SUM(o.TotalAmount) AS Top_Spent
FROM customers c LEFT JOIN orders o ON c.CustomerID = o.CustomerID
GROUP BY 1, 2
HAVING SUM(o.TotalAmount) = (
    SELECT MAX(TotalSpent)
    FROM (
		select c2.Country, c2.CustomerName, sum(o2.TotalAmount) TotalSpent
		from customers c2 left join orders o2 on c2.CustomerID = o2.CustomerID
		group by 1,2
    ) sub
    WHERE sub.Country = c.Country
)
order by 2

---

Lv4. 가장 높은 월급을 받는 직원은? ✨

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

내가 시도한 코드

-- 문제 2
select Department , max(avg) Avg_Salary
from (
	select e1.Department, avg(e1.Salary) avg
	from employees e1
	group by e1.Department
) sub

셀프 조인으로 문제를 해결해야 한다.

• 문제 2
  서브쿼리문과 max()으로 구했으나, Avg_Salary가 같을 때 여러개가 나와야한다.
  HAVING절을 이용하여 구한다.
  부서 별 평균 월급을 구하고, 그 중 최대값을 가진 것만 출력한다.
  

최종 코드

-- 문제 1
select e1.Name, e1.Department, e1.Salary, e2.Name Top_Earner, e2.Salary Top_Salary
from employees e1 inner join employees e2 on e1.Department = e2.Department
where e2.Salary = (
	select max(e3.Salary)
	from employees e3
	where e1.Department=e3.Department
)
order by 1

-- 문제 2
select e1.Department , avg(e1.Salary) Avg_Salary
from employees e1
group by e1.Department
having avg(e1.Salary) = (
select max(Avg_Salary)
from (
	select e1.Department , avg(e1.Salary) Avg_Salary
	from employees e1
	group by e1.Department
) sub)

---

Lv5. 가장 많이 팔린 품목은?

1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.
2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.

내가 시도한 코드

-- 문제 1
select CustomerName, sum(Quantity*p.Price) TotalAmount, count(1) OrderCount
from (
	select o.ProductID, c.CustomerName, o.Quantity
	from customers c inner join  orders o on c.CustomerID = o.CustomerID
) sub inner join  products p on sub.ProductID = p.ProductID
group by 1


-- 문제 2
select p.Category, p.ProductName Top_Product, sum(o.Quantity) TotalSold
from products p inner join orders o on p.ProductID = o.ProductID
group by 1,2
having sum(o.Quantity) = (
	select max(sum_q)
	from (
		select p1.Category, p1.ProductName, sum(o1.Quantity) sum_q
		from products p1 inner join orders o1 on p1.ProductID = o1.ProductID
		group by p1.ProductName
	) sub
	where sub.Category = p.Category
)

• 문제 1
  결과는 맞았지만, join을 중첩해서 사용할 수 있다.
  

최종 코드

-- 문제 1
select CustomerName, sum(Quantity*p.Price) TotalAmount, count(1) OrderCount
from customers c
	inner join orders o on c.CustomerID = o.CustomerID
	inner join products p on o.ProductID = p.ProductID
group by 1


-- 문제 2
select p.Category, p.ProductName Top_Product, sum(o.Quantity) TotalSold
from products p inner join orders o on p.ProductID = o.ProductID
group by 1,2
having sum(o.Quantity) = (
	select max(sum_q)
	from (
		select p1.Category, p1.ProductName, sum(o1.Quantity) sum_q
		from products p1 inner join orders o1 on p1.ProductID = o1.ProductID
		group by p1.ProductName
	) sub
	where sub.Category = p.Category
)

---

Lv5. 예산이 가장 큰 프로젝트는?

1. 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원들만 포함된 결과를 조회하는 SQL 쿼리
   (직원의 이름, 부서, 월급)
2. 직원이 참여한 프로젝트 중 예산이 10,000 이상인 프로젝트만을 조회하는 SQL 쿼리
   (직원 이름, 프로젝트 이름, 프로젝트 예산)

내가 시도한 코드

-- 문제 1
select e.Name, e.Department, e.Salary
from employees e
where e.Salary = (
	select max_s
	from (
		select e2.Name, e2.Department, max(e2.Salary) max_s
		from employees e2
		group by e2.Department
	) sub
	where sub.Department = e.Department
)

• 문제 1
  꼭 select * from (서브쿼리문) 별명의 형식을 가져야만 서브쿼리가 아니라, (서브쿼리문) 도 서브쿼리에 해당된다.

최종 코드

-- 문제 1
select e.Name, e.Department, e.Salary
from employees e
where e.Salary = (
	select max(e2.Salary) max_s
	from employees e2
	where e2.Department = e.Department
)

-- 문제 2
select e.Name, p.ProjectName, p.Budget
from employees e 
	inner join employeeprojects ep on e.EmployeeID = ep.EmployeeID
	inner join projects p on ep.ProjectID = p.ProjectID 
where p.Budget >= 10000
order by 3 desc

---

---

📜 다음 학습 목표

• 선택학습 진행 : java