Database Tutorial

Course Tutorial Site

Site Admin

Lab #10: Oracle

with 42 comments

Lab #10: 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 create the apply_oracle_lab10.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_lab10.txt
 
... insert code here ...
 
SPOOL OFF

You should embed the verification queries inside your apply_lab10_oracle.sql script. You don’t require indexes for Oracle but should use them for MySQL.

The following is a mapping and translation table. You should use the mapping and translation table to create three queries that take records from the TRANSACTION_UPLOAD table with intent of putting the information into the RENTAL, RENTAL_ITEM, and TRANSACTION tables.

The mapping of the TRANSACTION_UPLOAD external table to the TRANSACTION table requires some translations and mappings. A translation may mean that we take a column value from the source table and use it as a filter in a join to find a key value in another table. Translation may also mean that we apply an aggregation function to a source column. Mapping may mean associating a column name in the import *.csv file, or external table definition, with a column in a target table. A second meaning of mapping may mean that you need to join tables on a natural key to find a surrogate key. Lastly, mapping may mean linking to the system access logs or local current clock settings.

An example of translation would be using a COMMON_LOOKUP_TYPE column value from the *.csv files to find a valid COMMON_LOOKUP_ID value. Any *.csv file column that maps to a value found in the COMMON_LOOKUP_TYPE column of the COMMON_LOOKUP table is a key to discover a proper foreign key value (a COMMON_LOOKUP_ID column value) for an import target table.

The system analyst was pushed for time, and your manager assured him that you could build your own mapping guides on the first two SELECT  statements. The system analysis did provide a mapping guide between the import *.csv file or external table for the TRANSACTION table because of its complexity. The table provides both translations and mappings. An example of how you may incorporate translation in your statements follows the table definition.

You should probably develop mapping tables for the first two SELECT statements before you write any code. Then, you should leverage the second query to write the third SELECT statements.

 Import Column Mapping Table
Reference Table Reference Column Map
Translate		 Source Table Source Column
TRANSACTION TRANSACTION_ID Translate Sequence TRANSACTION_S1
TRANSACTION TRANSACTION_ACCOUNT Map TRANSACTION_UPLOAD PAYMENT_ACCOUNT_NUMBER
TRANSACTION TRANSACTION_TYPE Translate COMMON_LOOKUP COMMON_LOOKUP_ID
TRANSACTION TRANSACTION_DATE Map TRANSACTION_UPLOAD TRANSACTION_DATE
TRANSACTION TRANSACTION_AMOUNT Map TRANSACTION_UPLOAD SUM(TRANSACTION_AMOUNT)
TRANSACTION RENTAL_ID Map RENTAL RENTAL_ID
TRANSACTION PAYMENT_METHOD_TYPE Translate COMMON_LOOKUP COMMON_LOOKUP_ID
TRANSACTION PAYMENT_ACCOUNT_NUMBER Map MEMBER CREDIT_CARD_NUMBER
TRANSACTION CREATED_BY Translate SYSTEM_USER SYSTEM_USER_ID
TRANSACTION CREATION_DATE Translate Internal Clock Current Date
TRANSACTION LAST_UPDATED_BY Translate SYSTEM_USER SYSTEM_USER_ID
TRANSACTION LAST_UPDATE_DATE Translate Internal Clock Current Date

  1. [15 points] The first SELECT statement uses a query that relies on a mapping and translation table. This step requires you to create a query that take records from the TRANSACTION_UPLOAD table and inserts them into the RENTAL table. You’ll use this query inside a SELECT statement in Lab #11.

You can discover the CONTACT_ID by using the following type of 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.

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 on an INNER JOIN.
  • Join the foregoing result set (the foregoing result set is the result set of the prior join) to TRANSACTION_UPLOAD table on an INNER JOIN.
  • Join the foregoing result set to RENTAL table on an LEFT JOIN.
  • The query to support the SELECT statement to the RENTAL table should return 4,681 rows, which is the same number that should go into the TRANSACTION table.
  • The natural key for the TRANSACTION table is a composite key made up of the following columns: RENTAL_ID, TRANSACTION_TYPE, TRANSACTION_DATE, PAYMENT_METHOD_TYPE, and PAYMENT_ACCOUNT_NUMBER.
  • The query in the SELECT statement doesn’t need to return the who-audit columns because you can simply put them in the INSERT statement. However, you also need the LAST_UPDATED_BY and LAST_UPDATE_DATE columns in the UPDATE statement, as seen below. It is best for maintenance coding that you put them all in the query, even though they’re not required.
1
2

UPDATE SET last_updated_by = SOURCE.last_updated_by
,          last_update_date = SOURCE.last_update_date

Diagnostics: A possible data error in earlier steps can cause major heartache matching the anticipated row inserts. Here are some diagnostics that you may run when you fail to have a matching number of row inserts.

  1. Check the join condition between the MEMBER and CONTACT table, which should return 15 rows. If this is not correct, fix the foreign key values in the CONTACT table.
1
2
3

SELECT   COUNT(*)
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id;

It should return the following rows.

1
2
3
4
5

  COUNT(*)
----------
	15
 
1 row selected.
  1. Check the join condition between the CONTACT and TRANSACTION_UPLOAD tables. It should return 11,520 rows. If this is not correct, fix the FIRST_NAME, MIDDLE_NAME, or LAST_NAME values in the CONTACT table or check whether you have the current *.csv file.
1
2
3
4
5

SELECT   COUNT(*)
FROM     contact c 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;

It should return the following rows.

1
2
3
4
5

  COUNT(*)
----------
     11520
 
1 row selected.
  1. Check the join condition between both the MEMBER and CONTACT tables and the join of those two tables with the TRANSACTION_UPLOAD table. This should return 11,520 rows. If this is not correct, fix the ACCOUNT_NUMBER column values in the MEMBER table.
1
2
3
4
5
6
7

SELECT   COUNT(*)
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      m.account_number = tu.account_number;

It should return the following rows.

1
2
3
4
5

  COUNT(*)
----------
     11520
 
1 row selected.
  1. Did you insert the correct foreign key value in the MEMBER_ID column of the CONTACT table when you added the Potter Family?
  2. Did you insert an address for each of the individuals in the Potter Family?
  3. Do the Potter’s live in Utah?
  4. Did you insert the correct foreign key value in the CONTACT_ID column of the ADDRESS table.
  5. If the Potter’s live someplace other than Provo or Spanish Fork, did you enter a row in the AIRPORT table for them?
  6. Did you enter the correct names for the Potter’s? They should be Harry Potter, Ginny Potter, and Lily Luna Potter.

You use the following formatting instructions:

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"

You should see the following result set (redacted to the first 4 and last 4 rows) and the count of rows:

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.

After you query the rows, you query a count from the RENTAL table before you insert the rows from your query. This SELECT statement gets you the before value:

SELECT   COUNT(*) AS "Rental before count"
FROM     rental;

It should return the following value:

Rental before count
-------------------
	          8
 
1 row selected.

After you insert the records from the query, you re-query a count from the RENTAL table. The same SELECT statement gets you the after value:

SELECT   COUNT(*) AS "Rental after count"
FROM     rental;

It should return the following value:

Rental after count
------------------
	      4689
 
1 row selected.

  1. [15 points] The second SELECT statement requires that you inserted values into the RENTAL table in the last step. It leverages the joins that you worked out in the first SELECT statement. Don’t try to re-invent the wheel because it isn’t profitable in this case.

  • The query to support the SELECT statement to the RENTAL_ITEM table should return 11,520 rows and the DISTINCT operator isn’t by the SELECT statement to get the correct results. The DISTINCT keyword is required to verify your SQL statement returns the right number of rows before inserting it into the SELECT statement. You’ll also need to nest the query with the distinct result set as an inline view, like the following:
1
2
3
4
5
6
7
8
9
10
11

SELECT   COUNT(*)
FROM    (SELECT   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
         ,        3 AS created_by
         ,        TRUNC(creation_date) AS creation_date
         ,        3 AS last_updated_by
         ,        TRUNC(last_update_date) AS last_update_date
         FROM ... ) il
  • After you’ve created the first query for the SELECT statement to the RENTAL table, all you need to do for the SELECT statement to the RENTAL_ITEM table is the following:
  1. Join the foregoing result set (the foregoing result set is the result set of the prior join) to the COMMON_LOOKUP table on an INNER JOIN, and translate the COMMON_LOOKUP_TYPE column value to a COMMON_LOOKUP_ID value.
  2. Join the foregoing result set to the RENTAL_ITEM table on an LEFT JOIN.
  3. Remove the DISTINCT operator
  4. Restructure the SELECT clause list.

You use the following formatting instructions:

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"

You should see the following result set (redacted to the first 4 and last 4 rows) and the count of rows (please note that your results may differ when the 3-Day Rental value in the COMMON_LOOKUP table has a different result):

                       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.

After you query the rows, you query a count from the RENTAL_ITEM table before you insert the rows from your query. This SELECT statement gets you the before value:

SELECT   COUNT(*) AS "Rental Item Before Count"
FROM     rental_item;

It should return the following value:

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

After you insert the records from the query, you re-query a count from the RENTAL_ITEM table. The same SELECT statement gets you the after value:

SELECT   COUNT(*) AS "Rental Item After Count"
FROM     rental_item;

It should return the following value:

Rental items After count
------------------------
		   11533
 
1 row selected.

  1. [15 points] The third SELECT statement requires that you inserted values into the RENTAL_ITEM table in the last step. It also leverages the joins that you worked out in the first and secondSELECT statements. Don’t try to re-invent the wheel because it isn’t profitable, especially in this case.

  • The query to support the SELECT statement to the TRANSACTION table should return 4,681 rows, which is the same number that should have gone into the RENTAL table. Unlike the query for the RENTAL table, you can’t use the DISTINCT operator. You will need to use a SUM function and a GROUP BY clause.
  • Assuming that the query for the first SELECT statement is your starting point, change the LEFT JOIN to RENTAL table to an INNER JOIN because rows must exist in the RENTAL table before you enter rows in the TRANSACTION table.
  • Join the foregoing result set (the foregoing result set is the result set of the prior join) to the COMMON_LOOKUP table on an INNER JOIN, use an alias like cl1 because you’ll have another to this table. This is done to narrow limit the selection of the rows, and you should plan on the following two join statements:
1
2
3

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 the COMMON_LOOKUP table on an INNER JOIN, use an alias like cl2 because you don’t want to conflict with what you did in the prior step. This is done to narrow limit the selection of the rows, and you should plan on the following two join statements:
1
2
3

ON      cl2.common_lookup_table = 'TRANSACTION'
AND     cl2.common_lookup_column = 'PAYMENT_METHOD_TYPE'
AND     cl2.common_lookup_type = 'Check mapping table to find the correct type value'
  • Join the foregoing result set to the TRANSACTION table on an LEFT JOIN. The trick here is figuring out the natural key, which is the compound key of TRANSACTION_ACCOUNT, TRANSACTION_TYPE, TRANSACTION_DATE, TRANSACTION_AMOUNT, PAYMENT_METHOD, and PAYMENT_ACCOUNT_NUMBER columns. You should use the provided mapping table to figure out what goes in the SELECT clause.
  • A GROUP BY clause has restrictions that differ across database implementations. In Oracle, you can’t include aliases that are in the SELECT clause in the GROUP BY clause.
  • The query in the SELECT statement doesn’t need to return the who-audit columns because you can simply put them in the INSERT statement. However, you also need the LAST_UPDATED_BY and LAST_UPDATE_DATE columns in the UPDATE statement, as seen below. It is best for maintenance coding that you put them all in the query, even though they’re not required.
                                                                    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.

Written by michaelmclaughlin

June 22nd, 2014 at 1:26 am

Posted in

42 Responses to 'Lab #10: Oracle'

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

  1. “Did you inadvertently insert a zero length string instead of a null for a MIDDLE_NAME column value when you Ginny and Harry Potter (only a critical failure in MySQL)?”

    When you ‘what’ Ginny and Harry Potter?

    Abel Goodwin

    27 Jun 14 at 11:05 am

  2. You should explicity state in a step for 1 that we need to insert the results of our query into the rental table. I know it says it at the top, but maybe an additional step at the end of 1 telling us to insert it.
    Also,
    I’m having difficulty deciding what to join onto the rental table.

    Abel Goodwin

    27 Jun 14 at 11:19 am

  3. In step 2 it may be helpful to know to what tables we are joining to/from and perhaps on what keys.

    Abel Goodwin

    27 Jun 14 at 1:57 pm

  4. Abel, Good catch, it should only be in the MySQL version.

    michaelmclaughlin

    29 Jun 14 at 3:26 pm

  5. Abel, The tables are mentioned in the instructions but the purpose of the lab is for the student to discover which columns compose the natural key, and to perform outer join operations by using the natural key values.

    michaelmclaughlin

    29 Jun 14 at 3:34 pm

  6. Abel, I’ve re-worded it to be more direct. Discovering what to join is the focus of the exercise.

    michaelmclaughlin

    29 Jun 14 at 3:39 pm

  7. The “diagnostics” section collapsed would be nice so that the headers and the query can be connected easily to the query information at the top.

    Bryce Franzen

    30 Jun 14 at 1:33 pm 

  8. ON      cl1.common_lookup_table = 'TRANSACTION'
AND     cl1.common_lookup_column = 'PAYMENT_METHOD_TYPE'
AND     cl1.common_lookup_type = 'Check mapping table'

    these should be "cl2" as the alias throughout, right?

    Davies

    30 Jun 14 at 1:36 pm

  9. Brother McLaughlin,
    The test for #2 shows the rental item type as 1024, is that a 1-day or 3-day rental on your database?
    Everyone else I have asked is returning 1025, may be something to look at reconciling.

    Abel Goodwin

    30 Jun 14 at 5:55 pm

  10. Brother McLaughlin,
    Finished steps 1 and 2. I’m a bit confused as to how we’re going to get any results for #3 if our transaction tables are not populated with any data. I’ve asked a few people to do select * from transaction; and none of us have any rows.

    Is this why I’m unable to get the same result set as you?
    Thanks.

    Abel Goodwin

    30 Jun 14 at 7:06 pm

  11. The third query doesn’t use rows from the transaction table, it uses rows from the transaction_upload table. You don’t need to insert rows because there isn’t another query with a dependency on rows in the transaction table.

    michaelmclaughlin

    1 Jul 14 at 12:16 am

  12. Abel, it depends on the order you chose to insert the 3-Day Rental value for the RENTAL_ITEM table.

    michaelmclaughlin

    1 Jul 14 at 12:38 am

  13. Davies, Yes. I’ve corrected it. Great catch, I’ve made the following changes:

    ON      cl2.common_lookup_table = 'TRANSACTION'
AND     cl2.common_lookup_column = 'PAYMENT_METHOD_TYPE'
AND     cl2.common_lookup_type = 'Check mapping table'

    michaelmclaughlin

    1 Jul 14 at 12:47 am

  14. Bryce, it’s on the list for changes.

    michaelmclaughlin

    1 Jul 14 at 12:48 am

  15. Brother McLaughlin,

    You said, “The third query doesn’t use rows from the transaction table”, however instructions from #3 read, “Join the foregoing result set to the TRANSACTION table on an LEFT JOIN.”

    What am I misunderstanding here?

    Abel Goodwin

    1 Jul 14 at 8:45 pm

  16. Abel, A left join against a table without any values in the TRANSACTION table yields all the results from the rest of the join and a null value in the TRANSACTION_ID column.

    michaelmclaughlin

    2 Jul 14 at 12:39 am

  17. “plan on the following two join statements:”
    In stead of give us :
    AND cl1.common_lookup_type = ‘Check mapping table’
    Give us:
    AND cl1.common_lookup_type = ?
    and give detail before the code that we will need to check the mapping table for the comparison.
    This might help my first grade comprehension,
    Thanks.

    Abel Goodwin

    2 Jul 14 at 4:36 pm

  18. When you start with STEP 3 it sounds like you suggest that we should start with the join that we make in STEP 2. Then you assume in the SECOND bullet point in STEP 3 that we start with the join in STEP 1. This is very confusing and creates an issue that is very hard to find. I suggest that you be a little more clear about which join we should start with in STEP 3.

    Kent

    2 Jul 14 at 10:42 pm

  19. #4 regarding the delete statements, should it be Delete the RENTAL_ITEM table rows with a RENTAL_ID value greater than 9, (you have less than 8)

    Leesa

    7 Jul 14 at 1:35 pm

  20. Step 4 should say that you need to reset the starting sequence number back to what it was at first. It will give you problems in lab 12 when you try to import and merge the transaction_reversal into the transaction table. It fails because it can not find matching rental_item numbers in the rental and transaction_reversal tables. Just thought you should make a note of it.

    Kent

    8 Jul 14 at 12:43 am

  21. Fixed.

    michaelmclaughlin

    22 Oct 14 at 1:12 am

  22. Abel, I’m not sure a “?” helps but I’ve expanded the sentence.

    michaelmclaughlin

    22 Oct 14 at 2:53 am

  23. In the Import Column Mapping Table one of the columns’ name is incomplete, namely PAYMENT_METHOD instead of PAYMENT_METHOD_TYPE.

    Gabe Ventilla

    18 Nov 14 at 12:15 am

  24. The below query in step one misses an “ORDER BY contact_id” in order to get the result set shown.

    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;

    Gabe Ventilla

    18 Nov 14 at 12:20 am

  25. Gabe, Nice catch. It’s fixed.

    michaelmclaughlin

    18 Nov 14 at 2:23 am

  26. Gabe, Yes, it was incorrect in both the Oracle and MySQL lab descriptions. I’ve fixed both.

    michaelmclaughlin

    18 Nov 14 at 2:25 am

  27. Move step 4 to lab 11.

    Jeremy

    19 Nov 14 at 2:22 pm

  28. From the text:

    After you query the rows, you query a count from the RENTAL_ITEM table before you insert the rows from your query. This SELECT statement gets you the before value:

    SELECT   COUNT(*) AS "Rental Item Before Count"
FROM     rental_item;

    It should return the following value:

    Rental Item After Count
-----------------------
		     13

    1 row selected.

    both the validation codes show “rental item after count” but the code “SELECT COUNT(*) AS "Rental Item Before Count" says before and doesn’t match what is shown below in the example.

    Cory

    20 Nov 14 at 9:16 pm

  29. Your column formatting for information going into rental.

    SET NULL ''
COLUMN rental_item_id     FORMAT 9999 HEADING "Rental|Item ID #"
COLUMN rental_id          FORMAT 9999 HEADING "Rental|ID #"
COLUMN item_id            FORMAT 9999 HEADING "Item|ID #"
COLUMN rental_item_price  FORMAT A9   HEADING "Rental|Item|Price"
COLUMN rental_item_type   FORMAT A10  HEADING "Rental|Item|Type"

    rental_item_type has been translated and is no longer a string, it’s now a number. Shouldn’t it be something like:

    COLUMN rental_item_type   FORMAT 9999 HEADING "Rental|Item|Type"

    RJ

    21 Nov 14 at 4:44 pm

  30. In Step #2, the Rental Item Price should be changed to 3.18 within the result set from rental_item that shows the first 4 and last 4 rows…

    Gabe Ventilla

    21 Nov 14 at 5:06 pm

  31. RJ, Yes is should. It’s fixed now.

    michaelmclaughlin

    22 Nov 14 at 12:28 am

  32. Gabe, As discussed today, you’re right. It’s been updated. Thanks!

    michaelmclaughlin

    22 Nov 14 at 12:29 am

  33. Cory, Great catch, it’s fixed.

    michaelmclaughlin

    22 Nov 14 at 12:36 am

  34. The “Rental Item Before Count” and “Rental Item After Count” in step #2 are backwards AND the code does not match the output in the “Rental Item After Count” statement.

    Gabe Ventilla

    22 Nov 14 at 11:42 pm

  35. Gabe, Thanks. It’s fixed correctly now.

    michaelmclaughlin

    23 Nov 14 at 1:55 am

  36. The first SQL statement on this page references the wrong folder:

    — 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.
    @../lab8/apply_oracle_lab9.sql
    Says lab 8 then lab 9

    SPOOL apply_oracle_lab10.txt

    … insert code here …

    SPOOL OFF

    Sam Graham

    9 Dec 14 at 2:08 pm

  37. Sam, Thanks. It’s fixed: @../lab9/apply_oracle_lab9.sql

    michaelmclaughlin

    21 Dec 14 at 5:19 pm

  38. Dropped Step #4 as unnecessary because Lab #11 now calls Lab #9.

    michaelmclaughlin

    23 Dec 14 at 11:02 pm

  39. I think there should b a ‘;‘ at the end of this statement in step 1:

    UPDATE SET last_updated_by = SOURCE.last_updated_by
,          last_update_date = SOURCE.last_update_date

    Robbie Bise

    10 Mar 15 at 8:25 pm

  40. step 2.

    Format code.

    COLUMN rental_item_id     FORMAT 9999 HEADING "Rental|Item ID #"

    the format should be ‘99999’

    and for the rental_item_price

    COLUMN rental_item_price  FORMAT A9   HEADING "Rental|Item|Price"

    the format should be ‘9999’

    Joseph Tracy

    11 Mar 15 at 1:48 pm

  41. Robbie, It’s a fragment from a MERGE statement, not an UPDATE statement. A semicolon is unneeded.

    michaelmclaughlin

    13 Mar 15 at 9:38 am

  42. Joseph, Great catch! They’re fixed now.

    michaelmclaughlin

    13 Mar 15 at 9:43 am

Leave a Reply