Task 4 (2 marks)
Connect to sample TPC-H benchmark database as TPCHR user. A conceptual schema of the database is included in a file tpchr.pdf available in TPCHR folder.
(1) Implement the following queries using GROUP BY clause with CUBE operator.
(a) Find the total supply costs (PS_SUPPLYCOST) per part name (P_NAME), per brand (P_BRAND), per part name and brand, and the total supply costs only for the parts with the keys 4576, 4577, 4578, 4579.
(b) Find the total number of customers per nation (N_NAME) per region (R_NAME), per nation and region and the total number of customers for the customer keys 4576, 4577, 4578, 4579.
(2) Implement the following queries using GROUP BY clause with ROLLUP operator.
(a) Find the total price of orders (O_TOTALPRICE) per year (O_ORDERDATE), per year and month (O_ORDERDATE), and total price for the orders with the keys 39044,39045,39046,39047.
(b) Find the average retail price (P_RETAILPRICE) per part name (P_NAME), per part name and brand (P_BRAND), per part name, brand, and type (P_TYPE) and the average price for all parts with the keys 4576, 4577, 4578, 4579.
(3) Implement the following queries using GROUP BY clause with GROUPING SETS operator.
(a) Find the total number of orders per year (O_ORDERDATE), per brand (P_BRAND), per order status and order clerk (O_ORDERSTATUS, O_CLERK), per customer name (C_NAME) and the total number of orders only for the customers with the keys 4576, 4577, 4578, 4579.
(b) Find the total quantity of items (L_QUANTITY) per order year (O_ORDERDATE), and per tax and discount (L_TAX, L_DISCOUNT) for the orders with the keys 39044,39045,39046,39047.
When ready, save your SELECT statements in SQL script solution4.sql. Then, process a SQL script solution4.sql and save the results in a report solution4.lst. It is explained in Cookbook, Recipe 2.4, Step 9 -How to create and to save a report ?- how to save a report from processing of SQL script in a text file.
Note, that all SQL statements processed must be included in the report. To achieve that put the following SQL*Plus commands at the beginning of your SQL script solution4.sql:
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
Deliverables
Submit a file solution4.lst with a report from processing of SQL script solution4.sql. The report must have no errors and the report must list all SQL statements processed. Make absolutely sure before submission, that a file solution4.lst contains the correct contents.
A report that contains no listing of executed SQL statements scores no marks !
A report that contains any kind of processing errors scores no marks !
Submission of a file with a different name and/or different extension and/or different type scores no marks !

Task 5 (2 marks)
Connect to sample TPC-H benchmark database as TPCHR user. A conceptual schema of the database is included in a file tpchr.pdf available in TPCHR folder. Implement the queries listed below as SELECT statements over TPC-HR benchmark database.
(1) Find total available quantities (PS_AVAILQTY) summarized for all suppliers located in ETHIOPIA and summarized by supplier key and supplier name, and supplier country (PS_SUPPKEY, S_NAME, C_NAME).
(2) Find total supply costs (PS_SUPPLYCOST) summarized at nation level for suppliers located in EUROPE or in ASIA and separately summarized at region level for
suppliers located in EUROPE or in ASIA. List a nation name (N_NAME),
region name (R_NAME), and total supply costs (PS_SUPPLYCOST).
(3) Find an average part retail price (P_RETAILPRICE) for each nation (N_NAME) of suppliers and separately for each region (R_NAME) of suppliers.
(4) Find the total value of orders (O_TOTALPRICE) submitted in a period of time between 1 January 1991 and 31 December 1999 and summarized by year (O_ORDERDATE) and by year and month number (O_ORDERDATE) and the total value of all orders. The results should be sorted in ascending order by year and for the same year in descending order by month number.
(5) Find the total values of orders (O_TOTALPRICE) summarized by the regions (R_NAME). If no orders have been submitted from a region then its name must be listed with 0 (zero).
For each one of the queries listed above find one or more indexes that significantly improve performance of each query.
To estimate performance of query processing before and after indexing use EXPLAIN PLAN statement to display the query processing plans and the cost estimations. It is explained in Cookbook, Recipe 8.1, Step 3 - How to find an execution plan of SQL statement ?- how to use EXPLAIN PLAN statement and how to list a query processing plan together with the estimations of the total number of bytes read and estimated query processing time.
For each query a testing procedure is the following.
(1) Take implementation of query (1) and execute EXPLAIN PLAN statement to find a query processing plan for query (1), estimations of the total number of bytes read and estimated query processing time.
(2) Use CREATE INDEX statement to create the indexes that suppose to speed up processing of query (1). You can implement as many indexes you like. However, please remember, that it supposed to be the smallest collection of indexes that speed up the processing of the query in the best way.
(3) Take implementation of query (1) and execute EXPLAIN PLAN statement to find a query processing plan for query (1), estimations of the total number of bytes read and estimated query processing time after the indexes have been created. Note, that ALL indexes created by you must be used for processing of the query.
(4) Drop the indexes created in Step 2.
Repeat the Steps 1, 2, 3, and 4 for the implementations of all 5 queries listed above. The indexes must be created independently for each query.
Save all SQL statements that estimate performance of queries listed above without and with the indexes (Steps 1, 2, 3, and 4 above repeated for all queries) in a file solution5.sql. When ready execute a script file solution5.sql and save a report from the execution in a file solution5.lst. It is explained in Cookbook, Recipe 2.4, Step 9 -How to create and to save a report ?- how to save a report from processing of SQL script in a text file.
Note, that all SQL statements processed must be included in the report. To achieve that put the following SQL*Plus commands at the beginning of your SQL script solution5.sql:
SET ECHO ON
SET FEEBACK ON
SET LINESIZE 100
SET PAGESIZE 200
Note, that to get the marks for indexing that improves performance of query processing your implementation of the queries must be consistent with the specification above and it must be correct.
Deliverables
Submit a file solution5.lst with a report from processing of SQL script solution5.sql. The report must have no errors and the report must list all SQL statements processed. Make absolutely sure before submission, that a file solution5.lst contains the correct contents.
A report that contains no listing of executed SQL statements scores no marks !
A report that contains any kind of processing errors scores no marks !
Submission of a file with a different name and/or different extension and/or different type scores no marks !