Given the following relational schema, write queries in SQL to answer the Englis

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.

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.

Explanation / Answer

Query to fetch the details according to the given relational schema:

1. Return the product names and inventory value of each product (price*inventory) ordered by product name.

SELECT pname,price*inventory AS inventoryvalue from Product ORDER BY pname;

2. Return total value of products in inventory.

SELECT SUM(price*inventory) AS TotalValue from Product;

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

SELECT Customer.cname,Product.pname,ShippedProduct.amount FROM
Customer LEFT OUTER JOIN Shipment ON Customer.cid=Shipment.cid
LEFT OUTER JOIN ShippedProduct ON Shipment.sid=ShippedProduct.sid
LEFT OUTER JOIN Product ON ShippedProduct.pid=Product.pid WHERE Customer.state='IA';

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

SELECT pname from Product where pname LIKE '%Ch%' and price>(SELECT AVG(price) FROM product);

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.