Database Tutorial

Course Tutorial Site

Site Admin

Lab #8: MySQL

with 7 comments

Lab #8: MySQL Assignment

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.

  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 provide a NULL value for the PRICE_ID inside the SELECT-list, or an override signature that excludes the PRICE_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

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      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)

  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
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)

  1. [5 points] The following query should update twelve rows in the RENTAL_ITEM table. It should return nine values of 5, and one value of 3, 10, and 15. 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)

  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
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)

Written by michaelmclaughlin

June 3rd, 2014 at 1:53 am

Posted in

7 Responses to 'Lab #8: MySQL'

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

  1. 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

  2. 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

  3. 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

  4. 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

  5. Elfre, Great catch! It’s fixed.

    michaelmclaughlin

    8 Nov 14 at 2:27 am

  6. 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

    ,        COUNT(*) AS "TOTAL"

    to

    ,        COUNT(CASE WHEN amount =  3 OR amount = 10 OR amount = 15 THEN 1 END) AS "TOTAL"

    Gerry

    10 Nov 14 at 9:59 pm

  7. 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

Leave a Reply