CSE111 Lab 4 Solved

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):

1. Print the total amount of money (o totalprice) spent on orders by each customer from FRANCE.
2. Return the number of suppliers in each country.
3. How many orders are posted by customers in each country in EUROPE?
4. How many parts with size below 20 does each supplier from BRAZIL sell?
5. Find the number of orders posted by each customer from GERMANY in 1995.
6. How many parts produced by each manufacturer (p mfgr) are ordered at each order priority?
7. How many orders do customers in each country in ASIA have in each order status?
8. Print the number of orders completed in 1995 by the suppliers in each country in AMERICA (see orders.orderstatus; F stands for complete).
9. How many different clerks did the suppliers in RUSSIA work with?
10. Find the maximum discount for each type of items having ECONOMY in their type name.
11. Find the supplier(s) with the largest account balance in every region.
12. What is the maximum account balance for the suppliers in each region?
13. How many distinct orders are between suppliers in EUROPE and customers in CANADA?
14. List the total value (l extendedprice) of all the lineitems sold between any two regions. Print the supplying region, the customer region, and the total value.
15. How many orders involve a customer with negative balance and a supplier with positive balance?

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 different database. Folder Lab4 under Lab contains an archive lab4-results.zip with the correct answers. There is a file 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 file with the SQL statement for every query. The names of the files have to be 1.sql for query 1, 2.sql for query 2, and so on. There should be exactly 15 files in the archive file. The name of the archive file has to be firstname-lastname-lab4.zip where firstname and lastname are your first and last names, respectively. The type of the archive file has to be zip.