Lab #7: MySQL at Database Tutorial

Lab #7: MySQL

Lab #7: MySQL Assignment

You begin these steps after running the cleanup_mysql.sql, create_mysql_store_ri2.sql, seed_mysql_store_ri2.sql, apply_mysql_lab5.sql, and apply_mysql_lab6.sql scripts. You should use the script provided in the downloaded instance or create a script like:

-- Run the prior lab script.
\. /home/student/Data/cit225/oracle/lab6/apply_mysql_lab6.sql
 
TEE apply_mysql_lab7.txt
 
... insert code here ...
 
NOTEE

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

[3 points] Insert two new rows into the COMMON_LOOKUP table to support the ACTIVE_FLAG column in the PRICE table.

Table Name: COMMON_LOOKUP
Table	Column	Code	Type	Meaning
PRICE	ACTIVE_FLAG	Y	YES	Yes
PRICE	ACTIVE_FLAG	N	NO	No

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

SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table = 'PRICE'
AND      common_lookup_column = 'ACTIVE_FLAG'
ORDER BY 1, 2, 3 DESC;

It should display the following results:

+---------------------+----------------------+--------------------+
| common_lookup_table | common_lookup_column | common_lookup_type |
+---------------------+----------------------+--------------------+
| PRICE               | ACTIVE_FLAG          | YES                |
| PRICE               | ACTIVE_FLAG          | NO                 |
+---------------------+----------------------+--------------------+
2 rows in set (0.01 sec)

[3 points] Insert three new rows into the COMMON_LOOKUP table to support the PRICE_TYPE column in the PRICE table, and three new rows into the COMMON_LOOKUP table to support the RENTAL_ITEM_TYPE column in the RENTAL_ITEM table.

Table Name: COMMON_LOOKUP
Table	Column	Code	Type	Meaning
PRICE	PRICE_TYPE	1	1-DAY RENTAL	1-Day Rental
PRICE	PRICE_TYPE	3	3-DAY RENTAL	3-Day Rental
PRICE	PRICE_TYPE	5	5-DAY RENTAL	5-Day Rental
RENTAL_ITEM	RENTAL_ITEM_TYPE	1	1-DAY RENTAL	1-Day Rental
RENTAL_ITEM	RENTAL_ITEM_TYPE	3	3-DAY RENTAL	3-Day Rental
RENTAL_ITEM	RENTAL_ITEM_TYPE	5	5-DAY RENTAL	5-Day Rental

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

SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table IN ('PRICE','RENTAL_ITEM')
AND      common_lookup_column IN ('PRICE_TYPE','RENTAL_ITEM_TYPE')
ORDER BY 1, 2, 3;

It should display the following results:

+---------------------+----------------------+--------------------+
| common_lookup_table | common_lookup_column | common_lookup_type |
+---------------------+----------------------+--------------------+
| PRICE               | PRICE_TYPE           | 1-DAY RENTAL       |
| PRICE               | PRICE_TYPE           | 3-DAY RENTAL       |
| PRICE               | PRICE_TYPE           | 5-DAY RENTAL       |
| RENTAL_ITEM         | RENTAL_ITEM_TYPE     | 5-DAY RENTAL       |
| RENTAL_ITEM         | RENTAL_ITEM_TYPE     | 3-DAY RENTAL       |
| RENTAL_ITEM         | RENTAL_ITEM_TYPE     | 1-DAY RENTAL       |
+---------------------+----------------------+--------------------+
6 rows in set (0.00 sec)

[4 points] Update the RENTAL_ITEM_TYPE column with values for all pre-existing rows, and add the NOT NULL constraint for the RENTAL_ITEM_TYPE column.

Update the RENTAL_ITEM_TYPE column with values for all pre-existing rows.

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

Hint for Step #3a

You should use the following to verify the status of the rental_item table as you begin this step:

SELECT   table_name
,        ordinal_position
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        column_type
FROM     information_schema.columns
WHERE    table_name = 'rental_item'
ORDER BY 2;

It should return the following:

+-------------+------------------+-------------------+----------+------------------+
| table_name  | ordinal_position | column_name       | nullable | column_type      |
+-------------+------------------+-------------------+----------+------------------+
| rental_item |                1 | rental_item_id    | NOT NULL | int(10) unsigned |
| rental_item |                2 | rental_id         | NOT NULL | int(10) unsigned |
| rental_item |                3 | item_id           | NOT NULL | int(10) unsigned |
| rental_item |                4 | created_by        | NOT NULL | int(10) unsigned |
| rental_item |                5 | creation_date     | NOT NULL | date             |
| rental_item |                6 | last_updated_by   | NOT NULL | int(10) unsigned |
| rental_item |                7 | last_update_date  | NOT NULL | date             |
| rental_item |                8 | rental_item_type  |          | int(10) unsigned |
| rental_item |                9 | rental_item_price |          | int(10) unsigned |
+-------------+------------------+-------------------+----------+------------------+
9 rows in set (0.05 sec)

The following shows you how to UPDATE existing rows with the proper values from the COMMON_LOOKUP table, but it has a casting error. If you fix the casting error it’ll work like a charm.

UPDATE   rental_item ri
SET      rental_item_type =
           (SELECT   cl.common_lookup_id
            FROM     common_lookup cl
            WHERE    cl.common_lookup_code =
              (SELECT   r.return_date - r.check_out_date
               FROM     rental r
               WHERE    r.rental_id = ri.rental_id)
            AND      cl.common_lookup_table = 'RENTAL_ITEM'
            AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE');

*NOTE: Please note that date calculations in MySQL are performed by functions, like the DATEDIFF(), and casting done by the CAST() function.

You should use the following to verify that the UPDATE statement worked successfully in this step:

SELECT   row_count
,        col_count
FROM    (SELECT   COUNT(*) AS row_count
         FROM     rental_item) rc CROSS JOIN
        (SELECT   COUNT(rental_item_type) AS col_count
         FROM     rental_item
         WHERE    rental_item_type IS NOT NULL) cc;

It should display the following results:

+-----------+-----------+
| row_count | col_count |
+-----------+-----------+
|        13 |        13 |
+-----------+-----------+
1 row in set (0.06 sec)

Change the RENTAL_ITEM_TYPE column of the RENTAL_ITEM table from a null allowed column to a not null constrained column.

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:

SELECT   table_name
,        ordinal_position
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        column_type
FROM     information_schema.columns
WHERE    table_name = 'rental_item'
AND      column_name = 'rental_item_type';

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

+-------------+------------------+------------------+----------+------------------+
| table_name  | ordinal_position | column_name      | nullable | column_type      |
+-------------+------------------+------------------+----------+------------------+
| rental_item |                8 | rental_item_type | NOT NULL | int(10) unsigned |
+-------------+------------------+------------------+----------+------------------+
1 row in set (0.02 sec)

Add a FK_RENTAL_ITEM_7 foreign key constraint on the RENTAL_ITEM_TYPE column in the RENTAL_ITEM table. Then, you can find the details of the foreign key for the RENTAL_ITEM_TYPE column with this query:

SELECT   CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint"
,        CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key"
,        CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key"
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
WHERE    tc.table_name = 'rental_item'
AND      kcu.column_name = 'rental_item_type'
AND      tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name
,        kcu.column_name\G

With the \G, the output prints with the columns on the left and the values on the right. You should see the following in your log:

*************************** 1. row ***************************
 Constraint: studentdb.rental_item.fk_rental_item_7
Foreign Key: studentdb.rental_item.rental_item_type
Primary Key: studentdb.common_lookup.common_lookup_id
1 row in set (0.08 sec)

[30 points with 1 SELECT statement with only a UNION ALL operator in a fabricating statement];
[20 points with 2 SELECT statements with one non-fabricating UNION or UNION ALL operator]; or
[10 points with 6 SELECT statements with two to five non-fabricating UNION or UNION ALL operator]
You need to write a SELECT statement that returns a data set that you can subsequently insert into the PRICE table. This is a complex problem because you must fabricate rows from a base set of rows, and then you must perform mathematical calculations with the CASE statement. (HINT: All computations are performed inside individual rows.)

Hint for Step #4

The SELECT-list (or query) draws data from the current ITEM table and adds values that let you calculate different value sets for the ACTIVE_FLAG and AMOUNT column. Specifically, you must add the following values:

ACTIVE_FLAG values: 'Y' and 'N'
AMOUNT values: '1', '3', '5', '10', and '15'

You can fabricate the data set with a query like the example below. The sample query returns 24 (the number of) rows from the ITEM table times 2 rows from the fabrication of 'Y' and 'N' values times 3 rows from the fabrication of the '1', '3', and '5' rental day values.

SELECT   ...
FROM     item i CROSS JOIN
        (SELECT 'Y' AS active_flag FROM dual
         UNION ALL
         SELECT 'N' AS active_flag FROM dual) af CROSS JOIN
        (SELECT '1' AS rental_days FROM dual
         UNION ALL
         SELECT '3' AS rental_days FROM dual
         UNION ALL
         SELECT '5' AS rental_days FROM dual) dr INNER JOIN
         common_lookup cl ON dr.rental_days = SUBSTR(cl.common_lookup_type,1,1)
WHERE    cl.common_lookup_table = 'RENTAL_ITEM'
AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE'
AND NOT  ...
ORDER BY 1, 2, 3;

The combination of valid ACTIVE_FLAG and AMOUNT columns are shown below:

Table Name: PRICE
Distribution of Rows
Active Flag	Rental Type	New Release		Old Release		Total
Active Flag	Rental Type	<= 30	> 30	<= 30	> 30	Total
Y	1	3			51	54
	3	3			51	54
	5	3			51	54
N	1			51		51
	3			51		51
	5			51		51
Total		9		63	63	135

The logic represented in this problem is a standard business system. All items must maintain a price at the time of sale or rental, and prices change over time. Our simplified video store problem has only two prices for each type of rental. One price is the active price and the other the inactive price. All active prices have a start and end date values except the current or active price, which as a rule always has a null end date value. You need an active and inactive price for the three types of rentals – 1, 3, and 5 day rentals. Therefore, each row in the ITEM table requires:

Three rows in the PRICE table when they’re within 30 days of their release date.
Six rows in the PRICE table when the current date is 31 days or more from their release date.

The following is illustrates the business and system logic that occurs after close of business on the 30^th from the video’s release date. It shows how you inactivate the original row and how you add a new active row.

Without a WHERE clause, the sample fabrication query returns 144 rows. The WHERE clause should filter out those rows where the ACTIVE_FLAG is 'N' and the current date minus 30 is less than the item’s release date. The SELECT-list uses a CASE statements to determine the correct ACTIVE_FLAG and AMOUNT based on the relationship of other column values.

Detailed Business Rules & System Logic ↓

You can view the detailed business rules and system logic by expanding this section.

Business Rules:

Rentals are never made in advance of today’s date.
Rental prices may change any day after midnight, typically through a batch process (spawned by a scheduler). A batch process will:

Update the existing active PRICE rows to inactive PRICE rows by making two changes: Set the ACTIVE_FLAG column value to 'N' and set the END_DATE column value to the START_DATE column value plus 30.
Insert new active PRICE rows, as follows: Use an ACTIVE_FLAG value of ‘Y’, a START_DATE that is 31 days greater than the related RELEASE_DATE column in the ITEM table, an END_DATE that is null, and a PRICE_TYPE that maps to a 1, 3, or 5 day rental.

All rental rates should be stored for tax, historical and analytical purposes.
Current rental prices are determined by the natural key of the PRICE table, which is the ITEM_ID, PRICE_TYPE, ACTIVE_FLAG, START_DATE, and END_DATE columns.
Only three types of rentals exist. They are a 1-Day, 3-Day, and 5-Day rental.
New release rental prices are $3, $10, and $15 for 1-Day, 3-Day, and 5-Day rentals respectively.
Old release rental prices are $1, $3, and $5 for 1-Day, 3-Day, and 5-Day rentals respectively.

System Logic:

The END_DATE column should be nullable. In an Oracle implementation, the END_DATE should also have a CHECK constraint that disallows any date but one that is 30 days later than the START_DATE. This requirement means that all existing date will need to be updated from a date timestamp to a truncated date timestamp value (midnight of any day). This could have been avoided had the original INSERT statements used a TRUNC(SYSDATE) syntax. When porting this to databases that don’t support a CHECK constraint, like MySQL, the column will be nullable but otherwise unconstrained.
The ACTIVE_FLAG column should be constrained to accept only a 'Y' or 'N' value, and a value should be required. In an Oracle implementation, this may be effected through a CHECK constraint. In a MySQL implementation, this may be effected through a ENUM data type.
The PRICE_TYPE column can only contain a valid value found in the COMMON_LOOKUP table. This requires a FOREIGN KEY constraint and a scalar subquery against the COMMON_LOOKUP table. Please note that the COMMON_LOOKUP table is also being re-engineered because of prior design problems, and you should defer updates to the PRICE table until the COMMON_LOOKUP table is fixed. You should also defer placing a FOREIGN KEY constraint until after Step #7 below (that’s when it’ll have values).
The AMOUNT can be determined by using formulas based on existing data. There are two compound cases:

When the ACTIVE_FLAG is 'Y' or 'N', and the current system date minus the RELEASE_DATE is less than 31, then the PRICE_TYPE of a 1-Day, 3-Day, or 5-Day rental type sets the amount values at $3, $10, and $15 respectively. A single PRICE row exists for each ITEM_ID and PRICE_TYPE combination when the current system date minus the RELEASE_DATE is less than 31 for new releases. Two PRICE table rows exist for each ITEM_ID and PRICE_TYPE combination when the current system date minus the RELEASE_DATE is greater than 30 because the ACTIVE_FLAG value can be either a ‘Y’ or ‘N’. All rentals have the ACTIVE_FLAG value of 'Y' when they’re new, and a value of 'N' when they’re 31 days from their release date.
When the ACTIVE_FLAG is 'Y' or 'N', and the current system date minus the RELEASE_DATE is greater than 30, then the PRICE_TYPE of a 1-Day, 3-Day, or 5-Day rental type sets the amount values at $1, $3, and $5 respectively. In this model, all releases where the current system date minus the RELEASE_DATE is greater than 30 days are old releases with the lower price amounts.

You can use the following to calculate whether a ITEM row belongs in the new or old price group. All ITEM rows that have a RELEASE_DATE value more than 30 days older than the current system date should have two PRICE rows for each type of rental (1-Day, 3-Day, and 5-Day rentals).

New Release Date Calculation

You calculate whether an item is a new release by the following formula:

WHERE DATE_SUB(UTC_DATE(), INTERVAL 30 DAY) < i.release_date

Old Release Date Calculation

You calculate whether an item is an old release by the following formula:

WHERE DATE_SUB(UTC_DATE(), INTERVAL 30 DAY) > i.release_date

Active Flag Calculation

You calculate whether an item has an active flag by the following formula:

WHERE af.active_flag = 'Y'

Inactive Flag Calculation

You calculate whether an item has an inactive flag by the following formula:

WHERE af.active_flag = 'N'

Exclusionary WHERE clause

You combine a new or old release date calculation with an active or inactive flag calculation. Here’s a pseudocode formula:

WHERE ((active or inactive flag comparison) AND (new or old release date gap))

This should help you visualize the use case for your query because you’ll use the query with an INSERT statement in Lab #8:

Table Name: PRICE
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
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
ACTIVE_FLAG	CHECK(‘Y’,’N’)			Text	1
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

The query displays the following results (please note that the PRICE_TYPE value in the result set is derived from the COMMON_LOOKUP_ID column value). The query results will differ for the three new rows in the ITEM table because the current dates always move forward. The ITEM_ID values for items 1052, 1053, and 1054 are those that will change over time.

+---------+-------------+------------+------------+------------+--------+
| item_id | active_flag | price_type | start_date | end_date   | amount |
+---------+-------------+------------+------------+------------+--------+
|    1001 | N           |       1022 | 1990-03-02 | 1990-04-01 |      3 |
|    1001 | N           |       1023 | 1990-03-02 | 1990-04-01 |     10 |
|    1001 | N           |       1024 | 1990-03-02 | 1990-04-01 |     15 |
|    1001 | Y           |       1022 | 1990-04-02 | NULL       |      1 |
|    1001 | Y           |       1023 | 1990-04-02 | NULL       |      3 |
|    1001 | Y           |       1024 | 1990-04-02 | NULL       |      5 |
|    1002 | N           |       1022 | 1999-05-04 | 1999-06-03 |      3 |
|    1002 | N           |       1023 | 1999-05-04 | 1999-06-03 |     10 |
|    1002 | N           |       1024 | 1999-05-04 | 1999-06-03 |     15 |
|    1002 | Y           |       1022 | 1999-06-04 | NULL       |      1 |
|    1002 | Y           |       1023 | 1999-06-04 | NULL       |      3 |
|    1002 | Y           |       1024 | 1999-06-04 | NULL       |      5 |
|    1003 | N           |       1022 | 2002-05-16 | 2002-06-15 |      3 |
|    1003 | N           |       1023 | 2002-05-16 | 2002-06-15 |     10 |
|    1003 | N           |       1024 | 2002-05-16 | 2002-06-15 |     15 |
|    1003 | Y           |       1022 | 2002-06-16 | NULL       |      1 |
|    1003 | Y           |       1023 | 2002-06-16 | NULL       |      3 |
|    1003 | Y           |       1024 | 2002-06-16 | NULL       |      5 |
|    1004 | N           |       1022 | 2002-05-16 | 2002-06-15 |      3 |
|    1004 | N           |       1023 | 2002-05-16 | 2002-06-15 |     10 |
|    1004 | N           |       1024 | 2002-05-16 | 2002-06-15 |     15 |
|    1004 | Y           |       1022 | 2002-06-16 | NULL       |      1 |
|    1004 | Y           |       1023 | 2002-06-16 | NULL       |      3 |
|    1004 | Y           |       1024 | 2002-06-16 | NULL       |      5 |
|    1005 | N           |       1022 | 2005-05-19 | 2005-06-18 |      3 |
|    1005 | N           |       1023 | 2005-05-19 | 2005-06-18 |     10 |
|    1005 | N           |       1024 | 2005-05-19 | 2005-06-18 |     15 |
|    1005 | Y           |       1022 | 2005-06-19 | NULL       |      1 |
|    1005 | Y           |       1023 | 2005-06-19 | NULL       |      3 |
|    1005 | Y           |       1024 | 2005-06-19 | NULL       |      5 |
|    1006 | N           |       1022 | 2002-05-16 | 2002-06-15 |      3 |
|    1006 | N           |       1023 | 2002-05-16 | 2002-06-15 |     10 |
|    1006 | N           |       1024 | 2002-05-16 | 2002-06-15 |     15 |
|    1006 | Y           |       1022 | 2002-06-16 | NULL       |      1 |
|    1006 | Y           |       1023 | 2002-06-16 | NULL       |      3 |
|    1006 | Y           |       1024 | 2002-06-16 | NULL       |      5 |
|    1007 | N           |       1022 | 2003-07-24 | 2003-08-23 |      3 |
|    1007 | N           |       1023 | 2003-07-24 | 2003-08-23 |     10 |
|    1007 | N           |       1024 | 2003-07-24 | 2003-08-23 |     15 |
|    1007 | Y           |       1022 | 2003-08-24 | NULL       |      1 |
|    1007 | Y           |       1023 | 2003-08-24 | NULL       |      3 |
|    1007 | Y           |       1024 | 2003-08-24 | NULL       |      5 |
|    1008 | N           |       1022 | 2003-06-30 | 2003-07-30 |      3 |
|    1008 | N           |       1023 | 2003-06-30 | 2003-07-30 |     10 |
|    1008 | N           |       1024 | 2003-06-30 | 2003-07-30 |     15 |
|    1008 | Y           |       1022 | 2003-07-31 | NULL       |      1 |
|    1008 | Y           |       1023 | 2003-07-31 | NULL       |      3 |
|    1008 | Y           |       1024 | 2003-07-31 | NULL       |      5 |
|    1009 | N           |       1022 | 2003-06-30 | 2003-07-30 |      3 |
|    1009 | N           |       1023 | 2003-06-30 | 2003-07-30 |     10 |
|    1009 | N           |       1024 | 2003-06-30 | 2003-07-30 |     15 |
|    1009 | Y           |       1022 | 2003-07-31 | NULL       |      1 |
|    1009 | Y           |       1023 | 2003-07-31 | NULL       |      3 |
|    1009 | Y           |       1024 | 2003-07-31 | NULL       |      5 |
|    1010 | N           |       1022 | 2003-11-17 | 2003-12-17 |      3 |
|    1010 | N           |       1023 | 2003-11-17 | 2003-12-17 |     10 |
|    1010 | N           |       1024 | 2003-11-17 | 2003-12-17 |     15 |
|    1010 | Y           |       1022 | 2003-12-18 | NULL       |      1 |
|    1010 | Y           |       1023 | 2003-12-18 | NULL       |      3 |
|    1010 | Y           |       1024 | 2003-12-18 | NULL       |      5 |
|    1011 | N           |       1022 | 2003-04-08 | 2003-05-08 |      3 |
|    1011 | N           |       1023 | 2003-04-08 | 2003-05-08 |     10 |
|    1011 | N           |       1024 | 2003-04-08 | 2003-05-08 |     15 |
|    1011 | Y           |       1022 | 2003-05-09 | NULL       |      1 |
|    1011 | Y           |       1023 | 2003-05-09 | NULL       |      3 |
|    1011 | Y           |       1024 | 2003-05-09 | NULL       |      5 |
|    1012 | N           |       1022 | 2004-11-15 | 2004-12-15 |      3 |
|    1012 | N           |       1023 | 2004-11-15 | 2004-12-15 |     10 |
|    1012 | N           |       1024 | 2004-11-15 | 2004-12-15 |     15 |
|    1012 | Y           |       1022 | 2004-12-16 | NULL       |      1 |
|    1012 | Y           |       1023 | 2004-12-16 | NULL       |      3 |
|    1012 | Y           |       1024 | 2004-12-16 | NULL       |      5 |
|    1013 | N           |       1022 | 2006-05-19 | 2006-06-18 |      3 |
|    1013 | N           |       1023 | 2006-05-19 | 2006-06-18 |     10 |
|    1013 | N           |       1024 | 2006-05-19 | 2006-06-18 |     15 |
|    1013 | Y           |       1022 | 2006-06-19 | NULL       |      1 |
|    1013 | Y           |       1023 | 2006-06-19 | NULL       |      3 |
|    1013 | Y           |       1024 | 2006-06-19 | NULL       |      5 |
|    1014 | N           |       1022 | 2006-04-28 | 2006-05-28 |      3 |
|    1014 | N           |       1023 | 2006-04-28 | 2006-05-28 |     10 |
|    1014 | N           |       1024 | 2006-04-28 | 2006-05-28 |     15 |
|    1014 | Y           |       1022 | 2006-05-29 | NULL       |      1 |
|    1014 | Y           |       1023 | 2006-05-29 | NULL       |      3 |
|    1014 | Y           |       1024 | 2006-05-29 | NULL       |      5 |
|    1015 | N           |       1022 | 1992-03-01 | 1992-03-31 |      3 |
|    1015 | N           |       1023 | 1992-03-01 | 1992-03-31 |     10 |
|    1015 | N           |       1024 | 1992-03-01 | 1992-03-31 |     15 |
|    1015 | Y           |       1022 | 1992-04-01 | NULL       |      1 |
|    1015 | Y           |       1023 | 1992-04-01 | NULL       |      3 |
|    1015 | Y           |       1024 | 1992-04-01 | NULL       |      5 |
|    1016 | N           |       1022 | 1998-01-05 | 1998-02-04 |      3 |
|    1016 | N           |       1023 | 1998-01-05 | 1998-02-04 |     10 |
|    1016 | N           |       1024 | 1998-01-05 | 1998-02-04 |     15 |
|    1016 | Y           |       1022 | 1998-02-05 | NULL       |      1 |
|    1016 | Y           |       1023 | 1998-02-05 | NULL       |      3 |
|    1016 | Y           |       1024 | 1998-02-05 | NULL       |      5 |
|    1017 | N           |       1022 | 1999-11-02 | 1999-12-02 |      3 |
|    1017 | N           |       1023 | 1999-11-02 | 1999-12-02 |     10 |
|    1017 | N           |       1024 | 1999-11-02 | 1999-12-02 |     15 |
|    1017 | Y           |       1022 | 1999-12-03 | NULL       |      1 |
|    1017 | Y           |       1023 | 1999-12-03 | NULL       |      3 |
|    1017 | Y           |       1024 | 1999-12-03 | NULL       |      5 |
|    1018 | N           |       1022 | 1994-06-28 | 1994-07-28 |      3 |
|    1018 | N           |       1023 | 1994-06-28 | 1994-07-28 |     10 |
|    1018 | N           |       1024 | 1994-06-28 | 1994-07-28 |     15 |
|    1018 | Y           |       1022 | 1994-07-29 | NULL       |      1 |
|    1018 | Y           |       1023 | 1994-07-29 | NULL       |      3 |
|    1018 | Y           |       1024 | 1994-07-29 | NULL       |      5 |
|    1019 | N           |       1022 | 1991-12-11 | 1992-01-10 |      3 |
|    1019 | N           |       1023 | 1991-12-11 | 1992-01-10 |     10 |
|    1019 | N           |       1024 | 1991-12-11 | 1992-01-10 |     15 |
|    1019 | Y           |       1022 | 1992-01-11 | NULL       |      1 |
|    1019 | Y           |       1023 | 1992-01-11 | NULL       |      3 |
|    1019 | Y           |       1024 | 1992-01-11 | NULL       |      5 |
|    1020 | N           |       1022 | 1992-12-04 | 1993-01-03 |      3 |
|    1020 | N           |       1023 | 1992-12-04 | 1993-01-03 |     10 |
|    1020 | N           |       1024 | 1992-12-04 | 1993-01-03 |     15 |
|    1020 | Y           |       1022 | 1993-01-04 | NULL       |      1 |
|    1020 | Y           |       1023 | 1993-01-04 | NULL       |      3 |
|    1020 | Y           |       1024 | 1993-01-04 | NULL       |      5 |
|    1021 | N           |       1022 | 2002-05-28 | 2002-06-27 |      3 |
|    1021 | N           |       1023 | 2002-05-28 | 2002-06-27 |     10 |
|    1021 | N           |       1024 | 2002-05-28 | 2002-06-27 |     15 |
|    1021 | Y           |       1022 | 2002-06-28 | NULL       |      1 |
|    1021 | Y           |       1023 | 2002-06-28 | NULL       |      3 |
|    1021 | Y           |       1024 | 2002-06-28 | NULL       |      5 |
|    1022 | N           |       1022 | 1998-05-15 | 1998-06-14 |      3 |
|    1022 | N           |       1023 | 1998-05-15 | 1998-06-14 |     10 |
|    1022 | N           |       1024 | 1998-05-15 | 1998-06-14 |     15 |
|    1022 | Y           |       1022 | 1998-06-15 | NULL       |      1 |
|    1022 | Y           |       1023 | 1998-06-15 | NULL       |      3 |
|    1022 | Y           |       1024 | 1998-06-15 | NULL       |      5 |
|    1023 | N           |       1022 | 2007-03-13 | 2007-04-12 |      3 |
|    1023 | N           |       1023 | 2007-03-13 | 2007-04-12 |     10 |
|    1023 | N           |       1024 | 2007-03-13 | 2007-04-12 |     15 |
|    1023 | Y           |       1022 | 2007-04-13 | NULL       |      1 |
|    1023 | Y           |       1023 | 2007-04-13 | NULL       |      3 |
|    1023 | Y           |       1024 | 2007-04-13 | NULL       |      5 |
|    1024 | N           |       1022 | 2007-03-13 | 2007-04-12 |      3 |
|    1024 | N           |       1023 | 2007-03-13 | 2007-04-12 |     10 |
|    1024 | N           |       1024 | 2007-03-13 | 2007-04-12 |     15 |
|    1024 | Y           |       1022 | 2007-04-13 | NULL       |      1 |
|    1024 | Y           |       1023 | 2007-04-13 | NULL       |      3 |
|    1024 | Y           |       1024 | 2007-04-13 | NULL       |      5 |
|    1025 | N           |       1022 | 2003-06-03 | 2003-07-03 |      3 |
|    1025 | N           |       1023 | 2003-06-03 | 2003-07-03 |     10 |
|    1025 | N           |       1024 | 2003-06-03 | 2003-07-03 |     15 |
|    1025 | Y           |       1022 | 2003-07-04 | NULL       |      1 |
|    1025 | Y           |       1023 | 2003-07-04 | NULL       |      3 |
|    1025 | Y           |       1024 | 2003-07-04 | NULL       |      5 |
|    1026 | N           |       1022 | 2003-06-03 | 2003-07-03 |      3 |
|    1026 | N           |       1023 | 2003-06-03 | 2003-07-03 |     10 |
|    1026 | N           |       1024 | 2003-06-03 | 2003-07-03 |     15 |
|    1026 | Y           |       1022 | 2003-07-04 | NULL       |      1 |
|    1026 | Y           |       1023 | 2003-07-04 | NULL       |      3 |
|    1026 | Y           |       1024 | 2003-07-04 | NULL       |      5 |
|    1027 | N           |       1022 | 2003-06-03 | 2003-07-03 |      3 |
|    1027 | N           |       1023 | 2003-06-03 | 2003-07-03 |     10 |
|    1027 | N           |       1024 | 2003-06-03 | 2003-07-03 |     15 |
|    1027 | Y           |       1022 | 2003-07-04 | NULL       |      1 |
|    1027 | Y           |       1023 | 2003-07-04 | NULL       |      3 |
|    1027 | Y           |       1024 | 2003-07-04 | NULL       |      5 |
|    1028 | N           |       1022 | 2003-06-03 | 2003-07-03 |      3 |
|    1028 | N           |       1023 | 2003-06-03 | 2003-07-03 |     10 |
|    1028 | N           |       1024 | 2003-06-03 | 2003-07-03 |     15 |
|    1028 | Y           |       1022 | 2003-07-04 | NULL       |      1 |
|    1028 | Y           |       1023 | 2003-07-04 | NULL       |      3 |
|    1028 | Y           |       1024 | 2003-07-04 | NULL       |      5 |
|    1029 | N           |       1022 | 2003-06-03 | 2003-07-03 |      3 |
|    1029 | N           |       1023 | 2003-06-03 | 2003-07-03 |     10 |
|    1029 | N           |       1024 | 2003-06-03 | 2003-07-03 |     15 |
|    1029 | Y           |       1022 | 2003-07-04 | NULL       |      1 |
|    1029 | Y           |       1023 | 2003-07-04 | NULL       |      3 |
|    1029 | Y           |       1024 | 2003-07-04 | NULL       |      5 |
|    1030 | N           |       1022 | 1998-05-13 | 1998-06-12 |      3 |
|    1030 | N           |       1023 | 1998-05-13 | 1998-06-12 |     10 |
|    1030 | N           |       1024 | 1998-05-13 | 1998-06-12 |     15 |
|    1030 | Y           |       1022 | 1998-06-13 | NULL       |      1 |
|    1030 | Y           |       1023 | 1998-06-13 | NULL       |      3 |
|    1030 | Y           |       1024 | 1998-06-13 | NULL       |      5 |
|    1031 | N           |       1022 | 2000-05-16 | 2000-06-15 |      3 |
|    1031 | N           |       1023 | 2000-05-16 | 2000-06-15 |     10 |
|    1031 | N           |       1024 | 2000-05-16 | 2000-06-15 |     15 |
|    1031 | Y           |       1022 | 2000-06-16 | NULL       |      1 |
|    1031 | Y           |       1023 | 2000-06-16 | NULL       |      3 |
|    1031 | Y           |       1024 | 2000-06-16 | NULL       |      5 |
|    1032 | N           |       1022 | 2007-05-22 | 2007-06-21 |      3 |
|    1032 | N           |       1023 | 2007-05-22 | 2007-06-21 |     10 |
|    1032 | N           |       1024 | 2007-05-22 | 2007-06-21 |     15 |
|    1032 | Y           |       1022 | 2007-06-22 | NULL       |      1 |
|    1032 | Y           |       1023 | 2007-06-22 | NULL       |      3 |
|    1032 | Y           |       1024 | 2007-06-22 | NULL       |      5 |
|    1033 | N           |       1022 | 2000-08-29 | 2000-09-28 |      3 |
|    1033 | N           |       1023 | 2000-08-29 | 2000-09-28 |     10 |
|    1033 | N           |       1024 | 2000-08-29 | 2000-09-28 |     15 |
|    1033 | Y           |       1022 | 2000-09-29 | NULL       |      1 |
|    1033 | Y           |       1023 | 2000-09-29 | NULL       |      3 |
|    1033 | Y           |       1024 | 2000-09-29 | NULL       |      5 |
|    1034 | N           |       1022 | 1999-10-05 | 1999-11-04 |      3 |
|    1034 | N           |       1023 | 1999-10-05 | 1999-11-04 |     10 |
|    1034 | N           |       1024 | 1999-10-05 | 1999-11-04 |     15 |
|    1034 | Y           |       1022 | 1999-11-05 | NULL       |      1 |
|    1034 | Y           |       1023 | 1999-11-05 | NULL       |      3 |
|    1034 | Y           |       1024 | 1999-11-05 | NULL       |      5 |
|    1035 | N           |       1022 | 1998-10-21 | 1998-11-20 |      3 |
|    1035 | N           |       1023 | 1998-10-21 | 1998-11-20 |     10 |
|    1035 | N           |       1024 | 1998-10-21 | 1998-11-20 |     15 |
|    1035 | Y           |       1022 | 1998-11-21 | NULL       |      1 |
|    1035 | Y           |       1023 | 1998-11-21 | NULL       |      3 |
|    1035 | Y           |       1024 | 1998-11-21 | NULL       |      5 |
|    1036 | N           |       1022 | 1998-10-21 | 1998-11-20 |      3 |
|    1036 | N           |       1023 | 1998-10-21 | 1998-11-20 |     10 |
|    1036 | N           |       1024 | 1998-10-21 | 1998-11-20 |     15 |
|    1036 | Y           |       1022 | 1998-11-21 | NULL       |      1 |
|    1036 | Y           |       1023 | 1998-11-21 | NULL       |      3 |
|    1036 | Y           |       1024 | 1998-11-21 | NULL       |      5 |
|    1037 | N           |       1022 | 2003-05-06 | 2003-06-05 |      3 |
|    1037 | N           |       1023 | 2003-05-06 | 2003-06-05 |     10 |
|    1037 | N           |       1024 | 2003-05-06 | 2003-06-05 |     15 |
|    1037 | Y           |       1022 | 2003-06-06 | NULL       |      1 |
|    1037 | Y           |       1023 | 2003-06-06 | NULL       |      3 |
|    1037 | Y           |       1024 | 2003-06-06 | NULL       |      5 |
|    1038 | N           |       1022 | 2002-05-28 | 2002-06-27 |      3 |
|    1038 | N           |       1023 | 2002-05-28 | 2002-06-27 |     10 |
|    1038 | N           |       1024 | 2002-05-28 | 2002-06-27 |     15 |
|    1038 | Y           |       1022 | 2002-06-28 | NULL       |      1 |
|    1038 | Y           |       1023 | 2002-06-28 | NULL       |      3 |
|    1038 | Y           |       1024 | 2002-06-28 | NULL       |      5 |
|    1039 | N           |       1022 | 2002-05-28 | 2002-06-27 |      3 |
|    1039 | N           |       1023 | 2002-05-28 | 2002-06-27 |     10 |
|    1039 | N           |       1024 | 2002-05-28 | 2002-06-27 |     15 |
|    1039 | Y           |       1022 | 2002-06-28 | NULL       |      1 |
|    1039 | Y           |       1023 | 2002-06-28 | NULL       |      3 |
|    1039 | Y           |       1024 | 2002-06-28 | NULL       |      5 |
|    1040 | N           |       1022 | 2002-05-28 | 2002-06-27 |      3 |
|    1040 | N           |       1023 | 2002-05-28 | 2002-06-27 |     10 |
|    1040 | N           |       1024 | 2002-05-28 | 2002-06-27 |     15 |
|    1040 | Y           |       1022 | 2002-06-28 | NULL       |      1 |
|    1040 | Y           |       1023 | 2002-06-28 | NULL       |      3 |
|    1040 | Y           |       1024 | 2002-06-28 | NULL       |      5 |
|    1041 | N           |       1022 | 2002-05-28 | 2002-06-27 |      3 |
|    1041 | N           |       1023 | 2002-05-28 | 2002-06-27 |     10 |
|    1041 | N           |       1024 | 2002-05-28 | 2002-06-27 |     15 |
|    1041 | Y           |       1022 | 2002-06-28 | NULL       |      1 |
|    1041 | Y           |       1023 | 2002-06-28 | NULL       |      3 |
|    1041 | Y           |       1024 | 2002-06-28 | NULL       |      5 |
|    1042 | N           |       1022 | 2004-10-23 | 2004-11-22 |      3 |
|    1042 | N           |       1023 | 2004-10-23 | 2004-11-22 |     10 |
|    1042 | N           |       1024 | 2004-10-23 | 2004-11-22 |     15 |
|    1042 | Y           |       1022 | 2004-11-23 | NULL       |      1 |
|    1042 | Y           |       1023 | 2004-11-23 | NULL       |      3 |
|    1042 | Y           |       1024 | 2004-11-23 | NULL       |      5 |
|    1043 | N           |       1022 | 2004-10-23 | 2004-11-22 |      3 |
|    1043 | N           |       1023 | 2004-10-23 | 2004-11-22 |     10 |
|    1043 | N           |       1024 | 2004-10-23 | 2004-11-22 |     15 |
|    1043 | Y           |       1022 | 2004-11-23 | NULL       |      1 |
|    1043 | Y           |       1023 | 2004-11-23 | NULL       |      3 |
|    1043 | Y           |       1024 | 2004-11-23 | NULL       |      5 |
|    1044 | N           |       1022 | 2006-03-07 | 2006-04-06 |      3 |
|    1044 | N           |       1023 | 2006-03-07 | 2006-04-06 |     10 |
|    1044 | N           |       1024 | 2006-03-07 | 2006-04-06 |     15 |
|    1044 | Y           |       1022 | 2006-04-07 | NULL       |      1 |
|    1044 | Y           |       1023 | 2006-04-07 | NULL       |      3 |
|    1044 | Y           |       1024 | 2006-04-07 | NULL       |      5 |
|    1045 | N           |       1022 | 2006-03-07 | 2006-04-06 |      3 |
|    1045 | N           |       1023 | 2006-03-07 | 2006-04-06 |     10 |
|    1045 | N           |       1024 | 2006-03-07 | 2006-04-06 |     15 |
|    1045 | Y           |       1022 | 2006-04-07 | NULL       |      1 |
|    1045 | Y           |       1023 | 2006-04-07 | NULL       |      3 |
|    1045 | Y           |       1024 | 2006-04-07 | NULL       |      5 |
|    1046 | N           |       1022 | 2006-03-07 | 2006-04-06 |      3 |
|    1046 | N           |       1023 | 2006-03-07 | 2006-04-06 |     10 |
|    1046 | N           |       1024 | 2006-03-07 | 2006-04-06 |     15 |
|    1046 | Y           |       1022 | 2006-04-07 | NULL       |      1 |
|    1046 | Y           |       1023 | 2006-04-07 | NULL       |      3 |
|    1046 | Y           |       1024 | 2006-04-07 | NULL       |      5 |
|    1047 | N           |       1022 | 2007-12-11 | 2008-01-10 |      3 |
|    1047 | N           |       1023 | 2007-12-11 | 2008-01-10 |     10 |
|    1047 | N           |       1024 | 2007-12-11 | 2008-01-10 |     15 |
|    1047 | Y           |       1022 | 2008-01-11 | NULL       |      1 |
|    1047 | Y           |       1023 | 2008-01-11 | NULL       |      3 |
|    1047 | Y           |       1024 | 2008-01-11 | NULL       |      5 |
|    1048 | N           |       1022 | 2009-12-08 | 2010-01-07 |      3 |
|    1048 | N           |       1023 | 2009-12-08 | 2010-01-07 |     10 |
|    1048 | N           |       1024 | 2009-12-08 | 2010-01-07 |     15 |
|    1048 | Y           |       1022 | 2010-01-08 | NULL       |      1 |
|    1048 | Y           |       1023 | 2010-01-08 | NULL       |      3 |
|    1048 | Y           |       1024 | 2010-01-08 | NULL       |      5 |
|    1049 | N           |       1022 | 2011-10-15 | 2011-11-14 |      3 |
|    1049 | N           |       1023 | 2011-10-15 | 2011-11-14 |     10 |
|    1049 | N           |       1024 | 2011-10-15 | 2011-11-14 |     15 |
|    1049 | Y           |       1022 | 2011-11-15 | NULL       |      1 |
|    1049 | Y           |       1023 | 2011-11-15 | NULL       |      3 |
|    1049 | Y           |       1024 | 2011-11-15 | NULL       |      5 |
|    1050 | N           |       1022 | 2011-11-11 | 2011-12-11 |      3 |
|    1050 | N           |       1023 | 2011-11-11 | 2011-12-11 |     10 |
|    1050 | N           |       1024 | 2011-11-11 | 2011-12-11 |     15 |
|    1050 | Y           |       1022 | 2011-12-12 | NULL       |      1 |
|    1050 | Y           |       1023 | 2011-12-12 | NULL       |      3 |
|    1050 | Y           |       1024 | 2011-12-12 | NULL       |      5 |
|    1051 | N           |       1022 | 2011-11-11 | 2011-12-11 |      3 |
|    1051 | N           |       1023 | 2011-11-11 | 2011-12-11 |     10 |
|    1051 | N           |       1024 | 2011-11-11 | 2011-12-11 |     15 |
|    1051 | Y           |       1022 | 2011-12-12 | NULL       |      1 |
|    1051 | Y           |       1023 | 2011-12-12 | NULL       |      3 |
|    1051 | Y           |       1024 | 2011-12-12 | NULL       |      5 |
|    1052 | Y           |       1022 | 2014-07-13 | NULL       |      3 |
|    1052 | Y           |       1023 | 2014-07-13 | NULL       |     10 |
|    1052 | Y           |       1024 | 2014-07-13 | NULL       |     15 |
|    1053 | Y           |       1022 | 2014-07-13 | NULL       |      3 |
|    1053 | Y           |       1023 | 2014-07-13 | NULL       |     10 |
|    1053 | Y           |       1024 | 2014-07-13 | NULL       |     15 |
|    1054 | Y           |       1022 | 2014-07-13 | NULL       |      3 |
|    1054 | Y           |       1023 | 2014-07-13 | NULL       |     10 |
|    1054 | Y           |       1024 | 2014-07-13 | NULL       |     15 |
+---------+-------------+------------+------------+------------+--------+
315 rows in set (0.01 sec)

+---------+-------------+------------+------------+------------+--------+ | item_id | active_flag | price_type | start_date | end_date | amount | +---------+-------------+------------+------------+------------+--------+ | 1001 | N | 1022 | 1990-03-02 | 1990-04-01 | 3 | | 1001 | N | 1023 | 1990-03-02 | 1990-04-01 | 10 | | 1001 | N | 1024 | 1990-03-02 | 1990-04-01 | 15 | | 1001 | Y | 1022 | 1990-04-02 | NULL | 1 | | 1001 | Y | 1023 | 1990-04-02 | NULL | 3 | | 1001 | Y | 1024 | 1990-04-02 | NULL | 5 | | 1002 | N | 1022 | 1999-05-04 | 1999-06-03 | 3 | | 1002 | N | 1023 | 1999-05-04 | 1999-06-03 | 10 | | 1002 | N | 1024 | 1999-05-04 | 1999-06-03 | 15 | | 1002 | Y | 1022 | 1999-06-04 | NULL | 1 | | 1002 | Y | 1023 | 1999-06-04 | NULL | 3 | | 1002 | Y | 1024 | 1999-06-04 | NULL | 5 | | 1003 | N | 1022 | 2002-05-16 | 2002-06-15 | 3 | | 1003 | N | 1023 | 2002-05-16 | 2002-06-15 | 10 | | 1003 | N | 1024 | 2002-05-16 | 2002-06-15 | 15 | | 1003 | Y | 1022 | 2002-06-16 | NULL | 1 | | 1003 | Y | 1023 | 2002-06-16 | NULL | 3 | | 1003 | Y | 1024 | 2002-06-16 | NULL | 5 | | 1004 | N | 1022 | 2002-05-16 | 2002-06-15 | 3 | | 1004 | N | 1023 | 2002-05-16 | 2002-06-15 | 10 | | 1004 | N | 1024 | 2002-05-16 | 2002-06-15 | 15 | | 1004 | Y | 1022 | 2002-06-16 | NULL | 1 | | 1004 | Y | 1023 | 2002-06-16 | NULL | 3 | | 1004 | Y | 1024 | 2002-06-16 | NULL | 5 | | 1005 | N | 1022 | 2005-05-19 | 2005-06-18 | 3 | | 1005 | N | 1023 | 2005-05-19 | 2005-06-18 | 10 | | 1005 | N | 1024 | 2005-05-19 | 2005-06-18 | 15 | | 1005 | Y | 1022 | 2005-06-19 | NULL | 1 | | 1005 | Y | 1023 | 2005-06-19 | NULL | 3 | | 1005 | Y | 1024 | 2005-06-19 | NULL | 5 | | 1006 | N | 1022 | 2002-05-16 | 2002-06-15 | 3 | | 1006 | N | 1023 | 2002-05-16 | 2002-06-15 | 10 | | 1006 | N | 1024 | 2002-05-16 | 2002-06-15 | 15 | | 1006 | Y | 1022 | 2002-06-16 | NULL | 1 | | 1006 | Y | 1023 | 2002-06-16 | NULL | 3 | | 1006 | Y | 1024 | 2002-06-16 | NULL | 5 | | 1007 | N | 1022 | 2003-07-24 | 2003-08-23 | 3 | | 1007 | N | 1023 | 2003-07-24 | 2003-08-23 | 10 | | 1007 | N | 1024 | 2003-07-24 | 2003-08-23 | 15 | | 1007 | Y | 1022 | 2003-08-24 | NULL | 1 | | 1007 | Y | 1023 | 2003-08-24 | NULL | 3 | | 1007 | Y | 1024 | 2003-08-24 | NULL | 5 | | 1008 | N | 1022 | 2003-06-30 | 2003-07-30 | 3 | | 1008 | N | 1023 | 2003-06-30 | 2003-07-30 | 10 | | 1008 | N | 1024 | 2003-06-30 | 2003-07-30 | 15 | | 1008 | Y | 1022 | 2003-07-31 | NULL | 1 | | 1008 | Y | 1023 | 2003-07-31 | NULL | 3 | | 1008 | Y | 1024 | 2003-07-31 | NULL | 5 | | 1009 | N | 1022 | 2003-06-30 | 2003-07-30 | 3 | | 1009 | N | 1023 | 2003-06-30 | 2003-07-30 | 10 | | 1009 | N | 1024 | 2003-06-30 | 2003-07-30 | 15 | | 1009 | Y | 1022 | 2003-07-31 | NULL | 1 | | 1009 | Y | 1023 | 2003-07-31 | NULL | 3 | | 1009 | Y | 1024 | 2003-07-31 | NULL | 5 | | 1010 | N | 1022 | 2003-11-17 | 2003-12-17 | 3 | | 1010 | N | 1023 | 2003-11-17 | 2003-12-17 | 10 | | 1010 | N | 1024 | 2003-11-17 | 2003-12-17 | 15 | | 1010 | Y | 1022 | 2003-12-18 | NULL | 1 | | 1010 | Y | 1023 | 2003-12-18 | NULL | 3 | | 1010 | Y | 1024 | 2003-12-18 | NULL | 5 | | 1011 | N | 1022 | 2003-04-08 | 2003-05-08 | 3 | | 1011 | N | 1023 | 2003-04-08 | 2003-05-08 | 10 | | 1011 | N | 1024 | 2003-04-08 | 2003-05-08 | 15 | | 1011 | Y | 1022 | 2003-05-09 | NULL | 1 | | 1011 | Y | 1023 | 2003-05-09 | NULL | 3 | | 1011 | Y | 1024 | 2003-05-09 | NULL | 5 | | 1012 | N | 1022 | 2004-11-15 | 2004-12-15 | 3 | | 1012 | N | 1023 | 2004-11-15 | 2004-12-15 | 10 | | 1012 | N | 1024 | 2004-11-15 | 2004-12-15 | 15 | | 1012 | Y | 1022 | 2004-12-16 | NULL | 1 | | 1012 | Y | 1023 | 2004-12-16 | NULL | 3 | | 1012 | Y | 1024 | 2004-12-16 | NULL | 5 | | 1013 | N | 1022 | 2006-05-19 | 2006-06-18 | 3 | | 1013 | N | 1023 | 2006-05-19 | 2006-06-18 | 10 | | 1013 | N | 1024 | 2006-05-19 | 2006-06-18 | 15 | | 1013 | Y | 1022 | 2006-06-19 | NULL | 1 | | 1013 | Y | 1023 | 2006-06-19 | NULL | 3 | | 1013 | Y | 1024 | 2006-06-19 | NULL | 5 | | 1014 | N | 1022 | 2006-04-28 | 2006-05-28 | 3 | | 1014 | N | 1023 | 2006-04-28 | 2006-05-28 | 10 | | 1014 | N | 1024 | 2006-04-28 | 2006-05-28 | 15 | | 1014 | Y | 1022 | 2006-05-29 | NULL | 1 | | 1014 | Y | 1023 | 2006-05-29 | NULL | 3 | | 1014 | Y | 1024 | 2006-05-29 | NULL | 5 | | 1015 | N | 1022 | 1992-03-01 | 1992-03-31 | 3 | | 1015 | N | 1023 | 1992-03-01 | 1992-03-31 | 10 | | 1015 | N | 1024 | 1992-03-01 | 1992-03-31 | 15 | | 1015 | Y | 1022 | 1992-04-01 | NULL | 1 | | 1015 | Y | 1023 | 1992-04-01 | NULL | 3 | | 1015 | Y | 1024 | 1992-04-01 | NULL | 5 | | 1016 | N | 1022 | 1998-01-05 | 1998-02-04 | 3 | | 1016 | N | 1023 | 1998-01-05 | 1998-02-04 | 10 | | 1016 | N | 1024 | 1998-01-05 | 1998-02-04 | 15 | | 1016 | Y | 1022 | 1998-02-05 | NULL | 1 | | 1016 | Y | 1023 | 1998-02-05 | NULL | 3 | | 1016 | Y | 1024 | 1998-02-05 | NULL | 5 | | 1017 | N | 1022 | 1999-11-02 | 1999-12-02 | 3 | | 1017 | N | 1023 | 1999-11-02 | 1999-12-02 | 10 | | 1017 | N | 1024 | 1999-11-02 | 1999-12-02 | 15 | | 1017 | Y | 1022 | 1999-12-03 | NULL | 1 | | 1017 | Y | 1023 | 1999-12-03 | NULL | 3 | | 1017 | Y | 1024 | 1999-12-03 | NULL | 5 | | 1018 | N | 1022 | 1994-06-28 | 1994-07-28 | 3 | | 1018 | N | 1023 | 1994-06-28 | 1994-07-28 | 10 | | 1018 | N | 1024 | 1994-06-28 | 1994-07-28 | 15 | | 1018 | Y | 1022 | 1994-07-29 | NULL | 1 | | 1018 | Y | 1023 | 1994-07-29 | NULL | 3 | | 1018 | Y | 1024 | 1994-07-29 | NULL | 5 | | 1019 | N | 1022 | 1991-12-11 | 1992-01-10 | 3 | | 1019 | N | 1023 | 1991-12-11 | 1992-01-10 | 10 | | 1019 | N | 1024 | 1991-12-11 | 1992-01-10 | 15 | | 1019 | Y | 1022 | 1992-01-11 | NULL | 1 | | 1019 | Y | 1023 | 1992-01-11 | NULL | 3 | | 1019 | Y | 1024 | 1992-01-11 | NULL | 5 | | 1020 | N | 1022 | 1992-12-04 | 1993-01-03 | 3 | | 1020 | N | 1023 | 1992-12-04 | 1993-01-03 | 10 | | 1020 | N | 1024 | 1992-12-04 | 1993-01-03 | 15 | | 1020 | Y | 1022 | 1993-01-04 | NULL | 1 | | 1020 | Y | 1023 | 1993-01-04 | NULL | 3 | | 1020 | Y | 1024 | 1993-01-04 | NULL | 5 | | 1021 | N | 1022 | 2002-05-28 | 2002-06-27 | 3 | | 1021 | N | 1023 | 2002-05-28 | 2002-06-27 | 10 | | 1021 | N | 1024 | 2002-05-28 | 2002-06-27 | 15 | | 1021 | Y | 1022 | 2002-06-28 | NULL | 1 | | 1021 | Y | 1023 | 2002-06-28 | NULL | 3 | | 1021 | Y | 1024 | 2002-06-28 | NULL | 5 | | 1022 | N | 1022 | 1998-05-15 | 1998-06-14 | 3 | | 1022 | N | 1023 | 1998-05-15 | 1998-06-14 | 10 | | 1022 | N | 1024 | 1998-05-15 | 1998-06-14 | 15 | | 1022 | Y | 1022 | 1998-06-15 | NULL | 1 | | 1022 | Y | 1023 | 1998-06-15 | NULL | 3 | | 1022 | Y | 1024 | 1998-06-15 | NULL | 5 | | 1023 | N | 1022 | 2007-03-13 | 2007-04-12 | 3 | | 1023 | N | 1023 | 2007-03-13 | 2007-04-12 | 10 | | 1023 | N | 1024 | 2007-03-13 | 2007-04-12 | 15 | | 1023 | Y | 1022 | 2007-04-13 | NULL | 1 | | 1023 | Y | 1023 | 2007-04-13 | NULL | 3 | | 1023 | Y | 1024 | 2007-04-13 | NULL | 5 | | 1024 | N | 1022 | 2007-03-13 | 2007-04-12 | 3 | | 1024 | N | 1023 | 2007-03-13 | 2007-04-12 | 10 | | 1024 | N | 1024 | 2007-03-13 | 2007-04-12 | 15 | | 1024 | Y | 1022 | 2007-04-13 | NULL | 1 | | 1024 | Y | 1023 | 2007-04-13 | NULL | 3 | | 1024 | Y | 1024 | 2007-04-13 | NULL | 5 | | 1025 | N | 1022 | 2003-06-03 | 2003-07-03 | 3 | | 1025 | N | 1023 | 2003-06-03 | 2003-07-03 | 10 | | 1025 | N | 1024 | 2003-06-03 | 2003-07-03 | 15 | | 1025 | Y | 1022 | 2003-07-04 | NULL | 1 | | 1025 | Y | 1023 | 2003-07-04 | NULL | 3 | | 1025 | Y | 1024 | 2003-07-04 | NULL | 5 | | 1026 | N | 1022 | 2003-06-03 | 2003-07-03 | 3 | | 1026 | N | 1023 | 2003-06-03 | 2003-07-03 | 10 | | 1026 | N | 1024 | 2003-06-03 | 2003-07-03 | 15 | | 1026 | Y | 1022 | 2003-07-04 | NULL | 1 | | 1026 | Y | 1023 | 2003-07-04 | NULL | 3 | | 1026 | Y | 1024 | 2003-07-04 | NULL | 5 | | 1027 | N | 1022 | 2003-06-03 | 2003-07-03 | 3 | | 1027 | N | 1023 | 2003-06-03 | 2003-07-03 | 10 | | 1027 | N | 1024 | 2003-06-03 | 2003-07-03 | 15 | | 1027 | Y | 1022 | 2003-07-04 | NULL | 1 | | 1027 | Y | 1023 | 2003-07-04 | NULL | 3 | | 1027 | Y | 1024 | 2003-07-04 | NULL | 5 | | 1028 | N | 1022 | 2003-06-03 | 2003-07-03 | 3 | | 1028 | N | 1023 | 2003-06-03 | 2003-07-03 | 10 | | 1028 | N | 1024 | 2003-06-03 | 2003-07-03 | 15 | | 1028 | Y | 1022 | 2003-07-04 | NULL | 1 | | 1028 | Y | 1023 | 2003-07-04 | NULL | 3 | | 1028 | Y | 1024 | 2003-07-04 | NULL | 5 | | 1029 | N | 1022 | 2003-06-03 | 2003-07-03 | 3 | | 1029 | N | 1023 | 2003-06-03 | 2003-07-03 | 10 | | 1029 | N | 1024 | 2003-06-03 | 2003-07-03 | 15 | | 1029 | Y | 1022 | 2003-07-04 | NULL | 1 | | 1029 | Y | 1023 | 2003-07-04 | NULL | 3 | | 1029 | Y | 1024 | 2003-07-04 | NULL | 5 | | 1030 | N | 1022 | 1998-05-13 | 1998-06-12 | 3 | | 1030 | N | 1023 | 1998-05-13 | 1998-06-12 | 10 | | 1030 | N | 1024 | 1998-05-13 | 1998-06-12 | 15 | | 1030 | Y | 1022 | 1998-06-13 | NULL | 1 | | 1030 | Y | 1023 | 1998-06-13 | NULL | 3 | | 1030 | Y | 1024 | 1998-06-13 | NULL | 5 | | 1031 | N | 1022 | 2000-05-16 | 2000-06-15 | 3 | | 1031 | N | 1023 | 2000-05-16 | 2000-06-15 | 10 | | 1031 | N | 1024 | 2000-05-16 | 2000-06-15 | 15 | | 1031 | Y | 1022 | 2000-06-16 | NULL | 1 | | 1031 | Y | 1023 | 2000-06-16 | NULL | 3 | | 1031 | Y | 1024 | 2000-06-16 | NULL | 5 | | 1032 | N | 1022 | 2007-05-22 | 2007-06-21 | 3 | | 1032 | N | 1023 | 2007-05-22 | 2007-06-21 | 10 | | 1032 | N | 1024 | 2007-05-22 | 2007-06-21 | 15 | | 1032 | Y | 1022 | 2007-06-22 | NULL | 1 | | 1032 | Y | 1023 | 2007-06-22 | NULL | 3 | | 1032 | Y | 1024 | 2007-06-22 | NULL | 5 | | 1033 | N | 1022 | 2000-08-29 | 2000-09-28 | 3 | | 1033 | N | 1023 | 2000-08-29 | 2000-09-28 | 10 | | 1033 | N | 1024 | 2000-08-29 | 2000-09-28 | 15 | | 1033 | Y | 1022 | 2000-09-29 | NULL | 1 | | 1033 | Y | 1023 | 2000-09-29 | NULL | 3 | | 1033 | Y | 1024 | 2000-09-29 | NULL | 5 | | 1034 | N | 1022 | 1999-10-05 | 1999-11-04 | 3 | | 1034 | N | 1023 | 1999-10-05 | 1999-11-04 | 10 | | 1034 | N | 1024 | 1999-10-05 | 1999-11-04 | 15 | | 1034 | Y | 1022 | 1999-11-05 | NULL | 1 | | 1034 | Y | 1023 | 1999-11-05 | NULL | 3 | | 1034 | Y | 1024 | 1999-11-05 | NULL | 5 | | 1035 | N | 1022 | 1998-10-21 | 1998-11-20 | 3 | | 1035 | N | 1023 | 1998-10-21 | 1998-11-20 | 10 | | 1035 | N | 1024 | 1998-10-21 | 1998-11-20 | 15 | | 1035 | Y | 1022 | 1998-11-21 | NULL | 1 | | 1035 | Y | 1023 | 1998-11-21 | NULL | 3 | | 1035 | Y | 1024 | 1998-11-21 | NULL | 5 | | 1036 | N | 1022 | 1998-10-21 | 1998-11-20 | 3 | | 1036 | N | 1023 | 1998-10-21 | 1998-11-20 | 10 | | 1036 | N | 1024 | 1998-10-21 | 1998-11-20 | 15 | | 1036 | Y | 1022 | 1998-11-21 | NULL | 1 | | 1036 | Y | 1023 | 1998-11-21 | NULL | 3 | | 1036 | Y | 1024 | 1998-11-21 | NULL | 5 | | 1037 | N | 1022 | 2003-05-06 | 2003-06-05 | 3 | | 1037 | N | 1023 | 2003-05-06 | 2003-06-05 | 10 | | 1037 | N | 1024 | 2003-05-06 | 2003-06-05 | 15 | | 1037 | Y | 1022 | 2003-06-06 | NULL | 1 | | 1037 | Y | 1023 | 2003-06-06 | NULL | 3 | | 1037 | Y | 1024 | 2003-06-06 | NULL | 5 | | 1038 | N | 1022 | 2002-05-28 | 2002-06-27 | 3 | | 1038 | N | 1023 | 2002-05-28 | 2002-06-27 | 10 | | 1038 | N | 1024 | 2002-05-28 | 2002-06-27 | 15 | | 1038 | Y | 1022 | 2002-06-28 | NULL | 1 | | 1038 | Y | 1023 | 2002-06-28 | NULL | 3 | | 1038 | Y | 1024 | 2002-06-28 | NULL | 5 | | 1039 | N | 1022 | 2002-05-28 | 2002-06-27 | 3 | | 1039 | N | 1023 | 2002-05-28 | 2002-06-27 | 10 | | 1039 | N | 1024 | 2002-05-28 | 2002-06-27 | 15 | | 1039 | Y | 1022 | 2002-06-28 | NULL | 1 | | 1039 | Y | 1023 | 2002-06-28 | NULL | 3 | | 1039 | Y | 1024 | 2002-06-28 | NULL | 5 | | 1040 | N | 1022 | 2002-05-28 | 2002-06-27 | 3 | | 1040 | N | 1023 | 2002-05-28 | 2002-06-27 | 10 | | 1040 | N | 1024 | 2002-05-28 | 2002-06-27 | 15 | | 1040 | Y | 1022 | 2002-06-28 | NULL | 1 | | 1040 | Y | 1023 | 2002-06-28 | NULL | 3 | | 1040 | Y | 1024 | 2002-06-28 | NULL | 5 | | 1041 | N | 1022 | 2002-05-28 | 2002-06-27 | 3 | | 1041 | N | 1023 | 2002-05-28 | 2002-06-27 | 10 | | 1041 | N | 1024 | 2002-05-28 | 2002-06-27 | 15 | | 1041 | Y | 1022 | 2002-06-28 | NULL | 1 | | 1041 | Y | 1023 | 2002-06-28 | NULL | 3 | | 1041 | Y | 1024 | 2002-06-28 | NULL | 5 | | 1042 | N | 1022 | 2004-10-23 | 2004-11-22 | 3 | | 1042 | N | 1023 | 2004-10-23 | 2004-11-22 | 10 | | 1042 | N | 1024 | 2004-10-23 | 2004-11-22 | 15 | | 1042 | Y | 1022 | 2004-11-23 | NULL | 1 | | 1042 | Y | 1023 | 2004-11-23 | NULL | 3 | | 1042 | Y | 1024 | 2004-11-23 | NULL | 5 | | 1043 | N | 1022 | 2004-10-23 | 2004-11-22 | 3 | | 1043 | N | 1023 | 2004-10-23 | 2004-11-22 | 10 | | 1043 | N | 1024 | 2004-10-23 | 2004-11-22 | 15 | | 1043 | Y | 1022 | 2004-11-23 | NULL | 1 | | 1043 | Y | 1023 | 2004-11-23 | NULL | 3 | | 1043 | Y | 1024 | 2004-11-23 | NULL | 5 | | 1044 | N | 1022 | 2006-03-07 | 2006-04-06 | 3 | | 1044 | N | 1023 | 2006-03-07 | 2006-04-06 | 10 | | 1044 | N | 1024 | 2006-03-07 | 2006-04-06 | 15 | | 1044 | Y | 1022 | 2006-04-07 | NULL | 1 | | 1044 | Y | 1023 | 2006-04-07 | NULL | 3 | | 1044 | Y | 1024 | 2006-04-07 | NULL | 5 | | 1045 | N | 1022 | 2006-03-07 | 2006-04-06 | 3 | | 1045 | N | 1023 | 2006-03-07 | 2006-04-06 | 10 | | 1045 | N | 1024 | 2006-03-07 | 2006-04-06 | 15 | | 1045 | Y | 1022 | 2006-04-07 | NULL | 1 | | 1045 | Y | 1023 | 2006-04-07 | NULL | 3 | | 1045 | Y | 1024 | 2006-04-07 | NULL | 5 | | 1046 | N | 1022 | 2006-03-07 | 2006-04-06 | 3 | | 1046 | N | 1023 | 2006-03-07 | 2006-04-06 | 10 | | 1046 | N | 1024 | 2006-03-07 | 2006-04-06 | 15 | | 1046 | Y | 1022 | 2006-04-07 | NULL | 1 | | 1046 | Y | 1023 | 2006-04-07 | NULL | 3 | | 1046 | Y | 1024 | 2006-04-07 | NULL | 5 | | 1047 | N | 1022 | 2007-12-11 | 2008-01-10 | 3 | | 1047 | N | 1023 | 2007-12-11 | 2008-01-10 | 10 | | 1047 | N | 1024 | 2007-12-11 | 2008-01-10 | 15 | | 1047 | Y | 1022 | 2008-01-11 | NULL | 1 | | 1047 | Y | 1023 | 2008-01-11 | NULL | 3 | | 1047 | Y | 1024 | 2008-01-11 | NULL | 5 | | 1048 | N | 1022 | 2009-12-08 | 2010-01-07 | 3 | | 1048 | N | 1023 | 2009-12-08 | 2010-01-07 | 10 | | 1048 | N | 1024 | 2009-12-08 | 2010-01-07 | 15 | | 1048 | Y | 1022 | 2010-01-08 | NULL | 1 | | 1048 | Y | 1023 | 2010-01-08 | NULL | 3 | | 1048 | Y | 1024 | 2010-01-08 | NULL | 5 | | 1049 | N | 1022 | 2011-10-15 | 2011-11-14 | 3 | | 1049 | N | 1023 | 2011-10-15 | 2011-11-14 | 10 | | 1049 | N | 1024 | 2011-10-15 | 2011-11-14 | 15 | | 1049 | Y | 1022 | 2011-11-15 | NULL | 1 | | 1049 | Y | 1023 | 2011-11-15 | NULL | 3 | | 1049 | Y | 1024 | 2011-11-15 | NULL | 5 | | 1050 | N | 1022 | 2011-11-11 | 2011-12-11 | 3 | | 1050 | N | 1023 | 2011-11-11 | 2011-12-11 | 10 | | 1050 | N | 1024 | 2011-11-11 | 2011-12-11 | 15 | | 1050 | Y | 1022 | 2011-12-12 | NULL | 1 | | 1050 | Y | 1023 | 2011-12-12 | NULL | 3 | | 1050 | Y | 1024 | 2011-12-12 | NULL | 5 | | 1051 | N | 1022 | 2011-11-11 | 2011-12-11 | 3 | | 1051 | N | 1023 | 2011-11-11 | 2011-12-11 | 10 | | 1051 | N | 1024 | 2011-11-11 | 2011-12-11 | 15 | | 1051 | Y | 1022 | 2011-12-12 | NULL | 1 | | 1051 | Y | 1023 | 2011-12-12 | NULL | 3 | | 1051 | Y | 1024 | 2011-12-12 | NULL | 5 | | 1052 | Y | 1022 | 2014-07-13 | NULL | 3 | | 1052 | Y | 1023 | 2014-07-13 | NULL | 10 | | 1052 | Y | 1024 | 2014-07-13 | NULL | 15 | | 1053 | Y | 1022 | 2014-07-13 | NULL | 3 | | 1053 | Y | 1023 | 2014-07-13 | NULL | 10 | | 1053 | Y | 1024 | 2014-07-13 | NULL | 15 | | 1054 | Y | 1022 | 2014-07-13 | NULL | 3 | | 1054 | Y | 1023 | 2014-07-13 | NULL | 10 | | 1054 | Y | 1024 | 2014-07-13 | NULL | 15 | +---------+-------------+------------+------------+------------+--------+ 315 rows in set (0.01 sec)

Written by michaelmclaughlin

June 1st, 2014 at 2:15 am

Posted in

33 Responses to 'Lab #7: MySQL'

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

Your first instructions for my MySQL are giving Oracle commands, such as spool instead of TEE/NOTEE and @ instead of \.

Abel Goodwin

3 Jun 14 at 7:52 pm
To make a log file in MySql, the log statement should be TEE not SPOOL.

Matthew

3 Jun 14 at 8:35 pm
Yes, I’ve fixed it. Too much copy and paste.

michaelmclaughlin

3 Jun 14 at 9:15 pm

For 3b the check statement:

SELECT   TABLE_NAME
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE 'NULLABLE'
         END AS constraints
FROM     information_schema.columns
WHERE    TABLE_NAME = 'RENTAL_ITEM'
AND      column_name = 'RENTAL_ITEM_TYPE';

The where clause should be:

WHERE    table_name = 'rental_item'

SELECT   table_name
,        column_name
,        CASE
           WHEN is_nullable = 'NO' THEN 'NOT NULL'
           ELSE 'NULLABLE'
         END AS constraints
FROM     information_schema.columns
WHERE    table_name = 'rental_item'
AND      column_name = 'RENTAL_ITEM_TYPE';

Kyle Rhoton

4 Jun 14 at 1:40 pm

Step 2 validation you have an = when you need a second IN operator.

Matthew Mason

24 Jun 14 at 11:36 pm
Matt, Good catch. I fixed the comparison operator and changed the literal values to uppercase.

michaelmclaughlin

25 Jun 14 at 12:10 am
Your validation for 3.B to qualify the foreign key in the rental_item table needs to have the table_name, column_name, and the constraint_name in lower case. It returns an empty set.

Kent

1 Jul 14 at 4:59 pm
Kent, Here’s a necessary change. Thanks.
1 2 3
WHERE tc.table_name = 'rental_item' AND kcu.column_name = 'rental_item_type' AND tc.constraint_type = 'FOREIGN KEY'
WHERE tc.table_name = 'rental_item' AND kcu.column_name = 'rental_item_type' AND tc.constraint_type = 'FOREIGN KEY'
michaelmclaughlin

1 Jul 14 at 5:47 pm
For 3.a you need to have the two AND statements (just like in oracle) for it to work or it won’t display 12 columns.

Austin

1 Jul 14 at 10:18 pm
Austin, Great catch! Thanks. It’s fixed.

michaelmclaughlin

2 Jul 14 at 12:51 am
There are 6 rows being selected in part two but the result set states that there are “3 rows in set (0.00)”

Sam Graham

31 Oct 14 at 11:38 am
The results displayed for updating existing rows with info from the common_lookup table shows that it should be 12 when it should be 13.

Preston Massey

31 Oct 14 at 1:09 pm

The check statement below returns 6 rows, not 3 as the image displays.

SELECT   common_lookup_table
,        common_lookup_column
,        common_lookup_type
FROM     common_lookup
WHERE    common_lookup_table IN ('PRICE','RENTAL_ITEM')
AND      common_lookup_column IN ('PRICE_TYPE','RENTAL_ITEM_TYPE')
ORDER BY 1, 2, 3;

Robbie

31 Oct 14 at 1:46 pm

I think there should be an instruction to add the rental_item_type foreign key constraint at the end of 3b

Robbie

31 Oct 14 at 3:38 pm
Robbie, Great catch …

michaelmclaughlin

1 Nov 14 at 1:19 am
Sam, Thanks. It’s fixed.

michaelmclaughlin

1 Nov 14 at 1:25 am
Robbie, Great catch! I’ve added it. Let me know if it works for you?

michaelmclaughlin

1 Nov 14 at 1:34 am
Preston, I believe this is fixed now. Let me know if it isn’t.

michaelmclaughlin

1 Nov 14 at 1:42 am
Your Release Data Calculations for Part 4 appear to be formatted for Oracle, not MySQL.
WHERE (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31
WHERE (TRUNC(SYSDATE) - TRUNC(i.release_date)) < 31
Keith Banner

14 Nov 14 at 4:06 pm

Not sure if this was supposed to be part of what we were trying to figure out in the assignment but in part 4 it should read PRICE and PRICE_TYPE instead of RENTAL_ITEM and RENTAL_ITEM_TYPE.

SELECT   ...
FROM     item i CROSS JOIN
        (SELECT 'Y' AS active_flag FROM dual
         UNION ALL
         SELECT 'N' AS active_flag FROM dual) af CROSS JOIN
        (SELECT '1' AS rental_days FROM dual
         UNION ALL
         SELECT '3' AS rental_days FROM dual
         UNION ALL
         SELECT '5' AS rental_days FROM dual) dr INNER JOIN
         common_lookup cl ON dr.rental_days = SUBSTR(cl.common_lookup_type,1,1)
WHERE    cl.common_lookup_table = 'RENTAL_ITEM'
AND      cl.common_lookup_column = 'RENTAL_ITEM_TYPE'
AND NOT  ...
ORDER BY 1, 2, 3;

Keith Banner

18 Nov 14 at 8:17 pm

It’s the basis of the solution. You only need to fix the WHERE clause and provide the necessary SELECT-list values. Some of the SELECT-list values use CASE statements.

michaelmclaughlin

19 Nov 14 at 2:56 am
in the last step of 3a in this labe the query should display 13 for both the row_count and the col_count

James Stakebake

20 Feb 15 at 4:55 pm
3.a (just above step b) the output currently on the website is asking for ‘row_count = 13’ & ‘column_count = 12′

In the oracle instructions it asks for ’13’ in each column. I assume that this is just a typo

Joseph Tracy

21 Feb 15 at 1:00 pm

I keep getting this error for a syntax I wrote for an INSERT statement:

mysql> ERROR 1062 (23000): Duplicate entry 'YES' for key 
 
mysql> INSERT INTO common_lookup
    -> (common_lookup_id
    -> ,common_lookup_context
    -> ,common_lookup_type
    -> ,common_lookup_meaning
    -> ,created_by
    -> ,creation_date
    -> ,last_updated_by
    -> ,last_update_date
    -> ,common_lookup_table
    -> ,common_lookup_column
    -> ,common_lookup_code)
    -> VALUES
    -> (NULL
    -> ,''
    -> ,'YES'
    -> ,'yes'
    -> ,1
    -> ,UTC_DATE
    -> ,1
    -> ,UTC_DATE
    -> ,'PRICE'
    -> ,'ACTIVE_FLAG'
    -> ,'Y');

Is my error from the previous lab #6 or is it in this table referencing the 'YES' and the 'Yes'? I went through my lab #6, but I can’t find a duplicate, so I’m confused as to what duplicate the error is referencing to …

Renee' Miitchell

23 Feb 15 at 1:20 pm

I think 3b should read, “Change the RENTAL_ITEM_TYPE column of the RENTAL_ITEM table…” instead of, “Change the RENTAL_ITEM_TYPE column of the RENTAL_ITEM column…”

Jonathan

1 Mar 15 at 1:56 am
For the update statement in 3A, consider adding some explanatory text saying you also have to change the minus to datediff or date_sub to get it to work in MySQL.

Jeremy

4 Mar 15 at 12:45 pm
In step 3A:

+———–+———–+
| row_count | col_count |
+———–+———–+
| 13 | 12 |
+———–+———–+
1 row in set (0.06 sec)

should be

+———–+———–+
| row_count | col_count |
+———–+———–+
| 13 | 13 |
+———–+———–+
1 row in set (0.06 sec)

Jeremy

4 Mar 15 at 12:53 pm
Jeremy, Great catch! It’s fixed. Thanks.

michaelmclaughlin

6 Mar 15 at 11:11 pm
Jeremy, That sounds fair but honestly it’s covered in Chapter 11, which should have been read by the time the student performs this lab. By providing it, we’re acknowledging that they didn’t grasp that date math differs between the Oracle and MySQL databases.

michaelmclaughlin

7 Mar 15 at 12:02 am
Jonathan, You’re correct. I’ve fixed the text.

michaelmclaughlin

7 Mar 15 at 12:07 am
Renee’ Miitchell, You shouldn’t get a duplicate key error but it appears you didn’t complete step 4d because the COMMON_LOOKUP_CONTEXT should be dropped. It’s possible that you also neglected to drop the COMMON_LOOKUP_U1 index, which may be why you threw this error at this point in the sequence of labs.
1. Remove obsolete columns, apply not null constraints, and create a new unique index for the natural key of the COMMON_LOOKUP table.
michaelmclaughlin

7 Mar 15 at 12:49 am
Joseph, You’re right and it’s fixed. Thanks!

michaelmclaughlin

7 Mar 15 at 1:14 am
James, You’re right and it’s fixed.

michaelmclaughlin

7 Mar 15 at 1:20 am

Database Tutorial

Lab #7: MySQL

33 Responses to 'Lab #7: MySQL'

Leave a Reply

My Technical Blog

Tutorial Pages

Blogroll