Lab #8: MySQL
You begin these steps after running the create_mysql_store_ri2.sql
, the seed_mysql_store_ri2.sql
, apply_mysql_lab5.sql
, apply_mysql_lab6.sql
, and apply_mysql_lab7.sql
scripts. You should create the apply_mysql_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_mysql_lab7.sql TEE apply_mysql_lab8.txt ... insert code here ... NOTEE |
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 provide aNULL
value for thePRICE_ID
inside theSELECT
-list, or an override signature that excludes thePRICE_ID
.
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 DATEDIFF(UTC_DATE(), 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 DATEDIFF(UTC_DATE(), 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 DATEDIFF(UTC_DATE(), 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 DATEDIFF(UTC_DATE(), 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 | 51 | 51 | 51 | 153 | | OLD N | 51 | 51 | 51 | 153 | | NEW Y | 3 | 3 | 3 | 9 | | NEW N | 0 | 0 | 0 | 0 | +-------+-------+-------+-------+-------+ 4 rows in set (0.03 sec) |
- [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 11 12 | SELECT table_name , ordinal_position , column_name , CASE WHEN is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END AS constraints , column_type FROM information_schema.columns WHERE table_name = 'price' AND column_name = 'price_type' ORDER BY ordinal_position; |
It should display the following results for a not null constrained column:
+------------+------------------+-------------+-------------+------------------+ | table_name | ordinal_position | column_name | constraints | column_type | +------------+------------------+-------------+-------------+------------------+ | price | 3 | price_type | NOT NULL | int(10) unsigned | +------------+------------------+-------------+-------------+------------------+ 1 row in set (0.00 sec) |
- [5 points] The following query should update twelve rows in the
RENTAL_ITEM
table. It should return nine values of5
, and one value of3
,10
, and15
. 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 | SELECT ri.rental_item_id , ri.rental_item_price , p.amount FROM price p INNER JOIN common_lookup cl1 ON p.price_type = cl1.common_lookup_id 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 INNER JOIN rental r ON ri.rental_id = r.rental_id WHERE cl1.common_lookup_code = cl2.common_lookup_code AND r.check_out_date BETWEEN p.start_date AND IFNULL(p.end_date, DATE_ADD(UTC_DATE(), INTERVAL 1 DAY)) ORDER BY 1; |
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_item_id | rental_item_price | amount | +----------------+-------------------+--------+ | 1001 | 5 | 5.00 | | 1002 | 5 | 5.00 | | 1003 | 5 | 5.00 | | 1004 | 5 | 5.00 | | 1005 | 5 | 5.00 | | 1006 | 5 | 5.00 | | 1007 | 5 | 5.00 | | 1008 | 5 | 5.00 | | 1009 | 5 | 5.00 | | 1010 | 3 | 3.00 | | 1011 | 3 | 3.00 | | 1012 | 10 | 10.00 | | 1013 | 15 | 15.00 | +----------------+-------------------+--------+ 13 rows in set (0.00 sec) |
Detailed Diagnostic Rules ↓
The following provides a detailed analysis query to help you discover what may be wrong.
Query Qualifies Primary and Foreign Key Rules:
It’s possible that you made a mistake earlier that may cause you to update an incorrect number of records. If that’s the case, several scenarios are possible. The following script helps you see how all the primary and foreign key values should align. It is the equivalent of a query for the UPDATE statement in this step.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT ri.rental_item_id , p.item_id AS Price_ItemID , ri.item_id AS RentalItem_ItemID , ri.rental_id AS RentalItem_RentalID , r.rental_id AS Rental_RentalID , p.price_type AS Price_PriceType , ri.rental_item_type AS RentalItem_RentalItemType , cl.common_lookup_type AS CommonLookup_CommonLookupType , p.start_date AS Price_StartDate , r.check_out_date AS Rental_CheckOutDate , IFNULL(p.end_date, DATE_ADD(UTC_DATE(), INTERVAL 1 DAY)) AS Price_EndDate , ri.rental_item_price AS RentalItem_RentalItemPrice , p.amount AS Price_Amount FROM price p INNER JOIN rental_item ri ON p.item_id = ri.item_id AND p.price_type = ri.rental_item_type INNER JOIN rental r ON ri.rental_id = r.rental_id INNER JOIN common_lookup cl ON p.price_type = cl.common_lookup_id WHERE r.check_out_date BETWEEN p.start_date AND IFNULL(p.end_date, DATE_ADD(UTC_DATE(), INTERVAL 1 DAY)) ORDER BY 1\G |
It should print the following diagnostic result set:
*************************** 1. row *************************** rental_item_id: 1001 Price_ItemID: 1002 RentalItem_ItemID: 1002 RentalItem_RentalID: 1001 Rental_RentalID: 1001 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 1999-06-04 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 2. row *************************** rental_item_id: 1002 Price_ItemID: 1004 RentalItem_ItemID: 1004 RentalItem_RentalID: 1001 Rental_RentalID: 1001 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 2002-06-16 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 3. row *************************** rental_item_id: 1003 Price_ItemID: 1005 RentalItem_ItemID: 1005 RentalItem_RentalID: 1001 Rental_RentalID: 1001 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 2005-06-19 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 4. row *************************** rental_item_id: 1004 Price_ItemID: 1016 RentalItem_ItemID: 1016 RentalItem_RentalID: 1002 Rental_RentalID: 1002 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 1998-02-05 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 5. row *************************** rental_item_id: 1005 Price_ItemID: 1022 RentalItem_ItemID: 1022 RentalItem_RentalID: 1002 Rental_RentalID: 1002 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 1998-06-15 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 6. row *************************** rental_item_id: 1006 Price_ItemID: 1019 RentalItem_ItemID: 1019 RentalItem_RentalID: 1003 Rental_RentalID: 1003 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 1992-01-11 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 7. row *************************** rental_item_id: 1007 Price_ItemID: 1014 RentalItem_ItemID: 1014 RentalItem_RentalID: 1004 Rental_RentalID: 1004 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 2006-05-29 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 8. row *************************** rental_item_id: 1008 Price_ItemID: 1007 RentalItem_ItemID: 1007 RentalItem_RentalID: 1005 Rental_RentalID: 1005 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 2003-08-24 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 9. row *************************** rental_item_id: 1009 Price_ItemID: 1001 RentalItem_ItemID: 1001 RentalItem_RentalID: 1005 Rental_RentalID: 1005 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 1990-04-02 Rental_CheckOutDate: 2014-06-09 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 5 Price_Amount: 5.00 *************************** 10. row *************************** rental_item_id: 1010 Price_ItemID: 1052 RentalItem_ItemID: 1052 RentalItem_RentalID: 1006 Rental_RentalID: 1006 Price_PriceType: 1022 RentalItem_RentalItemType: 1022 CommonLookup_CommonLookupType: 1-DAY RENTAL Price_StartDate: 2014-06-14 Rental_CheckOutDate: 2014-06-17 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 3 Price_Amount: 3.00 *************************** 11. row *************************** rental_item_id: 1011 Price_ItemID: 1033 RentalItem_ItemID: 1033 RentalItem_RentalID: 1006 Rental_RentalID: 1006 Price_PriceType: 1022 RentalItem_RentalItemType: 1022 CommonLookup_CommonLookupType: 1-DAY RENTAL Price_StartDate: 2000-09-29 Rental_CheckOutDate: 2014-06-17 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 1 Price_Amount: 1.00 *************************** 12. row *************************** rental_item_id: 1012 Price_ItemID: 1053 RentalItem_ItemID: 1053 RentalItem_RentalID: 1007 Rental_RentalID: 1007 Price_PriceType: 1023 RentalItem_RentalItemType: 1023 CommonLookup_CommonLookupType: 3-DAY RENTAL Price_StartDate: 2014-06-14 Rental_CheckOutDate: 2014-06-17 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 10 Price_Amount: 10.00 *************************** 13. row *************************** rental_item_id: 1013 Price_ItemID: 1054 RentalItem_ItemID: 1054 RentalItem_RentalID: 1008 Rental_RentalID: 1008 Price_PriceType: 1024 RentalItem_RentalItemType: 1024 CommonLookup_CommonLookupType: 5-DAY RENTAL Price_StartDate: 2014-06-14 Rental_CheckOutDate: 2014-06-17 Price_EndDate: 2014-06-18 RentalItem_RentalItemPrice: 15 Price_Amount: 15.00 13 rows in set (0.00 sec) |
- [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 11 12 | SELECT table_name , ordinal_position , column_name , CASE WHEN is_nullable = 'NO' THEN 'NOT NULL' ELSE '' END AS constraints , column_type FROM information_schema.columns WHERE table_name = 'rental_item' AND column_name = 'rental_item_price' ORDER BY ordinal_position; |
It should display the following results for a not null constrained column:
+-------------+------------------+-------------------+-------------+------------------+ | table_name | ordinal_position | column_name | constraints | column_type | +-------------+------------------+-------------------+-------------+------------------+ | rental_item | 9 | rental_item_price | NOT NULL | int(10) unsigned | +-------------+------------------+-------------------+-------------+------------------+ 9 rows in set (0.00 sec) 1 row in set (0.03 sec) |
price and price_type in step 2 in the select query need to be lower case.
Aaron Smith
10 Jun 14 at 7:21 pm
rental and rental_item_price in step 4 in the select query need to be lower case
Aaron Smith
10 Jun 14 at 9:15 pm
The new advanced diagnostic for the update statement in step 3 with its correct output will be a very good help without handing the answer completely. I think it will make for a lesson in tracing errors for those that end up with the errors at this point.
Matthew Mason
17 Jun 14 at 1:55 pm
Part 3 has error.
Item 1011 is a new rental, so your price should be $3 dollars. instead of $1
Elfre Valdes
7 Nov 14 at 7:27 pm
Elfre, Great catch! It’s fixed.
michaelmclaughlin
8 Nov 14 at 2:27 am
In step 1,
COUNT(*)
counts all rows regardless of whether there are any items that should be counted.The following change fixes the problem for me:
From
to
Gerry
10 Nov 14 at 9:59 pm
Never mind. I had a error in the query I was using to populate the price table.
Gerry
10 Nov 14 at 11:17 pm