Lab #11: Oracle
You begin these steps after running the create_oracle_store.sql
, the seed_oracle_store.sql
, apply_oracle_lab5.sql
, apply_oracle_lab6.sql
, apply_oracle_lab7.sql
, apply_oracle_lab8.sql
, and apply_oracle_lab9.sql
scripts. You should exclude running apply_oracle_lab10.sql
scripts, which is designed to help you develop and test the MERGE
statements for this lab. You should create the apply_oracle_lab11.sql
script as follows:
-- This calls Lab #9, Lab #9 calls Lab #8, Lab #8 calls Lab #7, Lab #7 calls Lab #6, Lab #6 calls Lab #5, and Lab #5 calls both the Creation and Seed script files. @../lab9/apply_oracle_lab9.sql SPOOL apply_oracle_lab11.txt ... insert code here ... SPOOL OFF |
- [6 points] This step requires that you use the query from Lab #10 that you used to insert records into the
RENTAL
table. You need to put it inside theUSING
clause of theMERGE
statement as the query, resolve which columns you use in anUPDATE
statement, and resolve which columns you use in anINSERT
statement.
You can use the following MERGE
statement shell for the RENTAL
table:
1 2 3 4 5 6 7 8 9 10 11 12 | MERGE INTO rental target USING ( ... query_statement ... ) source ON (target.column_name = source.column_name) WHEN MATCHED THEN UPDATE SET target.column_name = source.column_name , target.column_name = source.column_name WHEN NOT MATCHED THEN INSERT VALUES ( rental_s1.nextval , source.column_name ... , source.column_name); |
Use the following query after you run the MERGE
statement:
1 2 | SELECT TO_CHAR(COUNT(*),'99,999') AS "Rental after merge" FROM rental; |
It should return the following result set:
Rental after merge ------------------ 4,689 1 row selected. |
- [6 points] This step requires that the preceding
MERGE
statement ran successfully and that you use the query from Lab #10 that you used to insert records into theRENTAL_ITEM
table. You need to put it inside theUSING
clause of theMERGE
statement as the query, resolve which columns you use in anUPDATE
statement, and resolve which columns you use in anINSERT
statement.
You can use the following MERGE
statement shell for the RENTAL_ITEM
table:
1 2 3 4 5 6 7 8 9 10 11 12 | MERGE INTO rental_item target USING ( ... query_statement ... ) source ON (target.column_name = source.column_name) WHEN MATCHED THEN UPDATE SET target.column_name = source.column_name , target.column_name = source.column_name WHEN NOT MATCHED THEN INSERT VALUES ( rental_item_s1.nextval , source.column_name ... , source.column_name); |
Use the following query after you run the MERGE
statement:
1 2 | SELECT TO_CHAR(COUNT(*),'99,999') AS "Rental Item after merge" FROM rental_item; |
It should return the following result set:
Rental Item after merge ----------------------- 11,533 1 row selected. |
- [6 points] This step requires that the preceding
MERGE
statement ran successfully and that you use the query from Lab #10 that you used to insert records into theTRANSACTION
table. You need to put it inside theUSING
clause of theMERGE
statement as the query, resolve which columns you use in anUPDATE
statement, and resolve which columns you use in anINSERT
statement.
You can use the following MERGE
statement shell for the TRANSACTION
table:
1 2 3 4 5 6 7 8 9 10 11 12 | MERGE INTO transaction target USING ( ... query_statement ... ) source ON (target.column_name = source.column_name) WHEN MATCHED THEN UPDATE SET target.column_name = source.column_name , target.column_name = source.column_name WHEN NOT MATCHED THEN INSERT VALUES ( transaction_s1.nextval , source.column_name ... , source.column_name); |
Use the following query after you run the MERGE
statement:
1 2 | SELECT TO_CHAR(COUNT(*),'99,999') AS "Transaction after merge" FROM transaction; |
It should return the following result set:
Transaction after merge ----------------------- 4,681 1 row selected. |
- [12 points] After running the first three steps, you need to put the three
MERGE
statement into a singleupload_transaction
procedure. Then, you need to run theupload_transaction
procedure, run a validation query, run theupload_transaction
procedure a second time, and run a validation query again.
- [4 points of 12 points] After running the first three steps, you need to put the three
MERGE
statement into a singleupload_transaction
procedure.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- Create a procedure to wrap the transaction. CREATE OR REPLACE PROCEDURE upload_transaction IS BEGIN -- Set save point for an all or nothing transaction. SAVEPOINT starting_point; -- Merge into RENTAL table. MERGE INTO rental ... -- Merge into RENTAL_ITEM table. MERGE INTO rental_item ... -- Merge into TRANSACTION table. MERGE INTO transaction ... -- Save the changes. COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO starting_point; RETURN; END; / |
- [2 points of 12 points] You run the
upload_transaction
procedure with the following syntax:
1 | EXECUTE upload_transaction; |
- [2 points of 12 points] You should use the following to query the results from procedure.
1 2 3 4 5 6 7 8 9 10 | COLUMN rental_count FORMAT 99,999 HEADING "Rental|Count" COLUMN rental_item_count FORMAT 99,999 HEADING "Rental|Item|Count" COLUMN transaction_count FORMAT 99,999 HEADING "Transaction|Count" SELECT il1.rental_count , il2.rental_item_count , il3.transaction_count FROM (SELECT COUNT(*) AS rental_count FROM rental) il1 CROSS JOIN (SELECT COUNT(*) AS rental_item_count FROM rental_item) il2 CROSS JOIN (SELECT COUNT(*) AS transaction_count FROM TRANSACTION) il3; |
It should return the following result set:
Rental Rental Item Transaction Count Count Count ------- ------- ----------- 4,689 11,532 4,681 1 row selected. |
- [2 points of 12 points] You re-run the
upload_transaction
procedure with the following syntax:
1 | EXECUTE upload_transaction; |
- [2 points of 12 points] You should reuse the following to query the results from procedure.
1 2 3 4 5 6 7 8 9 10 | COLUMN rental_count FORMAT 99,999 HEADING "Rental|Count" COLUMN rental_item_count FORMAT 99,999 HEADING "Rental|Item|Count" COLUMN transaction_count FORMAT 99,999 HEADING "Transaction|Count" SELECT il1.rental_count , il2.rental_item_count , il3.transaction_count FROM (SELECT COUNT(*) AS rental_count FROM rental) il1 CROSS JOIN (SELECT COUNT(*) AS rental_item_count FROM rental_item) il2 CROSS JOIN (SELECT COUNT(*) AS transaction_count FROM TRANSACTION) il3; |
It should return the following result set:
Rental Rental Item Transaction Count Count Count ------- ------- ----------- 4,689 11,532 4,681 1 row selected. |
- [5 points] Create a query that prints the following types of data for the year 2009, which is only possible when you adjust for the included 6% interest. You need to make that adjustment inside the third merge statement’s SELECT clause.
Implementation Note: This is a preliminary report that lets the business user see what their aggregate earnings would be with a 10% and 20% increase, as well as see the incremental increases by themselves.
Business Rule: This join can be made against only the TRANSACTION
table.
System Logic: Implement consistent with the business rule and available resources on the respective platforms. Performing the GROUP BY
in an inline view with a non-displayed sorting key that maps to the numeric value of the month is probably the easiest approach.
You’ll also may benefit from knowing about these functions, as you solve this part of the lab. They work in both Oracle and MySQL. The EXTRACT
function returns a number between 1 and 12 when you extract a MONTH
, and a four-digit year when you extract a YEAR
. Copy the code into a terminal session or development tool and test it.
SELECT EXTRACT(MONTH FROM TO_DATE('02-FEB-2009')) , EXTRACT(YEAR FROM TO_DATE('02-FEB-2009')) FROM dual; |
The TO_CHAR
function lets you apply formatting masks in Oracle, like the following that converts numbers to dollars with two values to the right of the decimal point. Again, you can copy and test how this works.
SELECT TO_CHAR(9999,'$9,999,999.00') AS "Formatted" FROM dual; |
The desired layout is noted below in the color formatted table.
Query Elements | |||||
---|---|---|---|---|---|
Month Year | Base Revenue |
10 Plus Revenue |
20 Plus Revenue |
10 Plus Difference |
20 Plus Difference |
JAN 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
FEB 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
MAR 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
APR 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
MAY 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
JUN 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
JUL 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
AUG 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
SEP 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
OCT 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
NOV 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
DEC 2009 | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn | n,nnn.nn |
The next two sections show you the expected outcome. You should note that these results work from only the seeded items. However, they do work from customers seeded and your three Potter users from your Lab #6.
Oracle Validation
The actual data from the import source should show the following when you run it in an Oracle database:
MONTH BASE_REVENUE 10_PLUS 20_PLUS 10_PLUS_LESS_B 20_PLUS_LESS_B ---------- -------------- -------------- -------------- -------------- -------------- JAN-2009 $2,520.00 $2,772.00 $3,024.00 $252.00 $504.00 FEB-2009 $4,029.00 $4,431.90 $4,834.80 $402.90 $805.80 MAR-2009 $5,067.00 $5,573.70 $6,080.40 $506.70 $1,013.40 APR-2009 $4,653.00 $5,118.30 $5,583.60 $465.30 $930.60 MAY-2009 $2,091.00 $2,300.10 $2,509.20 $209.10 $418.20 JUN-2009 $1,140.00 $1,254.00 $1,368.00 $114.00 $228.00 JUL-2009 $2,268.00 $2,494.80 $2,721.60 $226.80 $453.60 AUG-2009 $2,115.00 $2,326.50 $2,538.00 $211.50 $423.00 SEP-2009 $2,073.00 $2,280.30 $2,487.60 $207.30 $414.60 OCT-2009 $3,090.00 $3,399.00 $3,708.00 $309.00 $618.00 NOV-2009 $2,949.00 $3,243.90 $3,538.80 $294.90 $589.80 DEC-2009 $2,208.00 $2,428.80 $2,649.60 $220.80 $441.60 |
should rental item after merge have 11,532 items after the merge? because there are 12 rows in the rental item table before the merge that places 11520 rows into it.
Davies
1 Jul 14 at 8:34 pm
Where is step 5?
Kent
1 Jul 14 at 11:56 pm
Row count for step 2 says 11,520. Should it not be 11,532?
Kent
1 Jul 14 at 11:58 pm
Kent, Good catch. I’ve updated it.
michaelmclaughlin
2 Jul 14 at 12:28 am
Item #6 should have been #5.
michaelmclaughlin
2 Jul 14 at 12:31 am
On #5 you are asking us to query for 2010, but the validation shows 2009.
Cordon Davies
9 Jul 14 at 1:29 pm
Brother McLaughlin,
Are the numbers as shown in the table under “Oracle Validation” correct for our lab? I’m getting values nearly double.
Thanks.
Abel Goodwin
17 Jul 14 at 7:58 pm
You can ignore my previous comment. I had a mistake in my merge statement which was causing duplicate insertion.
Abel Goodwin
17 Jul 14 at 8:31 pm
The
AND
in the merge statements is not working for me. I was able to get it working by replacing with a comma, however. Is this okay?Justin Jaynes
25 Nov 14 at 1:41 pm
I think the “
115532
” is supposed to be “115533
” for merge #2 … and cross referencing lab 10 … and from trying to do lab 11 myselfBenjamin Walker
28 Nov 14 at 11:12 pm
I meant “11533” not “115533”, this occurs twice btw …
another problem I think …
is supposed to be
Benjamin Walker
28 Nov 14 at 11:51 pm
Benjamin, Yes, you’re right. I’ve made the change.
michaelmclaughlin
29 Nov 14 at 11:02 am
Benjamin, Yes, you’re right. We added an insert in an earlier lab and I forgot to update this page.
michaelmclaughlin
29 Nov 14 at 11:05 am
Justin, You’re right. It should always be a comma.
michaelmclaughlin
29 Nov 14 at 11:06 am
The result set for part one will print out a comma at the thousandths place.
Sam Graham
1 Dec 14 at 4:49 pm
PART 3
You can use the following
MERGE
statement shell for theRENTAL_ITEM
table:should be for transaction
RJ
2 Dec 14 at 7:35 pm
If we are NOT calling lab 10, then shouldn’t we create the unique indexes in lab 11 as well?
RJ
2 Dec 14 at 9:45 pm
Sam, good catch. It’s fixed.
michaelmclaughlin
21 Dec 14 at 2:36 pm
RJ, yes, it is corrected.
michaelmclaughlin
21 Dec 14 at 2:47 pm
RJ, you’re right the indexes should be migrated but without calling Lab 10. I’ve added two sentences to Lab 11 with the instructions to migrate the indexes from Lab 10 to Lab 11.
michaelmclaughlin
21 Dec 14 at 3:21 pm
The template for the apply_oracle_lab11.sql file is calling lab 9 from the wrong folder. It currently reads:
when it should be:
Sam Barney
17 Mar 15 at 5:05 pm
This seems inconsistent with the output we are to compare against holding data from 2009. Is it 2009 or 2010?
Cole
17 Mar 15 at 8:48 pm
In step 5:
“Create a query that prints the following types of data for the year 2010.”
Should be:
“Create a query that prints the following types of data for the year 2009.”
Chis
20 Mar 15 at 3:55 pm
Chis, You’re right!
michaelmclaughlin
22 Mar 15 at 7:40 pm
Cole, I’ve updated it from 2010 to 2009.
michaelmclaughlin
22 Mar 15 at 7:43 pm
Sam, Great catch! I’ve made the change.
michaelmclaughlin
22 Mar 15 at 7:46 pm
Just noticed that in both step two and step three, there is an AND in the UPDATE SET statement instead of a “,”. The merge statement only worked for me when I changed the AND to a comma.
JImmy`
1 Jul 15 at 8:59 am
Jimmy, Good catch.
michaelmclaughlin
17 Sep 15 at 7:37 pm
I recieved an “unable to get a stable set of rows in the source tables” error
it was fixed after changing
to
Is this correct?
Chad Austin
15 Mar 16 at 8:26 pm
Chad, Yes. The change has been made in the page.
michaelmclaughlin
29 Apr 16 at 2:04 am
“you’re three Potter users” should contain ‘your’ not ‘you’re’.
Cam
24 Nov 17 at 6:12 pm
Yes, changed.
michaelmclaughlin
23 Dec 17 at 2:55 pm
In steps 4c and 4e, shouldn’t Rental Item Count be 11,533 (not 11,532)?
Tyler
3 Jul 18 at 7:31 pm