Question

Given the following relational schema, write queries in SQL to answer the English questions. There is a shipment database on the MySQL server. You can also use the DDL for MySQL. You must only submit the SQL for your answers but you can include the query output as well to help the TA with marking.

Customer(cid: integer, cname: string, address: string, city: string, state: string)
Product(pid: integer, pname: string, price: currency, inventory: integer)
Shipment(sid: integer, cid: integer, shipdate: Date/Time)
ShippedProduct(sid: integer, pid: integer, amount: integer)

1. Return customer names and total sales of products shipped to each customer. Only show customers with total sales of over $200with the results ordered in descending order of total sales.

   Output:

   +-----------------+-------------+
   | cname           | total_sales |
   +-----------------+-------------+
   | Joe Smithsonian |     2427.80 |
   | Russell Johnson |     1057.68 |
   | Scott Charles   |      260.00 |
   +-----------------+-------------+

Answer 1

Return customer names and total sales of products shipped to each customer. Only show customers with total sales of over $200 with the results ordered in descending order of total sales.

select cname, sum(total_sales) total_sales from Customer c, Shipment s,
(select sid, sum(amount*price) total_sales from ShippedProduct sp, Product p where sp.pid = p.pid group by sid) sales
where c.cid = s.cid and s.sid = sales.sid group by cname having total_sales > 200 order by total_sales desc;

The above statement first calculate the sales for each shipment by getting the price from Product table and amount shipped from ShippedProduct by joining the tables in pid. Then it calculates the price of all the shipments for each customer and displays only those customer names (from Customer table ) and total sales by the customer where the total sales > 200 by joining the intermediate table by Shipment table on field sid and Shipment table with Customer table on field cid. The result is ordered by total sales in descending order