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

for choose a column we use select operator, here we select data

from two different table so we use join operator to join the table

select C.cname, A.amount

from Customer C Join ShippedProduct A