SQL, 이 정도는 알아야지 😎

만약 SQL 리부트를 하고 싶다면?

youtube.com

https://www.youtube.com/watch?v=_DgpFbcGuAc

실습하기

1. 200개 이상 팔린 상품명과 그 수량을 수량 기준 내림차순으로 보여주세요.

   SELECT 
   	pd.ProductID AS "상품아이디",
   	pd.ProductName AS "상품명",
   	SUM(od.Quantity) AS "총수량"
   FROM OrderDetails AS od
   INNER JOIN Products AS pd
   	ON od.ProductID = pd.ProductID
   GROUP BY pd.ProductID
   HAVING SUM(od.Quantity) >= 200
   ORDER BY SUM(od.Quantity) DESC;

   

2. 많이 주문한 순으로 고객 리스트(ID, 고객명)를 구해주세요. (고객별 구매한 물품 총 갯수)

   SELECT
       cs.CustomerID AS "고객아이디",
       cs.CustomerName AS "고객이름",
       SUM(Quantity) AS "주문량"
   FROM Orders AS od
   INNER JOIN Customers AS cs
   	ON cs.CustomerID = od.CustomerID
   INNER JOIN OrderDetails AS od_detail
   	ON od.OrderID = od_detail.OrderID
   GROUP BY cs.CustomerID
   ORDER BY SUM(Quantity) DESC;

   

3. 많은 돈을 지출한 순으로 고객 리스트를 구해주세요.

   SELECT
       cs.CustomerID AS "고객아이디",
       cs.CustomerName AS "고객이름",
       SUM(Quantity * Price) AS "지출금액($)"
   FROM Orders AS od
   INNER JOIN Customers AS cs
   	ON cs.CustomerID = od.CustomerID
   INNER JOIN OrderDetails AS od_detail
   	ON od.OrderID = od_detail.OrderID
   INNER JOIN Products AS pd
   	ON od_detail.ProductID = pd.ProductID
   GROUP BY cs.CustomerID
   ORDER BY SUM(Quantity * Price) DESC;