Write SQL statements for the following queries on the TPCH database
1. Find the average number of items (l quantity) shipped per month (l shipdate) in 1996.
2. Find the number of customers that had at most two orders in August 1995 (o orderdate).
3. Find how many parts are supplied by more than one supplier from CANADA.
4. Find how many suppliers from CANADA supply at least 4 different parts.
5. Find how many distinct suppliers supply the least expensive part (p retailprice).
6. Find the supplier-customer pair(s) of the most expensive (o totalprice) completed order(s). (’F’ in o orderstatus)
7. Find how many suppliers have less than 30 orders from customers in GERMANY and FRANCE.
8. Find how many customers have at least one order supplied only by suppliers from ASIA.
9. What are the parts (p name) ordered by customers from AMERICA that are supplied by exactly 4 distinct suppliers from ASIA?
10. What is the region where customers spend the largest amount of money (l extendedprice) buying items from suppliers in the same region?
11. What is the nation with the largest number of customers?
12. What is the nation where customers spend the largest amount of money (o totalprice)?
13. What is the country with the most powerful industry, i.e., selling items totaling the largest amount ofmoney (l extendedprice), in 1996 (l shipdate)?
14. Compute, for each country, the value (l extendedprice) of the economic exchange, i.e., the difference between the value of items from suppliers in that country sold to customers in other countries and the value of items bought by local customers from foreign suppliers, in 1996 (l shipdate).
15. Compute the relative ratio change in the economic exchange for each country between 1994 and 1996? There should be two columns in the output for each country: 1995 and 1996. Hint: search for the SQL CASE keyword to permute a table.
