SQL 미션은 너무 어려워요

두둥 너무 어려워 보이는 SQL 미션

• CRUD를 배웠기 때문에 관련 미션이 나올 거라는 착각
• 문제보니 여러 테이블을 짬뽕해서 데이터 추출 해야하는 느낌이듬
• 이제 막 CRUD를 뗀 개발자에게 너무 어려운 미션 아니오!!
• 미션은 요기
  • https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_concat

첫번째 문제

문제 파악

• 문제 : 200개 이상 팔린 상품명과 그 수량을 수량 기준 내림차순으로 보여주세요.
• OrderDeatail 테이블에 ProductId와 Quantity라는 컬럼이 존재
• 이것을 이용해서 같은 ProductId를 가지는 Quantitiy를 모두 더 해 200개 이상인 ProductId를 추출
  • 어떻게 하지?
• 추출 된 ProductId들로 Product 테이블에서 상품명 추출해내기
  
  • 이건 괜찮아보임
• 상품명 이랑 판매 수량이 따로 따로 구해지는 데 어떻게 같이 보여주지?
  • 감도 안잡히는데..?

 

GROUPBY, HAVING

• GROUPBY : 하나 이상의 열을 기준으로 그룹을 묶어서 결과를 가져오는 집계함수
• HAVING : 그룹 함수를 포함한 조건에서 사용

SELECT [DISTINCT] 컬럼, 그룹 함수(컬럼)

FROM 테이블명

[WHERE 조건]

[GROUP BY Group대상]

[HAVING 그룹 함수 포함 조건]

[ORDER BY 정렬대상 [ASC/DESC]]

• 그룹함수에는 중에 sum 이라는 녀석이 있었다. (오!?)
• 자세한 사항은 아래 블로그 참조
  • keep-cool.tistory.com/37

SELECT ProductID, SUM(Quantity) FROM OrderDetails 
GROUP BY ProductID HAVING SUM(Quantity) >= 200;

• ProductID 별로 Quantity 합계를 구할 수 있고, 합계가 200개 이상인 것을 추출해 온다.

SUBQUERY

• 위의 상태에서 ProductId을 매핑된 상품명으로 만 바꾸어준다면 상품명과 상품수량을 둘 다 조회 할 수 있다!
  • select 안에 select라고 검색 해 봤다. -> subquery 라는 키워드를 획득했다.

SELECT 
    (SELECT ProductName 
    FROM Products 
    WHERE Products.ProductID = OrderDetails.ProductID) 
    AS Product, 
    SUM(Quantity) AS TotalQuantity 
FROM OrderDetails 
GROUP BY ProductID 
HAVING SUM(Quantity) >= 200 
ORDER BY TotalQuantity DESC;

• 두 테이블 간 ProductID 비교 가 신기했다.
• as로 테이블 명을 바꾸어서 출력 했다.
  • as 안 쓰면 select문이 컬럼명으로 출력이 되었다;

두번째 문제

• 문제 : 많이 주문한 순으로 고객 리스트(ID, 고객명)를 구해주세요. (고객별 구매한 물품 총 갯수)
• Orders 테이블에 CustomerID와 OrderID가 존재
• OrdersDetails에 상품 주문 Quantity가 존재 -> OrderID를 사용해서 접근한다.
• CustomerID로 Customers 테이블에서 고객명을 찾는다.

SELECT 
    (SELECT CustomerName 
    FROM Customers 
    WHERE Customers.CustomerID = Orders.CustomerID) 
    AS Customer, 
    SUM((SELECT SUM(Quantity) 
    FROM OrderDetails 
    WHERE Orders.OrderID = OrderDetails.OrderID)) 
    AS OrderQuantiriy 
FROM Orders 
GROUP BY CustomerID 
ORDER BY OrderQuantiriy DESC;

• 서브 쿼리를 두개 사용했다.
• 서브 쿼리 결과를 GROUP BY와 SUM을 사용해서 묶어주었다.

 

세번째 문제

• 문제 : 많은 돈을 지출한 순으로 고객 리스트를 구해주세요.
• 두번째 문제에서 구한 많이 주문한 고객리스트를 구했던 방법에서 한 단계 더 들어가 ProductID를 통해 가격들의 합계를 구해 주면 될 것 같았다.
• 아!!!! 3개 테이블을 조합하려니까 머리가 터지는 줄 알았다. 찾아보니 JOIN 키워드가 있어서 사용해 보기로 했다.

INNER JOIN

• 동일한 값이 있는 행을 반환한다.
• 이전에 같은 OrderID를 가지는 데이터들을 Orders와 OrderDetails에서 가져 왔었다.
  • 이전에도 inner join을 사용하고 있었다!
• 이번에는 추가로 FROM 에서 두개의 테이블을 주어서 조인을 시도 했다.
  • Products와 OrderDetails가 ProductID를 통해 조인 된다.

SELECT 
    (SELECT CustomerName 
    FROM Customers 
    WHERE Customers.CustomerID = Orders.CustomerID) 
    AS Customer, 
    SUM((SELECT SUM(Price * Quantity) 
    FROM OrderDetails, Products 
    WHERE Orders.OrderID = OrderDetails.OrderID 
    AND Products.ProductID = OrderDetails.ProductID)) 
    AS PurchaseAmount 
FROM Orders 
GROUP BY CustomerID 
ORDER BY PurchaseAmount DESC;

• 조인의 종류가 여러 가지이던데 천천히 공부해 봐야 겠다.