Question

12.List all customers, title, artist, quantity ordered

13.List all customers along with the total revenue received from that customer (revenue would be total retail price)

14.List each state and the number of customers from that state

https://imgur.com/a/CN7SQIf

Answer 1

12.List all customers, title, artist, quantity ordered

SELECT c.customer_name,i.title,i.artist,ol.order_qty
FROM Customers c
JOIN Orders o
ON o.Customer_Id = c.Customer_Id
JOIN OrderLine ol
ON ol.Order_Id = o.Order_Id
JOIN Items AS i
ON i.Item_Id = ol.Item_id

13.List all customers along with the total revenue received from that customer (revenue would be total retail price)

SELECT c.customer_name,SUM(ol.Order_qty*i.unit_price) as TotalRevenue
FROM Customers c
JOIN Orders o
ON o.Customer_Id = c.Customer_Id
JOIN OrderLine ol
ON ol.Order_Id = o.Order_Id
JOIN Items AS i
ON i.Item_Id = ol.Item_id

14.List each state and the number of customers from that state

SELECT customer_state,COUNT(c.CustomerID)
FROM Customers c
GROUP BY customer_state