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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started