Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #11: MERGE Statement

Learn how to write MERGE statements with outer-join queries. Typical outer join queries in a MERGE statement return a surrogate key for a target table when the rows intersect with the transaction table; and the MERGE statement updates these rows. The outer join queries return a null value when they don’t intersect with the target table; and the MERGE statement inserts these rows as new rows. Learn how to query aggregated result sets from the transaction table.

Lab Instructions

This lab has five unit testing steps placed into one test script.

[46 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

You begin these steps after running the following script:

  • The lab9/apply_oracle_lab9.sql script

You put the following script in your apply_oracle_lab11.sql script, which should be in the following directory:

/home/student/Data/cit225/lab11

Sample script →

This expands to show you how to structure your apply_oracle_lab11.sql script.

-- ------------------------------------------------------------------
-- Call the prior lab.
-- ------------------------------------------------------------------
 
@@/home/student/Data/cit225/oracle/lab9/apply_oracle_lab9.sql
 
-- Open log file.  
SPOOL apply_oracle_lab8.txt
 
-- --------------------------------------------------------
--  Step #1
--  -------
--  Using the query from Lab 10, Step 1, insert the query
--  in the MERGE statement to the RENTAL table.
-- --------------------------------------------------------
 
-- Insert step #1 statements here.
 
-- --------------------------------------------------------
--  Step #2
--  -------
--  Using the query from Lab 10, Step 2, insert the query
--  in the MERGE statement to the RENTAL_ITEM table.
-- --------------------------------------------------------
 
-- Insert step #2 statements here.
 
-- --------------------------------------------------------
--  Step #3
--  -------
--  Using the query from Lab 10, Step 3, insert the query
--  in the MERGE statement to the TRANSACTION table.
-- --------------------------------------------------------
 
-- Insert step #3 statements here.
 
-- --------------------------------------------------------
--  Step #4
--  -------
--  Insert the MERGE statements to the RENTAL, RENTAL_ITEM,
--  and TRANSACTION tables into the UPLOAD_TRANSACTION
--  procedure; execute the UPLOAD_TRANSACTION procedure,
--  and query the results from the target tables; and
--  re-execute the UPLOAD_TRANSACTION procedure to
--  verify that the query returns the same set and only
--  inserts new records.
-- --------------------------------------------------------
 
-- Insert step #4 statements here.
 
-- --------------------------------------------------------
--  Step #5
--  -------
--  Write a query that uses date functions to report
--  aggregated transaction amount values for base revenue,
--  110% of revenue, 120% of revenue, 90% of revenue,
--  80% or revenue.
-- --------------------------------------------------------
 
-- Insert step #5 statements here.
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
COMMIT;

You should complete the following five steps:

  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 as a template:

Instruction Details →

You copy the query from Lab #10, Step #1 into the MERGE statement and test the MERGE statement.

1
2
3
4
5
6
7
8
9
10
11
12

MERGE INTO rental target
USING ( ... query_statement ... ) source
ON (target.rental_id = source.rental_id)
WHEN MATCHED THEN
UPDATE SET last_updated_by = source.last_updated_by
,          last_update_date = source.last_update_date
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.

Instruction Details →

You copy the query from Lab #10, Step #2 into the MERGE statement and test the MERGE statement.

1
2
3
4
5
6
7
8
9
10
11
12

MERGE INTO rental_item target
USING ( ... query_statement ... ) source
ON (target.rental_item_id = source.rental_item_id)
WHEN MATCHED THEN
UPDATE SET last_updated_by = source.last_updated_by
,          last_update_date = source.last_update_date
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.

Instruction Details →

You copy the query from Lab #10, Step #3 into the MERGE statement and test the MERGE statement.

1
2
3
4
5
6
7
8
9
10
11
12

MERGE INTO transaction target
USING ( ... query_statement ... ) source
ON (target.transaction_id = source.transaction_id)
WHEN MATCHED THEN
UPDATE SET last_updated_by = source.last_updated_by
,          last_update_date = source.last_update_date
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.

Instruction Details →

Copy the three MERGE statements into the upload_transaction procedure.

  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.

Instruction Details →

You write a query that uses date mathematics and aggregation against the transaction table.

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

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Test Case Instructions →

You should embed the verification queries from these instructions inside your apply_lab11_oracle.sql script file. You should call the apply_lab11_oracle.sql script from the sqlplus command-line utility

@apply_oracle_lab11.sql

You should submit your apply_oracle_lab11.sql script file and apply_oracle_lab11.txt log file for a grade.

Written by michaelmclaughlin

August 13th, 2018 at 11:48 am

Posted in