Description
In this Lab session you will write 15 SQL queries for the TPCH database. You will execute the queries for the data loaded in Lab 2 and check the results.
Write SQL statements for the following queries on the TPCH database (1 pt. per query):
-
Find the average number of items (l quantity) shipped per month (l shipdate) in 1996.
-
Find the number of customers that had at most two orders in August 1995 (o orderdate).
-
Find how many parts are supplied by more than one supplier from CANADA.
-
Find how many suppliers from CANADA supply at least 4 di erent parts.
-
Find how many distinct suppliers supply the least expensive part (p retailprice).
-
Find the supplier-customer pair(s) of the most expensive (o totalprice) completed order(s). (’F’ in o orderstatus)
-
Find how many suppliers have less than 30 orders from customers in GERMANY and FRANCE.
-
Find how many customers have at least one order supplied only by suppliers from ASIA.
-
What are the parts (p name) ordered by customers from AMERICA that are supplied by exactly 4 distinct suppliers from ASIA?
-
What is the region where customers spend the largest amount of money (l extendedprice) buying items from suppliers in the same region?
-
What is the nation with the largest number of customers?
-
What is the nation where customers spend the largest amount of money (o totalprice)?
-
What is the country with the most powerful industry, i.e., selling items totaling the largest amount of money (l extendedprice), in 1996 (l shipdate)?
-
Compute, for each country, the value (l extendedprice) of the economic exchange, i.e., the di erence 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).
-
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.
Instructions. You are required to execute the queries on the TPCH database you populated in Lab 2. We provide you the correct answers for this database. We will check the correctness of your queries on a di erent database. Folder Lab6 under Lab contains an archive lab6-results.zip with the correct answers. There is a le for each query, e.g., 1.out for query 1, 2.out for query 2, etc. You are required to turn in an archive containing a le with the SQL statement for every query. The names of the les have to be 1.sql for query 1, 2.sql for query 2, and so on. There should be exactly 15 les in the archive le. The name of the archive le has to be firstname-lastname-lab6.zip where firstname and lastname are your rst and last names, respectively. The type of the archive le has to be zip.
