Lab #8: Oracle
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.
- [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 aPRICE_S
sequence and call the sequence as a value in theSELECT
-list. (HINT: You call thePRICE_S
sequence like thisPRICE_S.NEXTVAL
inside theSELECT
-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 |
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. |
- [5 points] You should add the
NOT NULL
constraint to thePRICE_TYPE
column of thePRICE
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. |
- [5 points] The following query should update the
RENTAL_ITEM_PRICE
column for thirteen rows in theRENTAL_ITEM
table. It should return nine values of5
, and two values of3
, and one value of1
,10
and15
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 theUPDATE
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
, andTELEPHONE
tables. - Inserted the records into the
RENTAL
andRENTAL_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
, andTELEPHONE
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
RENTAL
RENTAL_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
andRENTAL_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
- [5 points] Add a not null constraint to the
RENTAL_ITEM_PRICE
column of theRENTAL_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. |
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
The veification query for step 3 should have the return results
This is because Harry Rented two items.
Jared Wrathall
4 Nov 14 at 9:51 pm
Jared, That’s a great catch.
michaelmclaughlin
6 Nov 14 at 1:18 pm
Jordan,
That’s a great catch. I’ve replaced line 11 with the following:
michaelmclaughlin
6 Nov 14 at 1:28 pm
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 theRENTAL_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 theRENTAL_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
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 aNN
constraint on it in step# 4, NOT onRENTAL_ITEM_TYPE
.Gabe Ventilla
7 Nov 14 at 10:38 pm
In the validation query for step# 3,
SYSDATE
is truncated. However, ifcheck_out_date
isNOT
truncated at the time a rental is inserted, then thecheck_out_date
will fall outside of the time frame ofBETWEEN p.start_date AND NVL(p.end_date,TRUNC(SYSDATE))
asTRUNC
will produce an earlier time than thecheck_out_date
by shaving off the time from the date…I believe that not truncatingSYSDATE
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
Gabe,
Your logic makes no sense unless I expect the corner use case as follows: The student enters the
ITEM_RELEASE_DATE
orRELEASE_DATE
column as theSYSDATE
value instead of theTRUNC(SYSDATE)
value. In that case, a nullEND_DATE
value would become aSYSDATE
value, and the laterSYSDATE
value shouldn’t present a problem because the earlierSYSDATE
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:michaelmclaughlin
8 Nov 14 at 11:39 pm
Gabe, I concur and have made the change.
michaelmclaughlin
9 Nov 14 at 1:53 am
Gabe, Thanks again. It’s fixed.
michaelmclaughlin
9 Nov 14 at 2:00 am
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
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.
RENTAL
and four new rows in theRENTAL_ITEM
tables. Two of the rows in theRENTAL_ITEM
table should link to the same row in theRENTAL
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
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
Andrew, That’s a great catch.
michaelmclaughlin
22 Mar 15 at 7:38 pm
Step 3 should show
Bryan Mcneil
4 Nov 15 at 2:44 pm
Bryan, You’re right and I’ve fixed it.
michaelmclaughlin
4 Nov 15 at 3:17 pm
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 theRental_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.Dean
5 Nov 16 at 12:13 am
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
Implemented some time ago.
michaelmclaughlin
23 Dec 17 at 2:59 pm