Database Tutorial

Course Tutorial Site

Site Admin

Lab #11: MySQL

with 13 comments

Lab #11: 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, apply_mysql_lab7.sql, apply_mysql_lab8.sql, and apply_mysql_lab9.sql scripts. You should exclude running apply_mysql_lab10.sql script, which is designed to help you develop and test the REPLACE statements for this lab.

While you’re not calling Lab 10, you need the indexes created by Lab 10. You should migrate all the indexes from Lab 10 to the beginning of Lab 11. Put the indexes as the first elements after the TEE command. You should create the apply_mysql_lab11.sql script as follows:

-- This calls Lab #9, Lab #9 calls Lab #8, Lab #8 calls Lab #7, Lab #7 calls Lab #6, Lab #6 calls Lab #5, and Lab #5 calls both the Creation and Seed script files.
@../lab10/apply_mysql_lab9.sql
 
TEE apply_mysql_lab11.txt
 
... INSERT code here ...
 
NOTEE

  • You must repeat the some of the steps from Lab #10 because we skip it at this point. These are the steps:

MySQL doesn’t treat a zero length string like a null value, which is different than Oracle. When you perform this step for MySQL, you must update all zero length strings in the MIDDLE_NAME column of the TRANSACTION_UPLOAD table with a null value.

You can check the count on empty, or zero length, strings with the following query:

1
2
3

SELECT   COUNT(*) AS "Empty String Columns"
FROM     transaction_upload
WHERE    middle_name = '';

It should return the following:

1
2
3
4
5
6

+----------------------+
| Empty String Columns |
+----------------------+
|                 6912 |
+----------------------+
1 row in set (0.00 sec)

1
2
3

UPDATE transaction_upload
SET    middle_name = null
WHERE  middle_name = '';

Using the previous diagnostic query, you can see there aren’t any empty strings after the UPDATE statement.

1
2
3
4
5
6

+----------------------+
| Empty String Columns |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

You can discover the CONTACT_ID by using the following type of query. 

1
2
3
4
5
6
7
8
9
10

SELECT   DISTINCT
         c.contact_id
,        c.last_name
FROM     member m INNER JOIN transaction_upload tu
ON       m.account_number = tu.account_number INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = tu.first_name
AND      IFNULL(c.middle_name,'x') = IFNULL(tu.middle_name,'x')
AND      c.last_name = tu.last_name
ORDER BY 1;

It should return the following rows.

+------------+-----------+
| contact_id | last_name |
+------------+-----------+
|       1001 | Winn      |
|       1002 | Winn      |
|       1003 | Vizquel   |
|       1004 | Vizquel   |
|       1005 | Sweeney   |
|       1006 | Sweeney   |
|       1007 | Sweeney   |
|       1008 | Clinton   |
|       1009 | Moss      |
|       1010 | Gretelz   |
|       1011 | Royal     |
|       1012 | Smith     |
|       1013 | Potter    |
|       1014 | Potter    |
|       1015 | Potter    |
+------------+-----------+
15 rows in set (0.15 sec)

  1. [6 points] This step requires that you use the query from Lab #10 that you used to insert records into the RENTAL table. You need to put it inside the USING clause of the REPLACE INTO statement as the query, resolve which columns you use in an UPDATE statement, and resolve which columns you use in an INSERT statement.

You can use the following REPLACE INTO statement shell for the RENTAL table: 

1
2

REPLACE INTO rental
( rental_query );

Use the following query after you run the REPLACE INTO statement:

1
2

SELECT   LPAD(CONCAT(' ',FORMAT(COUNT(*),2)),18,' ') AS "Rental after merge"
FROM     rental;

It should return the following result set:

+--------------------+
| Rental after merge |
+--------------------+
|            4380.00 |
+--------------------+
1 row in set (0.00 sec)

  1. [6 points] This step requires that the preceding REPLACE INTO statement ran successfully and that you use the query from Lab #10 that you used to insert records into the RENTAL_ITEM table. You need to put it inside the USING clause of the REPLACE INTO statement as the query, resolve which columns you use in an UPDATE statement, and resolve which columns you use in an INSERT statement.

You can use the following REPLACE INTO statement shell for the RENTAL_ITEM table: 

1
2

REPLACE INTO rental_item
( rental_item_query );

Use the following query after you run the REPLACE INTO statement:

1
2

SELECT   LPAD(CONCAT(' ',FORMAT(COUNT(*),2)),23,' ') AS "Rental Item after merge"
FROM     rental_item;

It should return the following result set:

+-------------------------+
| Rental Item after merge |
+-------------------------+
|                11533.00 |
+-------------------------+
1 row in set (0.00 sec)

  1. [6 points] This step requires that the preceding REPLACE INTO statement ran successfully and that you use the query from Lab #10 that you used to insert records into the TRANSACTION table. You need to put it inside the USING clause of the REPLACE INTO statement as the query, resolve which columns you use in an UPDATE statement, and resolve which columns you use in an INSERT statement.

You can use the following REPLACE INTO statement shell for the RENTAL_ITEM table: 

1
2

REPLACE INTO transaction
( transaction_query );

Use the following query after you run the REPLACE INTO statement:

1
2

SELECT   LPAD(CONCAT(' ',FORMAT(COUNT(*),2)),23,' ') AS "Transaction after merge"
FROM     transaction;

It should return the following result set:

+-------------------------+
| Transaction after count |
+-------------------------+
|                 4372.00 |
+-------------------------+
1 row in set (0.00 sec)
  1. [12 points] After running the first three steps, you need to put the three REPLACE INTO statement into a single upload_transaction procedure. Then, you need to run the upload_transaction procedure, run a validation query, run the upload_transaction procedure a second time, and run a validation query again.
  1. [4 points of 12 points] After running the first three steps, you need to put the three REPLACE INTO statement into a single upload_transaction procedure.
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS transaction_upload;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE transaction_upload() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Replace into rental table. */  
  REPLACE INTO rental
  ( rental_query );
 
  /* Replace into rental_item table. */  
  REPLACE INTO rental_item
  ( rental_item_query );
 
  REPLACE INTO transaction
  ( transaction_query );
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;
  1. [2 points of 12 points] You run the upload_transaction procedure with the following syntax:
CALL transaction_upload();
  1. [2 points of 12 points] You should use the following to query the results from procedure.
1
2
3
4
5
6

SELECT   c1.rental_count
,        c2.rental_item_count
,        c3.transaction_count
FROM    (SELECT COUNT(*) AS rental_count FROM rental) c1 CROSS JOIN
        (SELECT COUNT(*) AS rental_item_count FROM rental_item) c2 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM transaction) c3;

It should return the following result set:

+--------------+-------------------+-------------------+
| rental_count | rental_item_count | transaction_count |
+--------------+-------------------+-------------------+
|         4380 |             11533 |              4372 | 
+--------------+-------------------+-------------------+
  1. [2 points of 12 points] You re-run the upload_transaction procedure with the following syntax:
CALL transaction_upload();
  1. [2 points of 12 points] You should reuse the following to query the results from procedure.
1
2
3
4
5
6

SELECT   c1.rental_count
,        c2.rental_item_count
,        c3.transaction_count
FROM    (SELECT COUNT(*) AS rental_count FROM rental) c1 CROSS JOIN
        (SELECT COUNT(*) AS rental_item_count FROM rental_item) c2 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM transaction) c3;

It should return the following result set:

+--------------+-------------------+-------------------+
| rental_count | rental_item_count | transaction_count |
+--------------+-------------------+-------------------+
|         4380 |             11533 |              4372 | 
+--------------+-------------------+-------------------+

  1. [5 points] Create a query that prints the following types of data for the year 2009.

Implementation Note: This is a preliminary report that lets the business user see what their aggregate earnings would be with a 10% and 20% increase, as well as see the incremental increases by themselves.

Business Rule: This join can be made against only the TRANSACTION table.

System Logic: Implement consistent with the business rule and available resources on the respective platforms. Performing the GROUP BY in an inline view with a non-displayed sorting key that maps to the numeric value of the month is probably the easiest approach.

You’ll also may benefit from knowing about these functions, as you solve this part of the lab. They work in both Oracle and MySQL. The EXTRACT function returns a number between 1 and 12 when you extract a MONTH, and a four-digit year when you extract a YEAR. Copy the code into a terminal session or development tool and test it.

SELECT   EXTRACT(MONTH FROM '20090202') AS "Month"
,        EXTRACT(YEAR FROM '20090202') AS "Year";

The FORMAT function lets you apply formatting masks in MySQL, like the following that converts numbers to dollars with two values to the right of the decimal point. Again, you can copy and test how this works.

SELECT   LPAD(CONCAT('$',FORMAT(SUM(9999999.00),2)),14,' ') AS "Formatted"
FROM     dual;

The desired layout is noted below in the color formatted table.

Query Elements
Month Year Base
Revenue		 10 Plus
Revenue		 20 Plus
Revenue		 10 Plus
Difference		 20 Plus
Difference
JAN 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
FEB 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
MAR 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
APR 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
MAY 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
JUN 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
JUL 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
AUG 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
SEP 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
OCT 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
NOV 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn
DEC 2009 n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn n,nnn.nn

The next two sections show you the expected outcome. You should note that these results work from only the seeded items. However, they do work from customers seeded and you’re three Potter users from your Lab #6.

MySQL Validation

The actual data from the import source should show the following when you run it in an MySQL database:

+----------+------------+------------+------------+------------+------------+
| MON-YEAR | BASE       | 10_PLUS    | 20_PLUS    | 10_DIFF    | 20_DIFF    |
+----------+------------+------------+------------+------------+------------+
| JAN-2009 |  $2,957.40 |  $3,253.14 |  $3,548.88 |    $295.74 |    $591.48 |
| FEB-2009 |  $4,022.70 |  $4,424.97 |  $4,827.24 |    $402.27 |    $804.54 |
| MAR-2009 |  $5,654.04 |  $6,219.44 |  $6,784.85 |    $565.40 |  $1,130.81 |
| APR-2009 |  $4,595.10 |  $5,054.61 |  $5,514.12 |    $459.51 |    $919.02 |
| MAY-2009 |  $2,219.64 |  $2,441.60 |  $2,663.57 |    $221.96 |    $443.93 |
| JUN-2009 |  $1,300.62 |  $1,430.68 |  $1,560.74 |    $130.06 |    $260.12 |
| JUL-2009 |  $2,413.62 |  $2,654.98 |  $2,896.34 |    $241.36 |    $482.72 |
| AUG-2009 |  $2,149.68 |  $2,364.65 |  $2,579.62 |    $214.97 |    $429.94 |
| SEP-2009 |  $2,162.40 |  $2,378.64 |  $2,594.88 |    $216.24 |    $432.48 |
| OCT-2009 |  $3,291.30 |  $3,620.43 |  $3,949.56 |    $329.13 |    $658.26 |
| NOV-2009 |  $3,246.78 |  $3,571.46 |  $3,896.14 |    $324.68 |    $649.36 |
| DEC-2009 |  $2,299.14 |  $2,529.05 |  $2,758.97 |    $229.91 |    $459.83 |
+----------+------------+------------+------------+------------+------------+

Written by michaelmclaughlin

June 29th, 2014 at 8:56 pm

Posted in

13 Responses to 'Lab #11: MySQL'

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

  1. Step 5 is labeled as step 6 again.

    Kent

    2 Jul 14 at 9:08 am

  2. Where is #5?

    Brian

    10 Jul 14 at 6:52 pm

  3. Are the values in the final query correct? We are getting different numbers.

    Brooke

    10 Jul 14 at 8:26 pm

  4. Year mentioned in step six is incorrect

    Bryce Franzen

    11 Jul 14 at 1:10 pm

  5. Your statement to call the procedure has an error. It attempts to call “transactions_upload()” where the created procedure is “transaction_upload()”.

    Tanner Crook

    15 Jul 14 at 3:36 pm

  6. Tanner, nice catch. It’s fixed.

    michaelmclaughlin

    22 Oct 14 at 1:02 am

  7. Brian, There should be a 5, not a 6. It’s fixed now.

    michaelmclaughlin

    22 Oct 14 at 1:07 am

  8. Fixed.

    michaelmclaughlin

    22 Oct 14 at 2:39 am

  9. Yes, did you divide by 1.06 to account for the interest in the uploaded file.

    michaelmclaughlin

    25 Oct 14 at 6:47 pm

  10. Step 4D reads “CALL transactions_upload();” and it needs to be “CALL transaction_upload();“. No ‘s’ at the end of “transaction.”

    Bryce

    20 Nov 14 at 10:25 pm

  11. Bryce, You’re right. I’ve updated it.

    michaelmclaughlin

    22 Nov 14 at 12:31 am

  12. We need to repeat the steps from the beginning of lab 10 so that we can get the right out put, since we aren’t calling lab 10.

    Danielle

    18 Mar 15 at 1:02 pm

  13. Danielle, You’re completely correct. I’ve added that content to Lab #11.

    michaelmclaughlin

    24 Mar 15 at 9:54 pm

Leave a Reply