Database Tutorial

Course Tutorial Site

Site Admin

Lab #8: Oracle

with 17 comments

Lab #8: 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, and apply_oracle_lab7.sql scripts. You should create the apply_oracle_lab8.sql script as follows:

-- This calls Lab #7, Lab #7 calls Lab #6, Lab #6 calls Lab #5, and Lab #5 calls both the Creation and Seed script files.
@../lab7/apply_oracle_lab7.sql
 
SPOOL apply_oracle_lab8.txt
 
... insert code here ...
 
SPOOL OFF

You should embed the verification queries inside your apply_lab8_oracle.sql script.

  1. [10 points] Using the query you developed in Step #4 of Lab #7, add the necessary columns to insert the values directly into the PRICE table. You need to create a PRICE_S sequence and call the sequence as a value in the SELECT-list. (HINT: You call the PRICE_S sequence like this PRICE_S.NEXTVAL inside the SELECT-list.)

Table Name: PRICE
Column Name Constraint Data
Type
Physical
Size
Type Reference Table Reference Column
PRICE_ID PRIMARY KEY Integer Maximum
ITEM_ID FOREIGN KEY ITEM ITEM_ID Integer Maimum
PRICE_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
ACTIVE_FLAG NOT NULL Text 1
CHECK(‘Y’,’N’)
START_DATE NOT NULL Date Date
END_DATE Date Date
AMOUNT Integer Maimum
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL Date Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL Date Date

You should use the following query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SELECT  'OLD Y' AS "Type"
,        COUNT(CASE WHEN amount = 1 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 3 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 5 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'Y' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30
AND      end_date IS NULL
UNION ALL
SELECT  'OLD N' AS "Type"
,        COUNT(CASE WHEN amount =  3 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 10 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 15 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'N' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) > 30
AND NOT end_date IS NULL
UNION ALL
SELECT  'NEW Y' AS "Type"
,        COUNT(CASE WHEN amount =  3 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 10 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 15 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'Y' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31
AND      end_date IS NULL
UNION ALL
SELECT  'NEW N' AS "Type"
,        COUNT(CASE WHEN amount = 1 THEN 1 END) AS "1-Day"
,        COUNT(CASE WHEN amount = 3 THEN 1 END) AS "3-Day"
,        COUNT(CASE WHEN amount = 5 THEN 1 END) AS "5-Day"
,        COUNT(*) AS "TOTAL"
FROM     price p , item i
WHERE    active_flag = 'N' AND i.item_id = p.item_id
AND     (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31
AND      NOT (end_date IS NULL);

It should display the following results:

Type       1-Day      3-Day      5-Day      TOTAL
----- ---------- ---------- ---------- ----------
OLD Y         21         21         21         63
OLD N         21         21         21         63
NEW Y          3          3          3          9
NEW N          0          0          0          0
 
4 rows selected.

  1. [5 points] You should add the NOT NULL constraint to the PRICE_TYPE column of the PRICE table.

You should use the following query to verify that the PRICE_TYPE column has been converted from a nullable to a not null constrained column:

1
2
3
4
5
6
7
8
9
10
COLUMN CONSTRAINT FORMAT A10
SELECT   TABLE_NAME
,        column_name
,        CASE
           WHEN NULLABLE = 'N' THEN 'NOT NULL'
           ELSE 'NULLABLE'
         END AS CONSTRAINT
FROM     user_tab_columns
WHERE    TABLE_NAME = 'PRICE'
AND      column_name = 'PRICE_TYPE';

It should display the following results for a not null constrained column:

TABLE_NAME     COLUMN_NAME            CONSTRAINT
-------------- ---------------------- ----------
PRICE          PRICE_TYPE             NOT NULL
 
1 row selected.

  1. [5 points] The following query should update the RENTAL_ITEM_PRICE column for thirteen rows in the RENTAL_ITEM table. It should return nine values of 5, and two values of 3, and one value of 1, 10 and 15 each. If it returns anything else, you’ve encountered a failure. Like the other DML statements, this one contains a critical error related to the non-equijoin operation. You need to fix it before completing this step, and may need to check out how null values are handled here.

1
2
3
4
5
6
7
8
9
10
11
UPDATE   rental_item ri
SET      rental_item_price =
          (SELECT   p.amount
           FROM     price p INNER JOIN common_lookup cl1
           ON       p.price_type = cl1.common_lookup_id CROSS JOIN rental r
                    CROSS JOIN common_lookup cl2 
           WHERE    p.item_id = ri.item_id AND ri.rental_id = r.rental_id
           AND      ri.rental_item_type = cl2.common_lookup_id
           AND      cl1.common_lookup_code = cl2.common_lookup_code
           AND      r.check_out_date
                      BETWEEN p.start_date AND p.end_date);

You can confirm you’ve updated the correct rows with the following query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
COL customer_name          FORMAT A20  HEADING "Customer Name"
COL rental_id              FORMAT 9999 HEADING "Rental|ID #"
COL rental_item_id         FORMAT 9999 HEADING "Rental|Item ID"
COL rental_item_price      FORMAT 9999 HEADING "Rental|Item|Price"
COL amount                 FORMAT 9999 HEADING "Price|Amount"
COL price_type_code        FORMAT 9999 HEADING "Price|Type|Code"
COL rental_item_type_code  FORMAT 9999 HEADING "Rental|Item|Type|Code"
SELECT   c.last_name||', '||c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name
         END AS customer_name
,        r.rental_id
,        ri.rental_item_id
,        ri.rental_item_price
,        p.amount
,        TO_NUMBER(cl2.common_lookup_code) AS price_type_code
,        TO_NUMBER(cl2.common_lookup_code) AS rental_item_type_code
FROM     price p INNER JOIN common_lookup cl1
ON       p.price_type = cl1.common_lookup_id
AND      cl1.common_lookup_table = 'PRICE'
AND      cl1.common_lookup_column = 'PRICE_TYPE' INNER JOIN rental_item ri 
ON       p.item_id = ri.item_id INNER JOIN common_lookup cl2
ON       ri.rental_item_type = cl2.common_lookup_id
AND      cl2.common_lookup_table = 'RENTAL_ITEM'
AND      cl2.common_lookup_column = 'RENTAL_ITEM_TYPE' INNER JOIN rental r
ON       ri.rental_id = r.rental_id INNER JOIN contact c
ON       r.customer_id = c.contact_id
WHERE    cl1.common_lookup_code = cl2.common_lookup_code
AND      r.check_out_date
BETWEEN  p.start_date AND NVL(p.end_date,TRUNC(SYSDATE) + 1)
ORDER BY 2, 3;

The query should return the following data set. If it doesn’t you’ve made a mistake in one or more prior steps. You need to return and review those steps after you understand the correlated UPDATE statement.

							Rental
				    Rental	  Price   Item
		     Rental  Rental   Item  Price  Type   Type
Customer Name	       ID # Item ID  Price Amount  Code   Code
-------------------- ------ ------- ------ ------ ----- ------
Vizquel, Oscar	       1001    1001	 5	5     5      5
Vizquel, Oscar	       1001    1002	 5	5     5      5
Vizquel, Oscar	       1001    1003	 5	5     5      5
Vizquel, Doreen        1002    1004	 5	5     5      5
Vizquel, Doreen        1002    1005	 5	5     5      5
Sweeney, Meaghan       1003    1006	 5	5     5      5
Sweeney, Ian M	       1004    1007	 5	5     5      5
Winn, Brian	       1005    1008	 5	5     5      5
Winn, Brian	       1005    1009	 5	5     5      5
Potter, Harry	       1006    1010	 1	1     1      1
Potter, Harry	       1006    1011	 3	3     1      1
Potter, Ginny	       1007    1012	10     10     3      3
Potter, Lily Luna      1008    1013	15     15     5      5

If you didn’t get the results above, you may have an error in several places. You could have an error when you:

  • Inserted the records into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables.
  • Inserted the records into the RENTAL and RENTAL_ITEM tables.
  • Inserted the records into the PRICE table.

You click on the “Detailed Problem Solving” link to find the advanced diagnostics to solve problems from the prior labs.

  1. [5 points] Add a not null constraint to the RENTAL_ITEM_PRICE column of the RENTAL_ITEM table.

Table Name: RENTAL_ITEM
Column Name Constraint Data
Type
Physical
Size
Type Reference Table Reference Column
RENTAL_ITEM_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
NOT NULL
RENTAL_ITEM_PRICE NOT NULL Integer Maximum

You should use the following query to verify that the RENTAL_ITEM_PRICE column has been converted from a nullable to a not null constrained column:

1
2
3
4
5
6
7
8
9
10
COLUMN CONSTRAINT FORMAT A10
SELECT   TABLE_NAME
,        column_name
,        CASE
           WHEN NULLABLE = 'N' THEN 'NOT NULL'
           ELSE 'NULLABLE'
         END AS CONSTRAINT
FROM     user_tab_columns
WHERE    TABLE_NAME = 'RENTAL_ITEM'
AND      column_name = 'RENTAL_ITEM_PRICE';

It should display the following results for a not null constrained column:

TABLE_NAME     COLUMN_NAME            CONSTRAINT
-------------- ---------------------- ----------
RENTAL_ITEM    RENTAL_ITEM_PRICE      NOT NULL
 
1 row selected.

Written by michaelmclaughlin

June 3rd, 2014 at 1:16 am

Posted in

17 Responses to 'Lab #8: Oracle'

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

  1. I believe your confirmation query for step 3 is missing the NVL function because the mySQL lab’s confirmation query does have the IFNULL function. Also, I think we are supposed to return two values of 3 intead of only one because we gave Harry two one-day rentals instead of only one one-day rental.

    Jordan Hawkes

    4 Nov 14 at 7:52 pm

  2. The veification query for step 3 should have the return results

    RENTAL_ITEM_ID RENTAL_ITEM_PRICE     AMOUNT
    -------------- ----------------- ----------
    	  1001		       5	  5
    	  1002		       5	  5
    	  1003		       5	  5
    	  1004		       5	  5
    	  1005		       5	  5
    	  1006		       5	  5
    	  1007		       5	  5
    	  1008		       5	  5
    	  1009		       5	  5
    	  1010		       3	  3
    	  1011		       3	  3
    	  1012		      10	 10
    	  1013		      15	 15

    This is because Harry Rented two items.

    Jared Wrathall

    4 Nov 14 at 9:51 pm

  3. Jared, That’s a great catch.

    michaelmclaughlin

    6 Nov 14 at 1:18 pm

  4. Jordan,

    That’s a great catch. I’ve replaced line 11 with the following:

    11
    
    BETWEEN  p.start_date AND NVL(p.end_date,TRUNC(SYSDATE))

    michaelmclaughlin

    6 Nov 14 at 1:28 pm

  5. In step# 3’s instructions, there must be a couple of changes made. Currently it says the following: “The following query should update the RENTAL_ITEM_PRICE column for twelve rows in the RENTAL_ITEM table. It should return nine values of 5, and one value of 3, 10, and 15.”

    It should say: “The following query should update the RENTAL_ITEM_PRICE column for thirteen rows in the RENTAL_ITEM table. It should return nine values of 5, two values of 3 and one value 10 and 15 each.”

    Gabe Ventilla

    7 Nov 14 at 6:22 pm

  6. Step# 4 has the following instruction above the verification code: “You should use the following query to verify that the RENTAL_ITEM_TYPE column has been converted from a nullable to a not null constrained column:”

    It should instead refer to RENTAL_ITEM_PRICE as we place a NN constraint on it in step# 4, NOT on RENTAL_ITEM_TYPE.

    Gabe Ventilla

    7 Nov 14 at 10:38 pm

  7. In the validation query for step# 3, SYSDATE is truncated. However, if check_out_date is NOT truncated at the time a rental is inserted, then the check_out_date will fall outside of the time frame of BETWEEN p.start_date AND NVL(p.end_date,TRUNC(SYSDATE)) as TRUNC will produce an earlier time than the check_out_date by shaving off the time from the date…I believe that not truncating SYSDATE will provide a solution as this validation query will run well after any rental is inserted…Therefore, BETWEEN p.start_date AND NVL(p.end_date, SYSDATE) should always provide the desired result.

    Gabe Ventilla

    7 Nov 14 at 11:59 pm

  8. Gabe,

    Your logic makes no sense unless I expect the corner use case as follows: The student enters the ITEM_RELEASE_DATE or RELEASE_DATE column as the SYSDATE value instead of the TRUNC(SYSDATE) value. In that case, a null END_DATE value would become a SYSDATE value, and the later SYSDATE value shouldn’t present a problem because the earlier SYSDATE value will be inside the range of values.

    However, I’ve fixed the validation query and added the use of the SQL TRUNC function in Lab #7, Step 4. The fix to the validation query is:

    AND r.check_out_date BETWEEN p.start_date AND NVL(p.end_date, SYSDATE + 1)

    michaelmclaughlin

    8 Nov 14 at 11:39 pm

  9. Gabe, I concur and have made the change.

    michaelmclaughlin

    9 Nov 14 at 1:53 am

  10. Gabe, Thanks again. It’s fixed.

    michaelmclaughlin

    9 Nov 14 at 2:00 am

  11. The validation query for step 3 is incorrect according to Lab 6 step 3 d. In lab 6 step 3d, we are to insert one new release and one old release, however the validation query for lab 8 step 3 requires us to have 2 new releases.

    Greg Hall

    26 Feb 15 at 9:24 pm

  12. Greg, I think you’ve misread Lab 6, Step 3d. You should enter three new rentals that are new and one that’s old. One of the new rental items and the old rental item should be linked to a single rental.

    1. Insert three new rows in the RENTAL and four new rows in the RENTAL_ITEM tables. Two of the rows in the RENTAL_ITEM table should link to the same row in the RENTAL table for the rental of Harry Potter. In other words, there you should insert data for: (a) Harry Potter renting two rental items for a 1-Day Rental period (one new release and one old release), (b) Ginny Potter renting one new release rental item for a 3-Day Rental period, and (c) Lily Luna Potter renting one new release rental item for a 5-Day Rental period.

    michaelmclaughlin

    7 Mar 15 at 12:22 am

  13. To be consistent with lab six, the output for row 1011 in step 3 should have 1 in the price column and 1 in the amount column. The reason for this is that in lab six the directions are that “you should insert data for: (a) Harry Potter renting two rental items for a 1-Day Rental period (one new release and one old release).” The price for a one-day rental on an old release is one dollar, not three.

    Andrew Yelton

    20 Mar 15 at 5:18 pm

  14. Andrew, That’s a great catch.

    michaelmclaughlin

    22 Mar 15 at 7:38 pm

  15. Step 3 should show

    RENTAL_ITEM_ID RENTAL_ITEM_PRICE     AMOUNT
    -------------- ----------------- ----------
    	  1001		       5	  5
    	  1002		       5	  5
    	  1003		       5	  5
    	  1004		       5	  5
    	  1005		       5	  5
    	  1006		       5	  5
    	  1007		       5	  5
    	  1008		       5	  5
    	  1009		       5	  5
    	  1010		       3	  3
    	  1011		       3	  3
    	  1012		      10	 10
    	  1013		      15	 15

    Bryan Mcneil

    4 Nov 15 at 2:44 pm

  16. Bryan, You’re right and I’ve fixed it.

    michaelmclaughlin

    4 Nov 15 at 3:17 pm

  17. I beg to differ on step 3. I say that Rental_Item_ID 1011 should be 1 as Andrew pointed out, not 3 as Bryan is showing. The instruction in lab 6 is for two rentals for Harry; one new and one old. Therefore, Rental_Item_ID 1011 should be $1 for an old item for 1 day. We insert 3 new row in the Rental table. That would be for Harry, Ginny and Lily. Then there are 4 new rows in the Rental_Item table; two for Harry, one for Ginny and one for LILY! Now take a look at the number in step 3: It should return nine values of 5, and two values of 3, and one value of 1, 10 and 15 each – – – lets see * * * 9 + 2 + 3 = 14 * * * we are only returning 13 rows . . . I think the following should be the results.

    1001	5	5	Star Wars I
    1002	5	5	Star Wars II
    1003	5	5	Star Wars III
    1004	5	5	I Remember Mama
    1005	5	5	Camelot
    1006	5	5	Hook
    1007	5	5	Cars
    1008	5	5	RoboCop
    1009	5	5	The Hunt for Red October
    1010	3	3	Ender's Game
    1011	1	1	Star Wars II
    1012	10	10	Elysium
    1013	15	15	Tron

    Dean

    5 Nov 16 at 12:13 am

Leave a Reply