수업문제를 복습.

sql연습사이트인 w3school에서 문제를 풀어보면된다.

문제를 풀기전에 일단 각 테이블마다 무슨 칼럼이있는지 조회를 해본다.

OrderDetails는 OrderDetailID, OrderID, ProductID, Quantity
Order는 OrderID, CustomerID, EmployeeID, OrderDate, ShipperID
Customers는 CustomerID, CustomerName, ContactName, Addredd, City, PstoalCode, Country
Products는 ProductID, PfoductName, SupplierID, CategoryID, Unit, Price

각 주문(OrderID)별 매출

select O.OrderID,
sum(Quantity * Price)
from OrderDetails O
inner join Products P on O.ProductID = P.ProductID
group by O.OrderID

우선 OrderDetails테이블에 Products테이블을 inner join시키고 select에서 매출을 보여줘야하는데 OrderID로 group by시키고나서 Quantity 와 Price를 곱하면 매출이나온다.

각 고객(CusomterID)별 매출

select O.CustomerID,
sum(Price * quantity)
from Orders O
inner join Customers C on O.customerID = C.customerID
inner join OrderDetails OD on O.OrderID = OD.OrderID
inner join Products P on OD.ProductID = P.ProductID
group by O.CustomerID

매출을 구하기위해선 Products 테이블에 들어있는 Price칼럼과 OrderDetail테이블에 들어있는 Quantity칼럼이 필요하다

그리고 고객별 매출이므로 Customer와 나머지 정보들을 연결시켜줄 Orders테이블도 연결해주고 CustomerID로 그룹화해서 매출을 구해주면된다.

각 고객(CusomterID)별 주문(OrderID) 건수(주문을 안한 고객도 0으로 나와야 함)

select C.CustomerID,
count(OrderID)
from Customers C
left outer join Orders O on C.CustomerID = O.CustomerID
group by C.CustomerID

주문을 안한 고객이 0으로 나오게하려면 left outer join을 해야한다.

그외에는 쉽다.

가장 많이 팔린 상품(ProductID) top3

select ProductID
from OrderDetails
group by ProductID
order by count(quantity) desc
limit 3

상품의 판매과 상품의ID 둘다 OrderDetails테이블에있기 때문에 OrderDetails에서 상품ID로 그룹화를하고, 판매수량의 양으로 내림차순 정렬하여 위에 세개를 뽑으면된다.

연도(OrderDate)별 매출

select year(OrderDate) as '연도',
sum(Price * Quantity) as '매출'
from Orders O
inner join OrderDetails OD on O.OrderID = OD.OrderID
inner join Products P on OD.ProductID = P.ProductID
group by year(OrderDate)

연도만 구하기위해 year()함수를 사용해주었다.

매출을 구하기위해선 OrderDetails와 Products테이블이 필요해서 둘을 inner join시켜주었다.

그리고 select해주었다.

월(OrderDate)별 매출

select concat(year(OrderDate),'-',month(OrderDate)) as '월',
sum(Price * Quantity) as '매출'
from Orders O
inner join OrderDetails OD on O.OrderID = OD.OrderID
inner join Products P on OD.ProductID = P.ProductID
group by concat(year(OrderDate),'-',month(OrderDate))

위에 연도별 매출에서 연도자리에 concat으로 month()함수를 붙여주었다.