Question

Given the following relational schema, write queries in SQL to answer the English questions. The Access Database for the schema is available, as is a DDL file. It is also available on the MySQL server. You must only submit the SQL for your answers. You can get your answers without using a DBMS or by using Access or MySQL.

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 product names and inventory value of each product (price*inventory) ordered by product name.

2. Return total value of products in inventory.

3. For all customers in Iowa ('IA') list the customer name, product name, and amount for all shipments

4. Return the products (name) whose name contains 'Ch' with a price more than the average price.

5. 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.

6. Return the number of shipments to customers with first name Scott.

7. Return the list of customers (no duplicates) that have never received a shipment.

8. Return product names and total amount shipped (price*amount) for products shipping over $1,000.

9. Return all customers and their states that share a state with another customer.

10. Return the shipment id and total value of the entire shipment (price*amount) ordered by the shipment values ascending.

Answer 1

GSELECT nt(sid cid From Shipment s customer c where c.cidニs.cid and C cnameScott " GDU C.Cid チ SELECT DISTINCT ( C, cid, C. cname) From customers where ccid NOT IN select cid fTom shipment