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

1. select pname, price*inventory from product order by pnme;

2. select sum(price) from inventory;

3. select c.cname,p.pname, sp.amount from shippedproduct sp join product p

on sp.pid=p.pid

join shipment s

on sp.sid=s.sid

join customer c

on s.cid=c.cid

and c. city='IA';

4. select pname from product where name like '%Ch%' and price >( select avg(price) from product);

5. select cname, sum(amount) from customer c join shipment s on c.cid=s.cid

join shippedproduct sp on s.sid=sp.sid group by cname having sum(amount)> 200;

6. select count(sid) from customer c join shipment s on c.cid=s.cid and substr(cname,1,5)='Scott';

7. select cname from customers where cid not in(select cid from shipment);

8.select pname, price*inventory from product where price*inventory> 1000;

9. select cname, state from customer where stwte in(select distinct state from(select state,count(cid) from customer group by state));

10. select sp.sid, price* amount from shippedproduct sp join product p on

sp.pid=p.pid order by sp.sid ;

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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