DB入門

Web上でSQLを試すことのできるサイト
W3Schools Try-SQL Editor(https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all ):ECサイト・販売管理を想定したサンプルデータから自由にSQLを試すことが出来る。
SQL Practice(https://www.sql-practice.com/ ):病院業務を想定したデータベース。問題を解きながらSQLを実行確認出来る(英語)
PostgreSQL Exercises(https://pgexercises.com/ ): PostgreSQLの演習が可能。
SQLBolt(https://sqlbolt.com/ ): SQL構文をStepByStepで学ぶことが出来る。
DB Fiddle(https://www.db-fiddle.com/): 自由にテーブルを作成して試すことが出来る。

0. データを確認する
1. CustomerIDが1の顧客情報を取得する
2. CustomerIDが20の注文情報を取得する
3. 商品ごとの累計購入数
4. 一度も注文されていない商品
5. 商品ごとに、売れた回数（注文明細の件数）と総売上額を集計する
6. 顧客ごとの最終購入日
7. 購入金額の多い順に顧客の総購入額を取得する
8. 1996年7月のカテゴリ別売上

Transcript

14. ★ メインで使用 1 W3Schools Try-SQL Editor https://www.w3schools.com/sql/trysql.asp 2 SQL Practice https://www.sql-practice.com/ 3 PostgreSQL Exercises https://pgexercises.com/ 4 SQLBolt https://sqlbolt.com/ 5 DB Fiddle https://www.db-fiddle.com/
15. 各テーブルの中身を確認する SELECT * FROM Customers; -- 顧客(顧客名) SELECT * FROM Categories; -- 商品カテゴリ SELECT * FROM Orders; -- 注文(注文ID,顧客ID,注文日) SELECT * FROM OrderDetails; -- 注文明細(注文ID,商品,注文数) SELECT * FROM Products; -- 商品(商品名、単価) SELECT * FROM Employees; -- 受注担当者 SELECT * FROM Shippers; -- 配送業者 SELECT * FROM Suppliers; -- 仕入れ先 SELECT * FROM 各テーブルの関係 矢印は「1 → 多」の関係を表します Customers Employees Shippers Orders OrderDetails Products Categories Suppliers 1 N 1 N 1 1 1 N
16. -- 顧客IDが1の顧客を取得 SELECT * FROM Customers WHERE CustomerID = 1; -- 顧客IDが1の顧客を取得 SELECT * FROM Customers WHERE CustomerID = 1; WHERE CustomerID = 1 実行結果 （CustomerID = 1 に一致する1件） Country PostalCode City Address ContactName CustomerName CustomerID Germany 12209 Berlin Obere Str. 57 Maria Anders Alfreds Futterkiste 1
17. SELECT o.OrderID, c.CustomerID, c.CustomerName, o.OrderDate FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID WHERE o.CustomerID = 20 ORDER BY o.CustomerID; SELECT o.OrderID, c.CustomerID, c.CustomerName, o.OrderDate FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID WHERE o.CustomerID = 20 ORDER BY o.CustomerID; INNER JOIN ORDER BY OrderDate CustomerName CustomerID OrderID 1996-07-17 Ernst Handel 20 10258 1996-07-23 Ernst Handel 20 10263 1996-11-11 Ernst Handel 20 10351
18. SELECT p.ProductID, p.ProductName, SUM(od.Quantity) AS TotalSalesQuantity FROM Products p INNER JOIN OrderDetails od ON p.ProductID = od.ProductID GROUP BY p.ProductID, p.ProductName ORDER BY TotalSalesQuantity DESC; SELECT p.ProductID, p.ProductName, SUM(od.Quantity) AS TotalSalesQuantity FROM Products p INNER JOIN OrderDetails od ON p.ProductID = od.ProductID GROUP BY p.ProductID, p.ProductName ORDER BY TotalSalesQuantity DESC; SUM GROUP BY TotalSalesQuantity ProductName ProductID 458 Gorgonzola Telino 31 430 Camembert Pierrot 60 369 Steeleye Stout 35
19. SELECT p.ProductID, p.ProductName, COUNT(od.OrderDetailID) AS SalesCount, SUM(p.Price * od.Quantity) AS TotalSalesAmount FROM Products p INNER JOIN OrderDetails od ON p.ProductID = od.ProductID GROUP BY p.ProductID, p.ProductName ORDER BY TotalSalesAmount DESC; SELECT p.ProductID, p.ProductName, COUNT(od.OrderDetailID) AS SalesCount, SUM(p.Price * od.Quantity) AS TotalSalesAmount FROM Products p INNER JOIN OrderDetails od ON p.ProductID = od.ProductID GROUP BY p.ProductID, p.ProductName ORDER BY TotalSalesAmount DESC; COUNT SUM(Price * Quantity) TotalSalesAmount SalesCount ProductName ProductID 62976.50 7 Côte de Blaye 38 20796.72 9 Thüringer Rostbratwurst 29 19030.00 14 Raclette Courdavault 59
20. 5 SELECT p.ProductID, p.ProductName FROM Products p WHERE NOT EXISTS ( SELECT 1 FROM OrderDetails od INNER JOIN Orders o ON od.OrderID = o.OrderID WHERE od.ProductID = p.ProductID AND o.OrderDate >= '1996-01-01' AND o.OrderDate < '1997-01-01' ) ORDER BY p.ProductID; SELECT p.ProductID, p.ProductName FROM Products p WHERE NOT EXISTS ( SELECT 1 FROM OrderDetails od INNER JOIN Orders o ON od.OrderID = o.OrderID WHERE od.ProductID = p.ProductID AND o.OrderDate >= '1996-01-01' AND o.OrderDate < '1997-01-01' ) ORDER BY p.ProductID; NOT EXISTS SELECT ProductName ProductID Mishi Kobe Niku 9 Chocolade 48 Sirop dérable 61
21. SELECT c.CustomerID, c.CustomerName, MAX(o.OrderDate) AS LastOrderDate FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName ORDER BY LastOrderDate DESC; SELECT c.CustomerID, c.CustomerName, MAX(o.OrderDate) AS LastOrderDate FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName ORDER BY LastOrderDate DESC; MAX LEFT JOIN LastOrderDate CustomerName CustomerID 1997-02-12 Reggiani Caseifici 66 1997-02-11 Ernst Handel 20 1997-02-10 Save-a-lot Markets 71 MIN( )
22. SELECT c.CustomerID, c.CustomerName, SUM(p.Price * od.Quantity) AS SalesAmount FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY c.CustomerID, c.CustomerName ORDER BY SalesAmount DESC; SELECT c.CustomerID, c.CustomerName, SUM(p.Price * od.Quantity) AS SalesAmount FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY c.CustomerID, c.CustomerName ORDER BY SalesAmount DESC; INNER JOIN ×3 SUM(Price * Quantity) SalesAmount CustomerName CustomerID 35631.21 Ernst Handel 20 23362.60 Mère Paillarde 51 22500.06 Save-a-lot Markets 71
23. SELECT c.CategoryName, SUM(p.Price * od.Quantity) AS SalesAmount FROM Orders o INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID INNER JOIN Categories c ON p.CategoryID = c.CategoryID WHERE o.OrderDate >= '1996-07-01' AND o.OrderDate < '1996-08-01' GROUP BY c.CategoryID, c.CategoryName ORDER BY SalesAmount DESC; SELECT c.CategoryName, SUM(p.Price * od.Quantity) AS SalesAmount FROM Orders o INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID INNER JOIN Categories c ON p.CategoryID = c.CategoryID WHERE o.OrderDate >= '1996-07-01' AND o.OrderDate < '1996-08-01' GROUP BY c.CategoryID, c.CategoryName ORDER BY SalesAmount DESC; INNER JOIN ×3 WHERE SalesAmount CategoryName 9231.70 Dairy Products 7834.75 Confections 5204.25 Produce