For the questions below, use the W3Schools customer orders database. You need to

Question

For the questions below, use the W3Schools customer orders database. You need to submit thoroughly tested SQL statements. For your convenience and reference, relevant data model is attached as a PDF document with the assignment in the Blackboard. Name of this document is "Relationship diagram for W3schools order.pdf."

1) Write a query to show Customers’ Country wise sales totals. Your query should show Customers’ Country name and the total sale value for that Country.

2) Write a query to show Product wise sales totals. Your query should Product name and the total sale value for that Product.

3) Write a query to show the number of orders for each shipper. Your query should show shipper name and the count of orders with the shipper.

4) Write a query to show number of products in each product category. Your query should show Category name and number of products in that category.

5) Write a query that shows Customers’ country wise customer count, and order count. Your query should show country name, total number of customers from that country, and total number of orders from the customers of that country.

6) Write a query that shows the number of products supplied by each Supplier. Your query should show Country name, supplier name and the count of products supplied by that supplier. Note: Sale Price = Orderdetails.Quantity * Products.price (see an example in the class slides deck) W3SCHOOLS SQL is located at http://www.w3schools.com/sql/default.asp (Preferred browser is Chrome).

Customers Customer Id Customer Name ContactName Address City PostalCode Country Shippers Shipper ID ShipperName Phone Orders Order Id Customer Id Employeeld Order Date ShipperID Order Details Order DetailID Order ID ProductID Quantity Suppliers Supplier ID Supplier Name ContactName Address City PostalCode Country Phone Products ProductID Product Name Supplier ID So, Category Id Unit Price Categories LA category Categoy Name Desc rhom

Explanation / Answer

#1)
SELECT C.Country, SUM(OD.Quantity * P.Price) AS 'total sale'
FROM Customers C, Orders O, OrderDetails OD, Products P
WHERE C.CustomerID = O.CustomerID AND
OD.OrderID = O.OrderID AND
P.ProductID= 0.ProductID

#2)

SELECT P.ProductName , SUM(O.Quantity * P.price) AS 'total sale'
From Products P JOIN OrderDetails O
ON P.ProductID= 0.ProductID
GROUP BY P.ProductName

#3)

SELECT S.ShipperName, COUNT(O.OrderID)
FROM Shipper S JOIN Orders O
ON S.ShipperID=O.ShipperID

#4)

SELECT C.CategoryName, COUNT(P.ProductID)
FROM Categories C JOIN ProductID P
ON S.CategoryID=O.CategoryID

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.