CSE111– Lab4 Solved

DATABASESYSTEMS


In this lab session you have to write 15 SQL queries for the TPCH database created and populated in the previous labs. The queries are the following (1 point per query):

1.       Find the total price paid on orders by every customer from FRANCE in 1995. Print the customer name and the total price.

2.       Find the number of suppliers from every region.

3.       How many orders are posted by customers in every country in AMERICA?

4.       How many parts with size below 20 does every supplier from CANADA offer? Print the name of the supplier and the number of parts.

5.       Find the number of orders posted by every customer from GERMANY in 1993.

6.       How many unique parts produced by every supplier in CANADA are ordered at every priority? Print the supplier name, the order priority, and the number of parts.

7.       How many orders do customers in every nation in AMERICA have in every status? Print the nation name, the order status, and the count.

8.       Find the number of distinct orders completed in 1995 by the suppliers in every nation. An order status of F stands for complete. Print only those nations for which the number of orders is larger than 50.

9.       How many different order clerks did the suppliers in UNITED STATES work with?

10.   Find the minimum and maximum discount for every part having ECONOMY and COPPER in its type.

11.   Find the supplier with the largest account balance in every region. Print the region name, the suppliername, and the account balance.

12.   What is the maximum account balance for the suppliers in every nation? Print only those nations forwhich the maximum balance is larger than 9000.

13.   How many line items are supplied by suppliers in AFRICA for orders made by customers in UNITED STATES?

14.   List the maximum total price of an order between any two regions, i.e., the suppliers are from oneregion and the customers are from the other region.

15.   How many distinct orders are between customers with positive account balance and suppliers withnegative account balance?

In order to complete the lab you have to perform the following tasks:

1.    Write the SQL statement corresponding to every query in the file test/x.sql, where x is the number of the query above. Every query goes into its separate file. These are the only files you have to modify and submit in this assignment.

2.    You can check the correctness of your queries by executing the command ./test.sh in the terminal. You have to be in the main lab folder. The expected output is available in results/x.res, where x is the number of the query. The output produced by your code is available in output/x.out. They have to match for every query, e.g., 1.res has to match with 1.out.

3.    In case there are any errors, repeat the process from step 1 for the incorrect queries.

4.    The submission consists of a compressed zip file that contains the files in the test folder. The name of the file has to be lab-4.zip. When you create the file, include the folder test into the compression, not every file test/x.sql separately.