Database Tutorial

Course Tutorial Site

Site Admin

Lab #11: Oracle

with 33 comments

Lab #11: Oracle Assignment

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

  1. [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 the USING clause of the MERGE statement as the query, resolve which columns you use in an UPDATE statement, and resolve which columns you use in an INSERT 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.

  1. [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 the RENTAL_ITEM table. You need to put it inside the USING clause of the MERGE statement as the query, resolve which columns you use in an UPDATE statement, and resolve which columns you use in an INSERT 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.

  1. [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 the TRANSACTION table. You need to put it inside the USING clause of the MERGE statement as the query, resolve which columns you use in an UPDATE statement, and resolve which columns you use in an INSERT 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.
  1. [12 points] After running the first three steps, you need to put the three MERGE statement into a single upload_transaction procedure. Then, you need to run the upload_transaction procedure, run a validation query, run the upload_transaction procedure a second time, and run a validation query again.
  1. [4 points of 12 points] After running the first three steps, you need to put the three MERGE statement into a single upload_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;
/
  1. [2 points of 12 points] You run the upload_transaction procedure with the following syntax:
1

EXECUTE upload_transaction;
  1. [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.
  1. [2 points of 12 points] You re-run the upload_transaction procedure with the following syntax:
1

EXECUTE upload_transaction;
  1. [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.

  1. [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

Written by michaelmclaughlin

June 29th, 2014 at 4:24 pm

Posted in

33 Responses to 'Lab #11: Oracle'

Subscribe to comments with RSS or TrackBack to 'Lab #11: Oracle'.

  1. 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

  2. Where is step 5?

    Kent

    1 Jul 14 at 11:56 pm

  3. Row count for step 2 says 11,520. Should it not be 11,532?

    Kent

    1 Jul 14 at 11:58 pm

  4. Kent, Good catch. I’ve updated it.

    michaelmclaughlin

    2 Jul 14 at 12:28 am

  5. Item #6 should have been #5.

    michaelmclaughlin

    2 Jul 14 at 12:31 am

  6. On #5 you are asking us to query for 2010, but the validation shows 2009.

    Cordon Davies

    9 Jul 14 at 1:29 pm

  7. 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

  8. 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

  9. 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

  10. I think the “115532” is supposed to be “115533” for merge #2 … and cross referencing lab 10 … and from trying to do lab 11 myself

    Benjamin Walker

    28 Nov 14 at 11:12 pm

  11. I meant “11533” not “115533”, this occurs twice btw …

    another problem I think …

    UPDATE SET target.column_name = source.column_name
AND        target.column_name = source.column_name

    is supposed to be

    UPDATE SET target.column_name = source.column_name
,        target.column_name = source.column_name

    Benjamin Walker

    28 Nov 14 at 11:51 pm

  12. Benjamin, Yes, you’re right. I’ve made the change.

    michaelmclaughlin

    29 Nov 14 at 11:02 am

  13. 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

  14. Justin, You’re right. It should always be a comma.

    michaelmclaughlin

    29 Nov 14 at 11:06 am

  15. The result set for part one will print out a comma at the thousandths place.

    Sam Graham

    1 Dec 14 at 4:49 pm

  16. PART 3

    You can use the following MERGE statement shell for the RENTAL_ITEM table:

    should be for transaction

    RJ

    2 Dec 14 at 7:35 pm

  17. 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

  18. Sam, good catch. It’s fixed.

    michaelmclaughlin

    21 Dec 14 at 2:36 pm

  19. RJ, yes, it is corrected.

    michaelmclaughlin

    21 Dec 14 at 2:47 pm

  20. 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

  21. The template for the apply_oracle_lab11.sql file is calling lab 9 from the wrong folder. It currently reads:

    @../lab11/apply_oracle_lab09.SQL

    when it should be:

    @../lab9/apply_oracle_lab9.SQL

    Sam Barney

    17 Mar 15 at 5:05 pm

  22. This seems inconsistent with the output we are to compare against holding data from 2009. Is it 2009 or 2010?

    1. [5 points] Create a query that prints the following types of data for the year 2010.

    Cole

    17 Mar 15 at 8:48 pm

  23. 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

  24. Chis, You’re right!

    michaelmclaughlin

    22 Mar 15 at 7:40 pm

  25. Cole, I’ve updated it from 2010 to 2009.

    michaelmclaughlin

    22 Mar 15 at 7:43 pm

  26. Sam, Great catch! I’ve made the change.

    michaelmclaughlin

    22 Mar 15 at 7:46 pm

  27. 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

  28. Jimmy, Good catch.

    michaelmclaughlin

    17 Sep 15 at 7:37 pm

  29. I recieved an “unable to get a stable set of rows in the source tables” error

    it was fixed after changing

    @../lab11/apply_oracle_lab9.sql

    to

    @../lab9/apply_oracle_lab9.sql

    Is this correct?

    Chad Austin

    15 Mar 16 at 8:26 pm

  30. Chad, Yes. The change has been made in the page.

    michaelmclaughlin

    29 Apr 16 at 2:04 am

  31. “you’re three Potter users” should contain ‘your’ not ‘you’re’.

    Cam

    24 Nov 17 at 6:12 pm

  32. Yes, changed.

    michaelmclaughlin

    23 Dec 17 at 2:55 pm

  33. In steps 4c and 4e, shouldn’t Rental Item Count be 11,533 (not 11,532)?

    Tyler

    3 Jul 18 at 7:31 pm

Leave a Reply