[1주차 - 과제] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시 보드 개발

Customers Table

Employees Table

Orders Table

OrderDetails Table

---

1. Country 별로 ContactName이 ‘A’로 시작하는 Customer의 숫자를 세는 쿼리

SELECT Country, COUNT(*) FROM Customers
WHERE ContactName LIKE 'A%'
GROUP BY Country
;

2. Customer 별로 Order한 Product의 총 Quantity를 세는 쿼리

SELECT C.CustomerID, SUM(OD.Quantity) FROM OrderDetails AS OD
JOIN Orders AS O ON O.OrderID = OD.OrderID
JOIN Customers AS C ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
;

3. 년월별, Employee 별로 Product를 몇 개씩 판매했는지를 표시하는 쿼리

SELECT O.EmployeeID, O.OrderDate ,SUM(OD.Quantity)   
FROM (SELECT a.OrderID, a.EmployeeID, SUBSTR(a.OrderDate,1,7) AS OrderDate
		FROM Orders a) AS O 
JOIN OrderDetails AS OD ON OD.OrderID = O.OrderID 
JOIN Employees AS E ON E.EmployeeID = O.EmployeeID
GROUP BY O.EmployeeID, O.OrderDate
ORDER BY O.EmployeeID
;