CSE { DATABASE SYSTEMs Lab 3 Solution

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. What is the address and phone number of \Customer#000000227″?

2. What are the names of the suppliers with the smallest account balance? Print the name and the balance.

3. Find all the items with the return ag set to \N” shipped on September 25, 1995. Print only the quantity and the extended price.

4. What is the maximum completion time for a lineitem? (from commit date to ship date)

5. What is the minimum and maximum account balance among the customers in the \BUILDING” market segment?

6. What are the distinct countries of customers who ordered items in \December 1996″?

7. What is the receipt date and the total number of items per that date ordered by \Customer#000000118″?

8. Find the suppliers from \ASIA” who have at least \$1000″ on account balance. Print their name and the account balance.

9. Find the average account balance of the suppliers from countries with 5 or more suppliers. Print the country, the number of suppliers, and the average account balance.

10. Find the total price of orders made by customers from \EUROPE” in 1996.

11. How many distinct customers received at least a 4% discount at least one time?

12. Find the total number of nished orders for customers from each region and print the result in de-creasing order of these numbers. (see orderstatus; F stands for nished)

13. Find the average account balance of all the customers from \EUROPE” in the \MACHINERY” market segment.

14. Find how many \1-URGENT” priority orders have been posted by customers from \Brazil” between 1994 and 1997, included.

15. Find the total number of \3-MEDIUM” priority orders supplied by suppliers in each country each year. Print the country, the year, and the number of orders.

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 Lab3 under Lab contains an archive lab3-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 name 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-lab3.zip where firstname and lastname are your rst and last names, respectively. The type of the archive le has to be zip.