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 product names and total amount shipped (price*amount) for products shipping over $1,000.

   Output:

   +-----------------+-------------+
   | pname           | Total_Sales |
   +-----------------+-------------+
   | Swiss Chocolate |     1055.68 |
   | Wooden Chair    |     2756.00 |
   +-----------------+-------------+

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

SQL Query :

select pname,sum(price*amount) as 'Total_Sales' from product,shippedproduct where product.pid=shippedproduct.pid group by pname HAVING sum(price*amount) > 1000;

Explanation :

• Above SQL query will join two tables product,shippedproduct based on pid  
• and will return pname of the product that having greater than 1000

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.