Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #10: Outer Join Select

Learn how to write SELECT statements with a query of data derived by an outer join against an external table and internally managed tables. Reinforce how you embed the queries to use sequence values as a pseudo column before inserting the results in INSERT statements. There are three queries that you must develop and insert their data sequentially. The sequential nature of the queries resolves around the independence of the first query, and the dependencies of the second and third queries. In other words, you must insert the records into the rental table before you can insert rows in the rental_item table, and you must insert records into the rental and rental_item tables before you can insert records into the transaction table.

Lab Instructions

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

[72 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_lab10.sql script, which should be in the following directory:

/home/student/Data/cit225/lab10

Sample script →

This expands to show you how to structure your apply_oracle_lab10.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 7, Step 4, insert the 135
--  rows in the PRICE table created in Lab 6.
-- --------------------------------------------------------
 
-- Insert step #1 statements here.
 
-- --------------------------------------------------------
--  Step #2
--  -------
--  Add a NOT NULL constraint on the PRICE_TYPE column
--  of the PRICE table.
-- --------------------------------------------------------
 
-- Insert step #2 statements here.
 
-- --------------------------------------------------------
--  Step #3
--  -------
--  Update the RENTAL_ITEM_PRICE column with valid price
--  values in the RENTAL_ITEM table.
-- --------------------------------------------------------
 
-- Insert step #3 statements here.
 
-- --------------------------------------------------------
--  Step #4
--  -------
--  Add a NOT NULL constraint on the RENTAL_ITEM_PRICE
--  column of the RENTAL_ITEM table.
-- --------------------------------------------------------
 
-- Insert step #4 statements here.
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
COMMIT;

You should complete the following four steps:

  1. [24 points] The first SELECT statement uses a query that relies on a mapping and translation table introduced above. This step requires you to create a query that takes records from the transaction_upload table and inserts them into the rental table.

You will have the following SELECT-list and column formatting in your first query statement:

SET NULL '<Null>'
COLUMN rental_id        FORMAT 9999 HEADING "Rental|ID #"
COLUMN customer         FORMAT 9999 HEADING "Customer|ID #"
COLUMN check_out_date   FORMAT A9   HEADING "Check Out|Date"
COLUMN return_date      FORMAT A10  HEADING "Return|Date"
COLUMN created_by       FORMAT 9999 HEADING "Created|By"
COLUMN creation_date    FORMAT A10  HEADING "Creation|Date"
COLUMN last_updated_by  FORMAT 9999 HEADING "Last|Update|By"
COLUMN last_update_date FORMAT A10  HEADING "Last|Updated"
SELECT   DISTINCT
         r.rental_id
,        c.contact_id
,        tu.check_out_date AS check_out_date
,        tu.return_date AS return_date
,        3 AS created_by
,        TRUNC(SYSDATE) AS creation_date
,        3 AS last_updated_by
,        TRUNC(SYSDATE) AS last_update_date
FROM     ...

Naturally, you can’t start with that SELECT-list because it refers to columns not yet in the result set. You begin this step with the following base query:

1
2
3
4
5
6
7
8

SELECT   DISTINCT c.contact_id
FROM     member m INNER JOIN transaction_upload tu
ON       m.account_number = tu.account_number INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = tu.first_name
AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
AND      c.last_name = tu.last_name
ORDER BY c.contact_id;

It should return the following rows:

CONTACT_ID
----------
      1001
      1002
      1003
      1004
      1005
      1006
      1007
      1008
      1009
      1010
      1011
      1012
      1013
      1014
      1015
 
15 rows selected.

Instruction Details →

In the first step of this lab, you will complete the FROM clause by providing all necessary join statements. You should write the join statements with INNER JOIN, LEFT JOIN, or RIGHT JOIN operations and the ON subclause. Then, you insert the results as rows in the rental table.

The first SELECT statement uses a query that performs the following joins. There aren’t any subqueries in the statement, and the DISTINCT operator is required to make it re-runnable.

  • Join the member table to contact table with an INNER JOIN operator on the surrogate key, which is the member_id column in both tables.
  • Join the foregoing result set, which is the result set of the join between the member and contact tables, to the transaction_upload table. As shown in the sample starting query, you effect that join by matching the account_number in the member table with the account_number in the transaction_upload table; and the first_name, middle_name, and last_name in the contact and transaction_upload table. Please note that the middle_name column may contain a null value, which necessitates using a NVL() function on both sides of the join because the equal (=) comparison operator only compares not null values.
  • Join the foregoing result set to rental table with a LEFT JOIN. You should match the contact_id column in the contact table with the customer_id column in the rental table; and the truncated check_out_date and truncated return_date from the transaction_upload and rental tables.

Your query should return the following type of result set:

Rental Customer Check Out Return     Created Creation	Update Last
  ID #	   ID # Date	  Date		  By Date	    By Updated
------ -------- --------- ---------- ------- ---------- ------ ----------
<Null>	   1002 12-OCT-09 15-OCT-09	1001 23-JUN-14	  1001 23-JUN-14
<Null>	   1002 30-AUG-09 02-SEP-09	1001 23-JUN-14	  1001 23-JUN-14
<Null>	   1003 29-OCT-09 01-NOV-09	1001 23-JUN-14	  1001 23-JUN-14
<Null>	   1014 23-DEC-09 26-DEC-09	1001 23-JUN-14	  1001 23-JUN-14
...
<Null>	   1001 18-FEB-09 21-FEB-09	1001 23-JUN-14	  1001 23-JUN-14
<Null>	   1003 20-JUN-09 23-JUN-09	1001 23-JUN-14	  1001 23-JUN-14
<Null>	   1012 06-JAN-09 09-JAN-09	1001 23-JUN-14	  1001 23-JUN-14
<Null>	   1004 28-JAN-09 31-JAN-09	1001 23-JUN-14	  1001 23-JUN-14
 
4681 rows selected.

You should insert the results into the rental table using the following type of INSERT statement:

INSERT INTO rental
SELECT   NVL(r.rental_id,rental_s1.NEXTVAL) AS rental_id
,        r.contact_id
,        r.check_out_date
,        r.return_date
,        r.created_by
,        r.creation_date
,        r.last_updated_by
,        r.last_update_date
FROM    (SELECT   DISTINCT
                  r.rental_id
         ,        c.contact_id
         ,        tu.check_out_date AS check_out_date
         ,        tu.return_date AS return_date
         ,        1001 AS created_by
         ,        TRUNC(SYSDATE) AS creation_date
         ,        1001 AS last_updated_by
         ,        TRUNC(SYSDATE) AS last_update_date
         FROM     member m INNER JOIN contact c
         ON       m.member_id = c.member_id INNER JOIN transaction_upload tu
         ON       c.first_name = tu.first_name
         AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
         AND      c.last_name = tu.last_name
         ... REMAINDER OF join solution ... ) r;

You should note that the NVL() function call in the SELECT-list substitutes a new sequence value when an existing value is not found. After the insert into the rental table, you should run the following verification query:

COL rental_count FORMAT 999,999 HEADING "Rental|after|Count"
SELECT   COUNT(*) AS rental_count
FROM     rental;

It should return the following value:

  Rental
   after
   count
--------
  4,689
 
1 row selected.

The result is the sum of the 8 rows originally inserted plus the 4,681 rows inserted from the transaction_upload table.

  1. [24 points] The second SELECT statement uses a query that relies on a combination of:

    • Mapping values through equijoin value comparisons; and
    • Translating values through equijoin value comparisons on something other than a primary and foreign key comparison.

    You map joins between the primary and foreign key column or columns, or between the natural key and a copy of the natural key. Sometimes you need to map values between only part of the a natural key, which can occur with the common_lookup table. The common_lookup table uses the common_lookup_table and common_lookup_column columns to identify subordinate unique table sets, and the common_lookup table uses the common_lookup_type column as a unique column within those subordinate table sets.

    After inserting the results of the first query into the rental table and selecting the correct row set for the second query, you should insert the rows into the rental_item table.

You will have the following SELECT-list and column formatting in your second query statement:

SET NULL '<Null>'
COLUMN rental_item_id     FORMAT 99999 HEADING "Rental|Item ID #"
COLUMN rental_id          FORMAT 99999 HEADING "Rental|ID #"
COLUMN item_id            FORMAT 99999 HEADING "Item|ID #"
COLUMN rental_item_price  FORMAT 99999 HEADING "Rental|Item|Price"
COLUMN rental_item_type   FORMAT 99999 HEADING "Rental|Item|Type"
COLUMN created_by         FORMAT 9999 HEADING "Created|By"
COLUMN creation_date      FORMAT A10  HEADING "Creation|Date"
COLUMN last_updated_by    FORMAT 9999 HEADING "Last|Update|By"
COLUMN last_update_date   FORMAT A10  HEADING "Last|Updated"
SELECT   ri.rental_item_id
,        r.rental_id
,        tu.item_id
,        TRUNC(r.return_date) - TRUNC(r.check_out_date) AS rental_item_price
,        cl.common_lookup_id AS rental_item_type
,        1001 AS created_by
,        TRUNC(SYSDATE) AS creation_date
,        1001 AS last_updated_by
,        TRUNC(SYSDATE) AS last_update_date
FROM     ...

The first query is your starting point for the second query.

Instruction Details →

You inserted rows into the rental table in the first step of this lab. In the second step, you insert rows into the rental_item table.

The second SELECT statement uses a query that performs the following joins. You do not need to use a DISTINCT operator because the SELECT-list returns a unique set.

  • Join the result set from the first query solution of this lab’s first step to the common_lookup table. You need to effect the join between a subtable inside the common_lookup table rather than a join to the common_lookup table. This type of join behavior is known as translating a join. You can translate the relationship using the following pattern of join logic: 

    ON      cl1.common_lookup_table = 'RENTAL_ITEM'
AND     cl1.common_lookup_column = 'RENTAL_ITEM_TYPE'
AND     cl1.common_lookup_type = 'Check mapping table to find the correct type value'
  • Join the foregoing result set to the rental_item table by using a LEFT JOIN operator, which resolves when you use the rental_id column found in the rental and rental_item tables.

A verification query should exclude the who-audit columns and return the following type of result set:

                       Rental Rental
Rental    Rental Item    Item   Item
Item ID # ID #   ID #   Price   Type
--------- ------ ----- ------ ------
<Null>	    5090  1005   3.18   1024
<Null>	    5090  1005   3.18   1024
<Null>	    1674  1018   3.18   1024
<Null>	    1674  1018   3.18   1024
...
<Null>	    2118  1014   3.18   1024
<Null>	    2118  1014   3.18   1024
<Null>	    2831  1005   3.18   1024
<Null>	    3160  1013   3.18   1024
 
11520 rows selected.

Before you insert the rows, you should query a count from the rental_item table, as shown:

COL rental_item_count FORMAT 999,999 HEADING "Rental|Item|Before|Count"
SELECT   COUNT(*) AS rental_item_count
FROM     rental_item;

It should return the following value:

 Rental
   Item
 Before
  Count
-------
     13
 
1 row selected.

You should insert the results into the rental table using the following type of INSERT statement:

INSERT INTO rental_item
(SELECT   NVL(ri.rental_item_id,rental_item_s1.NEXTVAL)
 ,        r.rental_id
 ,        tu.item_id
 ,        3 AS created_by
 ,        TRUNC(SYSDATE) AS creation_date
 ,        3 AS last_updated_by
 ,        TRUNC(SYSDATE) AS last_update_date
 ,        cl.common_lookup_id AS rental_item_type
 ,        r.return_date - r.check_out_date AS rental_item_price
 FROM     member m INNER JOIN contact c
 ON       m.member_id = c.member_id INNER JOIN transaction_upload tu
 ON       c.first_name = tu.first_name
 AND      NVL(c.middle_name,'x') = NVL(tu.middle_name,'x')
 AND      c.last_name = tu.last_name
 AND      tu.account_number = m.account_number
          ... REMAINDER OF join solution ... );

You should note that the NVL() function call in the SELECT-list substitutes a new sequence value when an existing value is not found. After the insert into the rental_item table, you should run the following verification query:

COL rental_item_count FORMAT 999,999 HEADING "Rental|Item|after|Count"
SELECT   COUNT(*) AS rental_item_count
FROM     rental_item;

It should return the following value:

 Rental
   Item
  After
  Count
-------
 11,533
 
1 row selected.

  1. [24 points] The third SELECT statement uses a query that relies on a combination of mapping and translating joins. Please refer to the preceding step for more detail on the differences and approach for using a translating join against a common_lookup table

After inserting the results of the first query into the rental and rental_item tables, you should insert the rows into the transaction table.

You will have the following SELECT-list and column formatting in your second query statement:

SET NULL '<Null>'
COLUMN transaction_id         FORMAT 99999 HEADING "Transaction|ID #"
COLUMN transaction_account    FORMAT A15   HEADING "Transaction|Account"
COLUMN transaction_type       FORMAT 99999 HEADING "Transaction|Type"
COLUMN transaction_date       FORMAT A11   HEADING "Date"
COLUMN transaction_amount     FORMAT 99999 HEADING "Amount"
COLUMN rental_id              FORMAT 99999 HEADING "Rental|ID #"
COLUMN payment_method_type    FORMAT 99999 HEADING "Payment|Method|Type"
COLUMN payment_account_number FORMAT A19    HEADING "Payment|Account Number"
COLUMN created_by             FORMAT 9999 HEADING "Created|By"
COLUMN creation_date          FORMAT A10  HEADING "Creation|Date"
COLUMN last_updated_by        FORMAT 9999 HEADING "Last|Update|By"
COLUMN last_update_date       FORMAT A10  HEADING "Last|Updated"
SELECT   t.transaction_id
,        tu.payment_account_number AS transaction_account
,        cl1.common_lookup_id AS transaction_type
,        TRUNC(tu.transaction_date) AS transaction_date
,       (SUM(tu.transaction_amount) / 1.06) AS transaction_amount
,        r.rental_id
,        cl2.common_lookup_id AS payment_method_type
,        m.credit_card_number AS payment_account_number
,        1001 AS created_by
,        TRUNC(SYSDATE) AS creation_date
,        1001 AS last_updated_by
,        TRUNC(SYSDATE) AS last_update_date
FROM     ...
GROUP BY t.transaction_id
,        tu.payment_account_number
,        cl1.common_lookup_id
,        tu.transaction_date
,        r.rental_id
,        cl2.common_lookup_id
,        m.credit_card_number
,        1001
,        TRUNC(SYSDATE)
,        1001
,        TRUNC(SYSDATE);

The SUM() aggregation function collapses the row set to a unique row set. Unlike the DISTINCT operator, aggregation functions require that you include a GROUP BY clause in the query. The GROUP BYclause determines how rows are collapsed to a unique set.

The first query is also your starting point for the third query.

Instruction Details →

You inserted rows into the rental table in the first step and you inserted rows into the rental_item table in the second step. In the third step, you query results to insert them into the transaction table.

The third SELECT statement uses a query that performs the following joins. You do not need to use a DISTINCT operator because the SELECT statement uses aggregation and a GROUP BY clause to guarantee uniqueness.

  • Join the result set from the first query solution of this lab’s first step to the common_lookup table. You need to effect the join between a subtable inside the common_lookup table rather than a join to the common_lookup table. This type of join behavior is known as translating a join. You can translate the relationship using the following pattern of join logic: 

    ON      cl1.common_lookup_table = 'TRANSACTION'
AND     cl1.common_lookup_column = 'TRANSACTION_TYPE'
AND     cl1.common_lookup_type = 'Check mapping table to find the correct type value'
  • Join the foregoing result set to a second copy of the common_lookup table. You need to effect the join between a subtable inside the common_lookup table like you did before: 

    ON      cl1.common_lookup_table = 'TRANSACTION'
AND     cl1.common_lookup_column = 'PAYMENT_METHOD_TYPE'
AND     cl1.common_lookup_type = 'Check mapping table to find the correct type value'
  • Join the foregoing result set to the transaction table by using a LEFT JOIN operator. You need to join the following keys:
    • The transaction_account column of the transaction table to payment_account_number of the transaction_upload table.
    • The rental_id of the transaction table to the rental_id of the rental table.
    • The transaction_type column of the transaction table to the correct copy of the common_lookup_id column in the scope of your translating join.
    • The transaction_date column of the transaction table to the transaction_date column of the transaction_upload table.
    • The payment_method_type column of the transaction table to the correct copy of the common_lookup_id column in the scope of your translating join.
    • The payment_account_number column to the credit_card_number in the member table.

You should receive the following output from the third query when you have written the correct WHERE clause.

                                                                    Payment
Transaction Transaction	    Transaction                      Rental  Method Payment
       ID # Account         Type        Date         Amount    ID #    Type Account Number
----------- --------------- ----------- --------- ---------- ------ ------- -------------------
<Null>	    111-111-111-111        1026 29-MAR-09      15.00   1012    1028 3333-4444-5555-6666
<Null>	    111-111-111-111        1026 03-MAY-09	3.00   1014    1028 2222-3333-4444-5555
<Null>	    111-111-111-111        1026 16-JAN-09	9.00   1017    1028 1111-2222-3333-4444
<Null>	    111-111-111-111        1026 13-JUL-09	9.00   1023    1028 1111-2222-3333-4444
...
<Null>	    111-111-111-111        1026 06-JAN-09	6.00   5672    1028 3333-4444-5555-6666
<Null>	    111-111-111-111        1026 29-JUN-09	3.00   5675    1028 1111-1111-1111-2222
<Null>	    111-111-111-111        1026 01-JUL-09	3.00   5678    1028 3333-4444-5555-6666
<Null>	    111-111-111-111        1026 24-JUL-09	6.00   5680    1028 1111-1111-1111-2222
 
4681 rows selected.

With a working query you can insert the rows into the transaction table. You should add your WHERE clause to the following INSERT template:

INSERT INTO TRANSACTION
SELECT   NVL(t.transaction_id,transaction_s1.NEXTVAL) transaction_id
,        t.transaction_account
,        t.transaction_type
,        t.transaction_date
,        t.transaction_amount
,        t.rental_id
,        t.payment_method_type
,        t.payment_account_number
,        t.created_by
,        t.creation_date
,        t.last_updated_by
,        t.last_update_date
FROM    (SELECT   t.transaction_id
         ,        tu.payment_account_number AS transaction_account
         ,        cl1.common_lookup_id AS transaction_type
         ,        tu.transaction_date
         ,       (SUM(tu.transaction_amount) / 1.06) AS transaction_amount
         ,        r.rental_id
         ,        cl2.common_lookup_id AS payment_method_type
         ,        m.credit_card_number AS payment_account_number
         ,        1001 AS created_by
         ,        TRUNC(SYSDATE) AS creation_date
         ,        1001 AS last_updated_by
         ,        TRUNC(SYSDATE) AS last_update_date
         FROM     ...
         GROUP BY t.transaction_id
         ,        tu.payment_account_number
         ,        cl1.common_lookup_id
         ,        tu.transaction_date
         ,        r.rental_id
         ,        cl2.common_lookup_id
         ,        m.credit_card_number
         ,        1001
         ,        TRUNC(SYSDATE)
         ,        1001
         ,        TRUNC(SYSDATE)) t;

After you have inserted the rows into the transaction table, you can query the results with the following query:

COL transaction_count FORMAT 999,999 HEADING "Transaction|After|Count"
SELECT   COUNT(*) AS transaction_count
FROM     TRANSACTION;

It should return the following value:

 Transaction
 After Count
------------
       4,681
 
1 row selected.

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_lab10_oracle.sql script file. You should call the apply_lab10_oracle.sql script from the sqlplus command-line utility

@apply_oracle_lab10.sql

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

Written by michaelmclaughlin

August 13th, 2018 at 11:42 am

Posted in