DBM 449 Lab 6 SQL Analytical Extensions and Materialized Views

Description

For the lab this week we are going to look at how the ROLLUP and CUBE extensions available in SQL can be used to create query result sets that have more than one dimension to them. Both of these extensions are used in conjunction with the GROUP BY clause and allow for a much more broad look at the data.

The first thing you will do for this lab is download the lab6_create.sql file and run the file in your database instance. This file will log into the DBM449_USER and then create and populate a set of tables that will be used for this lab.  Instructions for this are outlined in Step 1.

To record your work for this lab use the LAB6_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Dropbox for Week 6.

LAB STEPS

STEP 1: Setting up Your Instance

For this lab you will be using a different user and set of tables than you have used so far for other labs. To set up your instance you will need to do the following.

1. Download the lab6_create.sql file associated with the link to either the C drive on your computer or the F drive in your Citrix account.
2. Open up the file and edit the login information at the top for the new user that is being created. You will need to replace the @ORACLE piece with the specifics for your instance name. DO NOT include AS SYSDBA after the name of your instance for this login.
3. Now log into your instance as the SYS user. Run the script. The script is too long to copy/paste it into your SQL*Plus session so you should run the script using the @ sign from the SQL> prompt.
4. Once the script has finished running then issue a SELECT * FROM TAB; sql statement. The result set will have tables from other labs as well but you want to make sure that you see the following tables listed.

STEP 2: Using the ROLLUP Extension 

In this section of the lab you are going to create a sales report that will show a supplier code, product code and the total sales for each product based on unit price times a quantity. More importantly the column that shows the total sales will also show a grand total for the supplier as well as a grand total over all (this will be the last row of data shown). To do this you will use the ROLLUP extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.

SUPPLIER CODE PRODUCT    TOTAL SALES
————- ———- ———–

For this report you are going to use the SALES, PRODUCT and SUPPLIER tables. You should be able to write your query using NATURAL JOIN but if you feel more comfortable using a traditional JOIN method that will work just as well. When finished you should have a total of 16 rows in your report and the grand total amount should show 2810.74.

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

STEP 3: Using the CUBE Extension

In this section of the lab you are going to create a sales report that will show a month code, product code and the total sales for each product based on unit price times a quantity. In this report the column that shows the total sales will also show a subtotal for each month (in this case representing a quarter) . Following the monthly totals for each product and the subtotal by month then the report will list a total for each product sold during the period with a grand total for all sales during the period (this will be the last row of data shown). To do this you will use the CUBE extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.

     MONTH PRODUCT    TOTAL SALES
———- ———- ———–

For this report you are going to use the SALES, PRODUCT and TIME tables. You should be able to write your query using NATURAL JOIN but if you feel more comfortable using a traditional JOIN method that will work just as well. When finished you should have a grand total amount of 2810.74 (same total as in the step 2).

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

STEP 4: Materialized Views and View Logs

Materialized views, sometimes referred to as snapshots are a very important aspect of dealing with data when doing data mining or working with a data warehouse. Unlike regular views, a materialized view does not always automatically react to changes made in the base tables of the view. To help keep track of changes made to the base tables you must create what is call a Materialized View Log on each base table that will be used in the view. In this step of the lab we will do this.

For the Materialized View we are going to create we are going to use the TIME and the SALES tables. Before we can create the view you will need to create a Materialized View Log on each of these two tables that will keep track of the ROWID and Sequence and include new values that have been added to the base table.

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

STEP 5: Creating and Using the Materialized View

Now that we have our logs created we can progress on to the view itself. For this part of the lab you are going to create a Materialized View, demonstrate that the view works, insert a row of data into one of the base tables and then update the view. Finally, you will show that the new data is in the view. The following steps will help move you through this process.

1. First, write the SQL CREATE statement that will create a Materialized View based on the following:
• Name the view SALESBYMONTH.
• Include clauses that will build the view immediately, completely refresh the view, and enable a query rewrite.
• For the columns of the view you want to show the YEAR, MONTH, PRODUCT CODE, a TOTAL SALES UNITS, and a TOTAL SALES.
• You will want to group the columns by year, month and product code respectively.
2. Execute your script to create the view and then issue a SELECT * FROM SALESBYMONTH.

The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.

                                  YEAR      MONTH PRODUCT CO UNITS SOLD SALES TOTAL
                              ——– ———- ———- ———- ———–

Now we are going to add some data and update the view. Because we have several derived columns in out view we will have to force the update as Oracle will not automatically update a view with this configuration.

1. To begin with, insert the following data into the SALES table – (207, 110016, ‘SM-18277’,1,8.95).
2. Now we are going to use a subprogram within the Oracle built in package DBMS_MVIEW. The REFRESH subprogram within this package will update our view so that we can see the new data.
3. Write an SQL EXECUTE statement that will use the REFRESH procedure in the DBMS_MVIEW package (HINT: packagename.subprogram). The REFRESH subprogram accepts two parameters; the name of the materialized view to refresh, and either a ‘c’, ‘f’, or ‘?’. For the purposes of the lab use the ‘c’. (you can refer back to pages 654-659 of the DBA Handbook readings for week 3).
4. Execute your statement to update the view and then query the view once again.

You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.

Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.

Deliverables

Submit your completed Lab 6 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.