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 the number of shipments to customers with first name 'Scott'.

   Output:

   +----------+
   | count(*) |
   +----------+
   |        3 |
   +----------+

Answer 1

Answer:

Number of shipments to customers with first Name "Scott"

Query:

select count(*) from Shipment s
join Customer c on c.cid = s.cid
where c.cname = "Scott";

/*This query will return the total no of shipments to Customer Scott*/

Let me know in case if you have any questions. Thanks and All the best.