🎯시작 전 목표

1. SQL 문법 실전문제 4번~ 풀기

💻Today I learned

✔ SQL 문법 실전문제 4번~ 풀기

• 실전문제 4번

  갑자기 난이도가 급상승했다.
  더군다나 Having이라는 초면인 문법을 사용하라는 요구가 있어 찾아보았다.

  아하~
  더군다나 캡쳐에는 나와있지 않지만, 두 문제 모두 subquary, join, group by, having등을 사용해 풀 수 있어야 한다는 조건이 있는데, 필수적으로 모두 사용해야 한다는 것인지, 적재적소로 알맞게 사용하라는 것인지 잘 모르겠다.
  덤으로 데이터베이스도 만들어야하는 상홯
  일단은 되도록 많이 사용해 쿼리를 깔끔하게 만든다는 생각으로 작성해보았다.

Select _cn, COUNT(_oi) OrderCount, coalesce(sum(_ta), 0) TotalSpent
from(
Select c.CustomerName _cn, o.orderID _oi, o.totalAmount _ta from Customers c left join Orders o on c.CustomerID = o.CustomerID
) a group by 1 having count(_oi) >= 0;

Select c.Country, c.CustomerName Top_Customer, coalesce(sum(o.TotalAmount),0) Top_Spent
from Customers c left join Orders o on c.CustomerID = o.CustomerID
group by 1, 2 having coalesce(sum(o.TotalAmount),0) = 
(
	Select max(totalSpent) from
    (
		Select c2.Country, coalesce(sum(o2.TotalAmount),0) totalSpent from Customers c2 left join Orders o2 on c2.CustomerID = o2.CustomerID
		group by 1, c2.CustomerID
        ) ct where ct.Country = c.Country
    )order by 3 desc;

인생은 생각대로만 되지는 않는다는 것을 다시금 깨닫게 되었다.
쿼리의 실행결과는 다음과 같다.

참고로 semin은 원래 예제에 없는 데이터인데 확인을 위해 넣어봤다.
다음 레벨로 넘어가도록 하자.

• 실전문제 4-2번

  이 문제도 전 문제와 마찬가지로 subquary, join, group by, having등을 사용해 풀 수 있어야 한다는 조건이 있는 것을 보아 꼭 다 쓰란 말은 아니고 가진 능력을 여과없이 활용해 보다 효율적으로 쿼리를 작성하라는 의도가 아닌가 싶다.
  또한 여기서 추측할 수 있는 건, 한 테이블을 subquary를 이용해 여러 번 사용하는 것이 가능하다는 사실이다.
  이를 생각하며 작성한 쿼리는 다음과 같다.

  Select e.Name, e.Department, e.Salary, top_earner._topE, top_earner._topS
  from employees e join
  (	Select Department, Name _topE, Salary _topS from Employees 
  	where (Department, Salary) in(Select Department, MAX(Salary) from Employees group by 1)
  ) top_earner ON e.Department = top_earner.Department;

첫 번째 문제는 어렵지 않게 풀 수 있었으나
두 번째 문제는 간단한 걸 꼬아서 하려니 되려 힘들었다.

Select Department, round(avg(Salary)) from Employees group by 1 order by 2 desc limit 1;
//그냥 이렇게 하면 되는 거 아닌가? 싶은 생각이 들었지만..

Select Department,  round(avg(Salary)) '평균연봉' from Employees group by 1
having avg(Salary) = (
        Select max(avg_s)
        from ( Select avg(Salary) avg_s from Employees group by Department)avg_salaries 
);

두 쿼리의 결과는 이러하다.

나는 자비로워서 공동 1등도 1등으로 취급했지만 그게 싫다면 끝에 limit 1을 붙여도 된다.

• 실전문제 5번

  아 3줄요약좀요
  매우 귀찮지만 끝이 얼마남지 않은 관계로 테이블부터 추가해줬다.
  차근차근 하니 생각보다 할만하고 슬슬 감이 잡히는 것 같다.

  Select CustomerName, _ta TotalAmount, _cnt OrderCount from customers c join
  (Select CustomerID _ci, sum(p.price*o.quantity) _ta, count(1) _cnt
  	from orders o left join products p on o.ProductID = p.ProductID group by 1
  )_aa on _aa._ci= c.CustomerID;
  
  Select p.Category, p.ProductName, SUM(o.Quantity) TotalSolid from Products p left join Orders o on p.ProductID = o.ProductID
  group by 1,2 having sum(o.Quantity) = (
          Select max(total_solid) from( Select sum(o2.Quantity) total_solid from Products p2 left join Orders o2 on p2.ProductID = o2.ProductID
              where p2.Category = p.Category group by p2.ProductID
          ) dsa
      );

쿼리 실행결과는 다음과 같다.

대망의 마지막 문제가 남았다.

• 실전문제 5-2번 Last

  문제만 봤을 땐 그다지 어려워 보이진 않는다.

  Select e.Name, e.Department, e.Salary from Employees e join (
      Select Department, max(Salary) MaxSalary from Employees group by Department
  	) max_salaries on e.Department = max_salaries.Department and e.Salary = max_salaries.MaxSalary;
  
  Select e.Name AS EmployeeName, p.ProjectName, p.Budget from Employees e join EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
  	join Projects p ON ep.ProjectID = p.ProjectID where p.ProjectID in( Select ProjectID from Projects where Budget >= 10000 );

  실행 결과는 다음과 같다.

  이로써 사전캠프 퀘스트 문제는 모두 풀었다.

#마치며

물론 오늘 공부한 게 끝은 아니고 글을 올린 뒤 데일리 루틴도 최대한 풀어 볼 생각이지만
자잘자잘하게 일일이 올리긴 좀 그래서 앞으로는 고난을 겪은 문제만을 가지고 와서 정리하는 것이 낫다고 생각한 만큼 양보단 질에 초점을 맞춰 TIL도 마찬가지로 점점 개선해나가야 하겠다는 생각이 든다.