Need to write the following SQL queries for the schema provided in the screenshot as well as the DDL in text file. This should be simple for any SQL programmer, I'm new to SQL and having trouble
all queries should be written in the form:
SELECT 
FROM
WHERE

GROUP BY
HAVING

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

1. 2.Return total number of products ordered (amountfield inShippedProduct).
2. 3.For all customers in either Iowa ('IA') or California ('CA') list the customer name, product name, and amount for all shipments.
3. 4.Return the customers (cid,cname) (no duplicates) whose address contains the word 'Street' and have received at least one shipment.
4. 5.Return the products (pid, pname) that are shipped more (in terms ofamount) than the average total amount each product is shipped. E.g. If total amount that 4 products are shipped is 15,5,30,10. The average total amount each is shipped is 15, and only the 3rd product with total amount 30 is above the average.
5. 6.Return the list of products (no duplicates) that have never been in a shipment.
6. 7.Return a list of states and the number of customers in each state.
7. 8.Return a list of states and the total value of all shipments to customers in that state. Only show states whose total value is greater than $5000.
8. 9.Return a list of products (id and name) along with the number of times it has been shipped, the total amount of all shipments, and the total value of all shipments. Only consider shipments after January 13th, 2014, and only show products if they have been shipped at least 3 times.
9. 10.Return all customers and their states that share a state with another customer.

CREATE DATABASE shipment;CREATE TABLE Customer (cid integer,cname varchar(30),address varchar(50),city varchar(30),state varchar(2),primary key (cid) ) ENGINE=InnoDB;CREATE TABLE Product (pid integer,pname varchar(30),price decimal(9,2),inventory integer,primary key (pid) ) ENGINE=InnoDB;CREATE TABLE Shipment (sid integer,cid integer,shipdate datetime,primary key (sid),foreign key (cid) REFERENCES Customer(cid)) ENGINE=InnoDB;CREATE TABLE ShippedProduct (sid integer,pid integer,amount integer,primary key (sid, pid),foreign key (sid) REFERENCES Shipment(sid),foreign key (pid) REFERENCES Product(pid)) ENGINE=InnoDB;INSERT INTO Customer VALUES (1,'Fred Smith','101 Evergreen Terrace','Springfield','IL');INSERT INTO Customer VALUES (2,'Joe Smithsonian','245 Straight Street','Iowa City','IA');INSERT INTO Customer VALUES (3,'Steve Stevenson','24 Michigan Ave.','Chicago','IL');INSERT INTO Customer VALUES (4,'Russell Johnson','1 Hollywood Drive','Hollywood','CA');

Attachments:

• 1495425273-Screen Shot 2016-10-04 at 10.29.10 PM.png
• 1495425273-databaseshipmentddl.rtf