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.
 | L A B S T E P S |  |
| 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.
- 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.I have pasted lab6_create.sql at the end of this post. - 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.
- 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.
- 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.
| TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- SUPPLIER TABLE PRODUCT TABLE DISTRICT CUSTOMER TABLE TIME TABLE SALES TABLE |
| 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.
- 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.
- Execute your script to create the view and then issue a SELECT * FROM SALESBYMONTH.
YEAR MONTH PRODUCT CO UNITS SOLD SALES TOTAL
-------- ---------- ---------- ---------- -----------
- To begin with, insert the following data into the SALES table - (207, 110016, 'SM-18277',1,8.95).
- 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.
- 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).
- Execute your statement to update the view and then query the view once again.
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.
/* DATA WAREHOISE SQL FOR LAB6 */
/* Script file for ORACLE 10G DBMS */
/* This script file connects to the DBM49_USER user */
/* and then creates the following tables: */
/* SUPPLIER, PRODUCT, CUSTOMER, DISTRICT, TIME, SALES */
/* and loads the default data rows */
CONN DBM449_USER/DEVRY@DB####.WORLD
DROP TABLE SALES;
DROP TABLE CUSTOMER;
DROP TABLE DISTRICT;
DROP TABLE PRODUCT;
DROP TABLE SUPPLIER;
DROP TABLE TIME;
CREATE TABLE SUPPLIER (
SUP_CODE INTEGER PRIMARY KEY,
SUP_NAME VARCHAR(35),
SUP_AREACODE CHAR(3),
SUP_STATE CHAR(2));
CREATE TABLE PRODUCT (
PROD_CODE VARCHAR2(10) PRIMARY KEY,
PROD_DESCRIPT VARCHAR2(35),
PROD_CATEGORY VARCHAR(5),
SUP_CODE NUMBER REFERENCES SUPPLIER);
CREATE TABLE DISTRICT (
DIST_ID INTEGER PRIMARY KEY,
DIST_NAME VARCHAR(10));
CREATE TABLE CUSTOMER (
CUST_CODE NUMBER PRIMARY KEY,
CUST_LNAME VARCHAR(15),
CUST_FNAME VARCHAR(15),
CUST_INITIAL CHAR(1),
CUST_STATE CHAR(2),
DIST_ID NUMBER REFERENCES DISTRICT);
CREATE TABLE TIME (
TIME_ID INTEGER PRIMARY KEY,
TIME_YEAR INTEGER,
TIME_MONTH INTEGER,
TIME_DAY INTEGER,
TIME_QTR INTEGER);
CREATE TABLE SALES (
TIME_ID NUMBER,
CUST_CODE NUMBER,
PROD_CODE VARCHAR(10),
SALE_UNITS NUMBER,
SALE_PRICE NUMBER,
PRIMARY KEY (TIME_ID, CUST_CODE, PROD_CODE));
/* Loading data rows */
SET ESCAPE ON;
INSERT INTO SUPPLIER VALUES(31225,'Bryson, Inc.' ,'615','TN');
INSERT INTO SUPPLIER VALUES(31226,'SuperLoo, Inc.' ,'904','FL');
INSERT INTO SUPPLIER VALUES(31231,'DE Supply' ,'615','TN');
INSERT INTO SUPPLIER VALUES(31344,'Gomez Bros.' ,'615','KY');
INSERT INTO SUPPLIER VALUES(32567,'Dome Supply' ,'901','GA');
INSERT INTO SUPPLIER VALUES(33119,'Randsets Ltd.' ,'901','GA');
INSERT INTO SUPPLIER VALUES(44004,'Brackman Bros.' ,'615','TN');
INSERT INTO SUPPLIER VALUES(44288,'ORDVA, Inc.' ,'615','TN');
INSERT INTO SUPPLIER VALUES(55443,'BK, Inc.' ,'904','FL');
INSERT INTO SUPPLIER VALUES(55501,'Damal Supplies' ,'615','TN');
INSERT INTO SUPPLIER VALUES(55595,'Rubicon Systems' ,'904','FL');
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle' ,'CAT1',55595);
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade' ,'CAT1',31344);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade' ,'CAT1',31344);
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50' ,'CAT2',33119);
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50' ,'CAT2',33119);
INSERT INTO PRODUCT VALUES('2232/QTY','BD jigsaw, 12-in. blade' ,'CAT2',44288);
INSERT INTO PRODUCT VALUES('2232/QWE','BD jigsaw, 8-in. blade' ,'CAT3',44288);
INSERT INTO PRODUCT VALUES('2238/QPD','BD cordless drill, 1/2-in.' ,'CAT3',55595);
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer' ,'CAT4',31225);
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.' ,'CAT4',31225);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine' ,'CAT1',31344);
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.' ,'CAT2',44288);
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft' ,'CAT3',31225);
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25' ,'CAT4',31225);
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50' ,'CAT2',31231);
INSERT INTO PRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','CAT3',55595);
INSERT INTO DISTRICT VALUES(1,'NE');
INSERT INTO DISTRICT VALUES(2,'NW');
INSERT INTO DISTRICT VALUES(3,'SE');
INSERT INTO DISTRICT VALUES(4,'SW');
INSERT INTO CUSTOMER VALUES(110010,'Ramas' ,'Alfred','A' ,'TN',3);
INSERT INTO CUSTOMER VALUES(110011,'Dunne' ,'Leona' ,'K' ,'GA',3);
INSERT INTO CUSTOMER VALUES(110012,'Smith' ,'Kathy' ,'W' ,'NY',1);
INSERT INTO CUSTOMER VALUES(110013,'Olowski' ,'Paul' ,'F' ,'NJ',1);
INSERT INTO CUSTOMER VALUES(110014,'Orlando' ,'Myron' ,NULL,'CO',2);
INSERT INTO CUSTOMER VALUES(110015,'O''Brian','Amy' ,'B' ,'TN',3);
INSERT INTO CUSTOMER VALUES(110016,'Brown' ,'James' ,'G' ,'GA',3);
INSERT INTO CUSTOMER VALUES(110017,'Williams','George',NULL,'CA',4);
INSERT INTO CUSTOMER VALUES(110018,'Farriss' ,'Anne' ,'G' ,'CA',4);
INSERT INTO CUSTOMER VALUES(110019,'Smith' ,'Olette','K' ,'CO',2);
INSERT INTO TIME VALUES(201,2009,09,29,3);
INSERT INTO TIME VALUES(202,2009,09,30,3);
INSERT INTO TIME VALUES(203,2009,09,31,3);
INSERT INTO TIME VALUES(206,2009,10,03,4);
INSERT INTO TIME VALUES(207,2009,10,04,4);
INSERT INTO SALES VALUES(201,110014,'13-Q2/P2',1,14.99);
INSERT INTO SALES VALUES(201,110014,'23109-HB',1,11.95);
INSERT INTO SALES VALUES(201,110015,'54778-2T',2,5.99);
INSERT INTO SALES VALUES(201,110015,'2238/QPD',1,38.95);
INSERT INTO SALES VALUES(202,110016,'1546-QQ2',1,311.95);
INSERT INTO SALES VALUES(202,110016,'13-Q2/P2',5,15.99);
INSERT INTO SALES VALUES(202,110017,'54778-2T',3,5.99);
INSERT INTO SALES VALUES(202,110017,'23109-HB',2,11.95);
INSERT INTO SALES VALUES(202,110018,'PVC23DRT',12,5.87);
INSERT INTO SALES VALUES(203,110012,'SM-18277',3,8.95);
INSERT INTO SALES VALUES(203,110014,'2232/QTY',1,109.92);
INSERT INTO SALES VALUES(203,110015,'23109-HB',1,11.95);
INSERT INTO SALES VALUES(203,110015,'89-WRE-Q',1,258.95);
INSERT INTO SALES VALUES(203,110016,'13-Q2/P2',2,15.99);
INSERT INTO SALES VALUES(203,110016,'54778-2T',1,5.99);
INSERT INTO SALES VALUES(203,110016,'PVC23DRT',5,5.87);
INSERT INTO SALES VALUES(203,110017,'WR3/TT3',3,111.95);
INSERT INTO SALES VALUES(203,110017,'23109-HB',1,11.95);
INSERT INTO SALES VALUES(203,110017,'13-Q2/P2',1,15.99);
INSERT INTO SALES VALUES(203,110018,'23109-HB',1,11.95);
INSERT INTO SALES VALUES(203,110018,'54778-2T',2,5.99);
INSERT INTO SALES VALUES(203,110018,'2238/QPD',1,38.95);
INSERT INTO SALES VALUES(203,110019,'1546-QQ2',1,311.95);
INSERT INTO SALES VALUES(206,110010,'13-Q2/P2',5,15.99);
INSERT INTO SALES VALUES(206,110010,'54778-2T',3,5.99);
INSERT INTO SALES VALUES(206,110010,'23109-HB',2,11.95);
INSERT INTO SALES VALUES(206,110010,'PVC23DRT',12,5.87);
INSERT INTO SALES VALUES(206,110011,'SM-18277',3,8.95);
INSERT INTO SALES VALUES(206,110011,'2232/QTY',1,109.92);
INSERT INTO SALES VALUES(206,110012,'23109-HB',1,11.95);
INSERT INTO SALES VALUES(206,110012,'89-WRE-Q',1,258.95);
INSERT INTO SALES VALUES(207,110013,'13-Q2/P2',2,15.99);
INSERT INTO SALES VALUES(207,110013,'54778-2T',1,5.99);
INSERT INTO SALES VALUES(207,110013,'PVC23DRT',5,5.87);
INSERT INTO SALES VALUES(207,110014,'WR3/TT3',3,111.95);
INSERT INTO SALES VALUES(207,110015,'23109-HB',1,11.95);
COMMIT;
SET ESCAPE OFF;
1 comment:
If you need solution of above problem contact at arjuiut at gmail.com
Post a Comment