Database Tutorial

Course Tutorial Site

Site Admin

Lab #8: Oracle

with 19 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 couple errors. You need to fix it before completing this step, and you can find the missing components in the diagnostic query below the UPDATE statement.

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

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, which implements the complete logic required of a successful UPDATE statement:

-- Widen the display console.
SET LINESIZE 110
 
-- Set the column display values.
COL customer_name          FORMAT A20  HEADING "Contact|--------|Customer Name"
COL contact_id             FORMAT 9999 HEADING "Contact|--------|Contact|ID #"
COL customer_id            FORMAT 9999 HEADING "Rental|--------|Customer|ID #"
COL r_rental_id            FORMAT 9999 HEADING "Rental|------|Rental|ID #"
COL ri_rental_id           FORMAT 9999 HEADING "Rental|Item|------|Rental|ID #"
COL rental_item_id         FORMAT 9999 HEADING "Rental|Item|------||ID #"
COL price_item_id          FORMAT 9999 HEADING "Price|------|Item|ID #"
COL rental_item_item_id    FORMAT 9999 HEADING "Rental|Item|------|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
,        c.contact_id
,        r.customer_id
,        r.rental_id AS r_rental_id
,        ri.rental_id AS ri_rental_id
,        ri.rental_item_id
,        p.item_id AS price_item_id
,        ri.item_id AS rental_item_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' FULL 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' RIGHT JOIN rental r
ON       ri.rental_id = r.rental_id FULL 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;
 
-- Reset the column display values to their default value.
SET LINESIZE 80

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	   Rental Rental	       Rental
		      Contact	Rental Rental	Item   Item  Price   Item   Item  Price  Price	 Item
Contact 	     -------- -------- ------ ------ ------ ------ ------ ------ ------ ------ ------
--------	      Contact Customer Rental Rental	      Item   Item		  Type	 Type
Customer Name		 ID #	  ID #	 ID #	ID #   ID #   ID #   ID #  Price Amount   Code	 Code
-------------------- -------- -------- ------ ------ ------ ------ ------ ------ ------ ------ ------
Winn, Brian		 1002	  1002	 1005	1005   1008   1007   1007      5      5      5	    5
Winn, Brian		 1002	  1002	 1005	1005   1009   1001   1001      5      5      5	    5
Vizquel, Oscar		 1003	  1003	 1001	1001   1003   1005   1005      5      5      5	    5
Vizquel, Oscar		 1003	  1003	 1001	1001   1002   1004   1004      5      5      5	    5
Vizquel, Oscar		 1003	  1003	 1001	1001   1001   1002   1002      5      5      5	    5
Vizquel, Doreen 	 1004	  1004	 1002	1002   1005   1021   1021      5      5      5	    5
Vizquel, Doreen 	 1004	  1004	 1002	1002   1004   1016   1016      5      5      5	    5
Sweeney, Meaghan	 1005	  1005	 1003	1003   1006   1019   1019      5      5      5	    5
Sweeney, Ian M		 1007	  1007	 1004	1004   1007   1014   1014      5      5      5	    5
Potter, Harry		 1013	  1013	 1006	1006   1011   1022   1022      1      1      1	    1
Potter, Harry		 1013	  1013	 1006	1006   1010   1022   1022      3      3      1	    1
Potter, Ginny		 1014	  1014	 1007	1007   1012   1023   1023     10     10      3	    3
Potter, Lily Luna	 1015	  1015	 1008	1008   1013   1024   1024     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.

Detailed Problem Solving ↓

The following diagnostics help you determine which of the errors you may have made:

  • You can verify that the correct customers are configured with the following query. If they don’t show up in your environment with the following query, you made a mistake with the insert into the MEMBER, CONTACT, ADDRESS, STREET_ADDRESS, and TELEPHONE tables. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    COL customer_name  FORMAT A24  HEADING "Customer Name"
COL city           FORMAT A12  HEADING "City"
COL state          FORMAT A6   HEADING "State"
COL telephone      FORMAT A10  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name||', '||c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name
         END AS customer_name
,        a.city AS city
,        a.state_province AS state
,        t.telephone_number AS telephone
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN address a
ON       c.contact_id = a.contact_id INNER JOIN telephone t
ON       c.contact_id = t.contact_id;

    It should return the following rows:

    Account
Number	   Customer Name	    City	 State	Telephone
---------- ------------------------ ------------ ------ ----------
B293-71445 Winn, Randi		    San Jose	 CA	111-1111
B293-71445 Winn, Brian		    San Jose	 CA	111-1111
B293-71446 Vizquel, Oscar	    San Jose	 CA	222-2222
B293-71446 Vizquel, Doreen	    San Jose	 CA	222-2222
B293-71447 Sweeney, Meaghan	    San Jose	 CA	333-3333
B293-71447 Sweeney, Matthew	    San Jose	 CA	333-3333
B293-71447 Sweeney, Ian M	    San Jose	 CA	333-3333
R11-514-34 Clinton, Goeffrey Ward   Provo	 Utah	423-1234
R11-514-35 Moss, Wendy		    Provo	 Utah	423-1234
R11-514-36 Gretelz, Simon Jonah     Provo	 Utah	423-1234
R11-514-37 Royal, Elizabeth Jane    Provo	 Utah	423-1234
R11-514-38 Smith, Brian Nathan	    Spanish Fork Utah	423-1234
US00011    Potter, Harry	    Provo	 Utah	333-3333
US00011    Potter, Ginny	    Provo	 Utah	333-3333
US00011    Potter, Lily Luna	    Provo	 Utah	333-3333

  • You can verify that the correct rental and rental items are configured with the following query. If they don’t show up in your environment with the following query, you made a mistake with the insert into the RENTALRENTAL_ITEM tables. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

    COL account_number  FORMAT A10  HEADING "Account|Number"
COL customer_name   FORMAT A22  HEADING "Customer Name"
COL rental_id       FORMAT 9999 HEADING "Rental|ID #"
COL rental_item_id  FORMAT 9999 HEADING "Rental|Item|ID #"
SELECT   m.account_number
,        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
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN rental r
ON       c.contact_id = r.customer_id INNER JOIN rental_item ri
ON       r.rental_id = ri.rental_id
ORDER BY 3, 4;

    It should return the following rows:

    					 Rental
Account 			  Rental   Item
Number	   Customer Name	    ID #   ID #
---------- ---------------------- ------ ------
B293-71446 Vizquel, Oscar	    1001   1001
B293-71446 Vizquel, Oscar	    1001   1002
B293-71446 Vizquel, Oscar	    1001   1003
B293-71446 Vizquel, Doreen	    1002   1004
B293-71446 Vizquel, Doreen	    1002   1005
B293-71447 Sweeney, Meaghan	    1003   1006
B293-71447 Sweeney, Ian M	    1004   1007
B293-71445 Winn, Brian		    1005   1008
B293-71445 Winn, Brian		    1005   1009
US00011    Potter, Harry	    1006   1010
US00011    Potter, Harry	    1006   1011
US00011    Potter, Ginny	    1007   1012
US00011    Potter, Lily Luna	    1008   1013

  • You can confirm you’ve updated the correct PRICE_TYPE and RENTAL_ITEM_TYPE foreign keys values 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

    COL common_lookup_table  FORMAT A12 HEADING "Common|Lookup Table"
COL common_lookup_column FORMAT A18 HEADING "Common|Lookup Column"
COL common_lookup_code   FORMAT 999 HEADING "Common|Lookup|Code"
COL total_pk_count       FORMAT 999 HEADING "Foreign|Key|Count"
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        TO_NUMBER(cl.common_lookup_code) AS common_lookup_code
,        COUNT(*) AS total_pk_count
FROM     price p INNER JOIN common_lookup cl
ON       p.price_type = cl.common_lookup_id
AND      cl.common_lookup_table = 'PRICE'
AND      cl.common_lookup_column = 'PRICE_TYPE'
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_code
UNION ALL
SELECT   cl.common_lookup_table
,        cl.common_lookup_column
,        TO_NUMBER(cl.common_lookup_code) AS common_lookup_code
,        COUNT(*) AS total_pk_count
FROM     rental_item ri INNER JOIN common_lookup cl
ON       ri.rental_item_type = cl.common_lookup_id
AND      cl.common_lookup_table = 'RENTAL_ITEM'
AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE'
GROUP BY cl.common_lookup_table
,        cl.common_lookup_column
,        cl.common_lookup_code
ORDER BY 1, 2, 3;

    It should return the following values:

    				Common Foreign
Common	     Common		Lookup	   Key
Lookup Table Lookup Column	  Code	 Count
------------ ------------------ ------ -------
PRICE	     PRICE_TYPE 	     1	    45
PRICE	     PRICE_TYPE 	     3	    45
PRICE	     PRICE_TYPE 	     5	    45
RENTAL_ITEM  RENTAL_ITEM_TYPE	     1	     2
RENTAL_ITEM  RENTAL_ITEM_TYPE	     3	     1
RENTAL_ITEM  RENTAL_ITEM_TYPE	     5	    10

  • The most common error occurs when you insert the wrong start date in the PRICE table. The following query will help you see that type of error: 

    COL customer_name          FORMAT A20  HEADING "Contact|--------|Customer Name"
COL r_rental_id            FORMAT 9999 HEADING "Rental|------|Rental|ID #"
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"
COL needle                 FORMAT A11  HEADING "Rental|--------|Check Out|Date"
COL low_haystack           FORMAT A11  HEADING "Price|--------|Start|Date"
COL high_haystack          FORMAT A11  HEADING "Price|--------|End|Date"
SELECT   c.last_name||', '||c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' '||c.middle_name
         END AS customer_name
,        ri.rental_id AS ri_rental_id
,        p.amount
,        TO_NUMBER(cl2.common_lookup_code) AS price_type_code
,        TO_NUMBER(cl2.common_lookup_code) AS rental_item_type_code
,        p.start_date AS low_haystack
,        r.check_out_date AS needle
,        NVL(p.end_date,TRUNC(SYSDATE) + 1) AS high_haystack
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' FULL 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' RIGHT JOIN rental r
ON       ri.rental_id = r.rental_id FULL JOIN contact c
ON       r.customer_id = c.contact_id
WHERE    cl1.common_lookup_code = cl2.common_lookup_code
AND      p.active_flag = 'Y'  
AND NOT  r.check_out_date
           BETWEEN  p.start_date AND NVL(p.end_date,TRUNC(SYSDATE) + 1)
ORDER BY 2, 3;

    You made the mistake if the start date occurs after the rental start date, like the following rows for the Harry, Ginny, and Lily Potter:

    		     Rental		  Rental
		       Item  Price  Price   Item Price	     Rental	 Price
Contact 	     ------ ------ ------ ------ --------    --------	 --------
--------	     Rental	     Type   Type Start	     Check Out	 End
Customer Name	       ID # Amount   Code   Code Date	     Date	 Date
-------------------- ------ ------ ------ ------ ----------- ----------- -----------
Potter, Harry	       1006	 1	1      1 01-JUL-18   16-JUN-18	 17-JUN-18
Potter, Harry	       1006	 3	3      1 01-JUL-18   16-JUN-18	 17-JUN-18
Potter, Ginny	       1007	10	3      3 01-JUL-18   16-JUN-18	 17-JUN-18
Potter, Lily Luna      1008	15	5      5 01-JUL-18   16-JUN-18	 17-JUN-18

  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

19 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

  18. I’m also with Dean and Andrew, if lab 6 was followed correctly, then the data should be as they say, with Harry having 2 rentals, for $1 and $3.

    Cole

    16 Nov 17 at 12:12 am

  19. Implemented some time ago.

    michaelmclaughlin

    23 Dec 17 at 2:59 pm

Leave a Reply