CSE111–Lab5 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.       How many customers are not from EUROPE or AFRICA or ASIA?

2.       How many suppliers in every region have less balance in their account than the average account balanceof their own region?

3.       For the line items ordered in October 1996 (o orderdate), find the smallest discount that is larger than the average discount among all the orders.

4.       How many customers and suppliers are in every country from AFRICA?

5.       For parts whose type contains STEEL, return the name of the supplier from ASIA that can supply them at minimum cost (ps supplycost), for every part size. Print the supplier name together with the part size and the minimum cost.

6.       Based on the available quantity of items, who is the manufacturer p mfgr of the most popular item (the more popular an item is, the less available it is in ps availqty) from Supplier#000000010?

7.       For every order priority, count the number of parts ordered in 1997 and received later (l receiptdate) than the commit date (l commitdate).

8.       Count the number of distinct suppliers that supply parts whose type contains POLISHED and have size equal to any of 3, 23, 36, and 49.

9.       Print the name of the parts supplied by suppliers from UNITED STATES that have total value in the top 1% total values across all the supplied parts. The total value is ps supplycost*ps availqty. Hint: Use the LIMIT keyword.

10.   How many customers from every region have never placed an order and have less than the averageaccount balance?

11.   Find the lowest value line item(s) (l extendedprice*(1-l discount)) shipped after October 2, 1996. Print the name of the part corresponding to these line item(s).

12.   What is the total supply cost (ps  supplycost) for parts less expensive than $1000 (p retailprice) shipped in 1997 (l shipdate) by suppliers who did not supply any line item with an extended price less than 2000 in 1996?

13.   Count the number of orders made in the fourth quarter of 1997 in which at least one line item was received by a customer earlier than its commit date. List the count of such orders for every order priority.

14.   For any two regions, find the gross discounted revenue (l extendedprice*(1-l discount)) derived from line items in which parts are shipped from a supplier in the first region to a customer in the second region in 1996 and 1997. List the supplier region, the customer region, the year (l  shipdate), and the revenue from shipments that took place in that year.

15.   The market share for a given nation within a given region is defined as the fraction of the revenue fromthe line items ordered by customers in the given region that are supplied by suppliers from the given nation. The revenue of a line item is defined as l  extendedprice*(1-l  discount). Determine the market share of UNITED STATES in ASIA in 1997 (l shipdate).

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-5.zip. When you create the file, include the folder test into the compression, not every file test/x.sql separately.

Lab 5                                                                                                                                                                                                   2