Lab #12: MySQL at Database Tutorial

Lab #12: MySQL

Lab #11: MySQL Assignment

You begin these steps after running the create_mysql_store_ri2.sql, the seed_mysql_store_ri2.sql, apply_lab5_mysql.sql, apply_mysql_lab6.sql, apply_mysql_lab7.sql, apply_mysql_lab8.sql, apply_mysql_lab9.sql, apply_mysql_lab10.sql, and apply_mysql_lab11.sql scripts. You should create the apply_mysql_lab12.sql script as follows:

-- This calls Lab #11, Lab #11 calls Lab #10, Lab #10 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.
\. ../lab11/apply_mysql_lab11.sql
 
TEE apply_mysql_lab12.txt
 
... INSERT code here ...
 
NOTEE

[3 points] Create the following CALENDAR table as per the specification.

Implementation Note: The CALENDAR table has a range relationship to the TRANSACTION table created in the prior lab.

Business Rule: The CALENDAR table provides end points to bracket transaction dates into calendar months.

System Logic: In future refinement this table may become multilingual and support regional languages based on HTML request header information.

Table Name: CALENDAR
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
CALENDAR_ID	PRIMARY KEY			Integer	Maximum
CALENDAR_NAME	NOT NULL			String	10
CALENDAR_SHORT_NAME	NOT NULL			String	3
START_DATE	NOT NULL			Date	Date
END_DATE	NOT NULL			Date	Date
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

[3 points] Seed the CALENDAR table with the following data.

Data Entry to Table: CALENDAR
Calendar Month Name	Calendar Short Month Name	Start Date	End Date
January	JAN	01-JAN-2009	31-JAN-2009
February	FEB	01-FEB-2009	28-FEB-2009
March	MAR	01-MAR-2009	31-MAR-2009
April	APR	01-APR-2009	30-APR-2009
May	MAY	01-MAY-2009	31-MAY-2009
June	JUN	01-JUN-2009	30-JUN-2009
July	JUL	01-JUL-2009	31-JUL-2009
August	AUG	01-AUG-2009	31-AUG-2009
September	SEP	01-SEP-2009	30-SEP-2009
October	OCT	01-OCT-2009	31-OCT-2009
November	NOV	01-NOV-2009	30-NOV-2009
December	DEC	01-DEC-2009	31-DEC-2009

[4 points] Import and merge the new *.csv files.

Implementation Note: The first *.csv file contained incorrect values. This one reverses a number of rentals and adds new ones. Download this zip file, and use the transaction_upload2.csv for Oracle and transaction_upload2_mysql.csv for MySQL. A merge is not necessary. You may accomplish the addition by using an INSERT statement. You need to ignore the TRANSACTION_ID from the TRANSACTION_REVERSAL table but use the other fields.

A cautionary note: If you open the MySQL upload file in Excel, it will change the native date formats and make them incompatible with an upload to MySQL. You’ll need to manually change them to make it work. You can do that by choosing the other date formatting option.

System Logic: Create the TRANSACTION_REVERSAL table as qualified below by leveraging the existing structure of the TRANSACTION table. Oracle provides the DBMS_METADATA.GET_DDL to create a starting point for an external table definition, and you can simply select the structure in MySQL (see the hint).

Table Name: TRANSACTION_REVERSAL
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
TRANSACTION_ID	PRIMARY KEY			Integer	Maximum
TRANSACTION_ACCOUNT				String	15
TRANSACTION_TYPE				Integer	Maximum
TRANSACTION_DATE				Date	Date
TRANSACTION_AMOUNT				Float	Maximum
RENTAL_ID				Integer	Maximum
PAYMENT_METHOD_TYPE				Integer	Maximum
PAYMENT_ACCOUNT_NUMBER				String	19
CREATED_BY				Integer	Maximum
CREATION_DATE				DATE	Date
LAST_UPDATED_BY				Integer	Maximum
LAST_UPDATE_DATE				DATE	Date

Hints for creating the TRANSACTION_REVERSAL table. ↓

This provides a couple tricks that save some typing.

This allows you to create a new table based on the structure of an existing table. Just don’t forget to truncate the data after creating the table.

-- Create a table with a unique name from another table.
CREATE TABLE transaction_reversal AS SELECT * FROM transaction;
 
-- Truncate the table to maintain the structure but dump the data.
TRUNCATE TABLE transaction_reversal;

You can move the table from the Oracle External TRANSACTION table into the TRANSACTION_REVERSAL table with this trick:

-- Move the data from TRANSACTION_REVERSAL to TRANSACTION.
INSERT INTO transaction
(SELECT <column_list>
 FROM    transaction_reversal);

You should find that you have 1,093 rows in MySQL in the TRANSACTION_REVERSAL table. After you insert them into the TRANSACTION table, run the following queries and compare results.

-- Check current contents of the model.
SELECT 'SELECT record counts' AS "Statement";
SELECT   LPAD(FORMAT(c1.transaction_count,0),19,' ') AS "Debit Transactions"
,        LPAD(FORMAT(c2.transaction_count,0),19,' ') AS "Credit Transactions"
,        LPAD(FORMAT(c3.transaction_count,0),19,' ') AS "All Transactions"
FROM    (SELECT COUNT(*) AS transaction_count FROM transaction WHERE transaction_account = '111-111-111-111') c1 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM transaction WHERE transaction_account = '222-222-222-222') c2 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM transaction) c3;

You should get the following results.

+---------------------+---------------------+---------------------+
| Debit Transactions  | Credit Transactions | All Transactions    |
+---------------------+---------------------+---------------------+
|               4,372 |               1,093 |               5,465 |
+---------------------+---------------------+---------------------+

[10 points] Create the following transformation report by a CROSS JOIN between the TRANSACTION and CALENDAR tables. While it is possible to perform this task when the accounting periods are calendar months with only the TRANSACTION table, that coincidence isn’t always the case. Therefore, this presents an opportunity to reinforce a non-equijoin range query.

Implementation Note: This is an ad hoc (run anytime report) that shows rental income, expense, and the total (rental income – rental expense). A zero is returned when there’s no income or expense in a given calendar period (month, quarter, or year-to-date).

Business Rule: Rental income are those rows in the TRANSACTION table that have a PAYMENT_ACCOUNT_NUMBER of 111-111-111-111, and rental return income are those rows in the TRANSACTION table that have a PAYMENT_ACCOUNT_NUMBER of 222-222-222-222 .

System Logic: The CASE statement inside of a SUM function call on the TRANSACTION_DATE column allows you to return only qualifying TRANSACTION_AMOUNT column values in the aggregated sum.

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

Annual Financial Report
Transaction	Jan	Feb	Mar	FQ1	Apr	May	Jun	FQ2	Jul	Aug	Sep	FQ3	Sep	Nov	Dec	FQ4	YTD
Income	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn
Expense	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn
Total	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn	n,nnn.nn

Hint ↓

This shows you how to apply the aggregation syntax for a single month and year. You should also check the lookup functions for quarters and year-to-date transformation columns.

You should be able to extend this behavior to fulfill most of this steps’ requirements. However, you’ll need to replicate a number of components, label them respectively as the month, quarter, or year-to-date; and then wrap this as an inline view.

SELECT   CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 'Debit'
           WHEN t.transaction_account = '222-222-222-222' THEN 'Credit'
         END AS "Account"
,        CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 1
           WHEN t.transaction_account = '222-222-222-222' THEN 2
         END AS "Sortkey"
,        LPAD(FORMAT
        (SUM(CASE
               WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                    EXTRACT(YEAR FROM transaction_date) = 2009 THEN
                 CASE
                   WHEN cl.common_lookup_type = 'DEBIT'
                   THEN t.transaction_amount
                   ELSE t.transaction_amount * -1
                 END
             END),2),10,' ') AS "JANUARY"
FROM     transaction t INNER JOIN common_lookup cl
ON       t.transaction_type = cl.common_lookup_id 
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE' 
GROUP BY CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 'Debit'
           WHEN t.transaction_account = '222-222-222-222' THEN 'Credit'
         END;

The numbers that let you verify the accuracy of your query with the following results:

+-------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+--------------+
| Transaction | Jan        | Feb        | Mar        | F1Q        | Apr        | May        | Jun        | F2Q        | Jul        | Aug        | Sep        | F3Q        | Oct        | Nov        | Dec        | F4Q        | YTD          |
+-------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+--------------+
| Debit       |   2,957.40 |   4,022.70 |   5,654.04 |  12,634.14 |   4,595.10 |   2,219.64 |   1,300.62 |   8,115.36 |   2,413.62 |   2,149.68 |   2,162.40 |   6,725.70 |   3,291.30 |   3,246.78 |   2,299.14 |   8,837.22 |    36,312.42 |
| Credit      |    -750.48 |    -992.16 |  -1,437.36 |  -3,180.00 |  -1,217.94 |    -546.96 |    -302.10 |  -2,067.00 |    -597.84 |    -537.42 |    -604.20 |  -1,739.46 |    -829.98 |    -829.98 |    -594.66 |  -2,254.62 |    -9,241.08 |
| Total       |   2,206.92 |   3,030.54 |   4,216.68 |   9,454.14 |   3,377.16 |   1,672.68 |     998.52 |   6,048.36 |   1,815.78 |   1,612.26 |   1,558.20 |   4,986.24 |   2,461.32 |   2,416.80 |   1,704.48 |   6,582.60 |    27,071.34 |
+-------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+--------------+

Written by michaelmclaughlin

July 1st, 2014 at 1:35 pm

Posted in

4 Responses to 'Lab #12: MySQL'

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

At the end of part 3 'INSERT INTO TRANSACTION' and 'FROM TRANSACTION' should be lowercase 'transaction'.

Adam Quinton

4 Dec 14 at 11:45 pm
Adam, Thanks! That was a formatting issue with the code formatting plugin. It’s fixed.

michaelmclaughlin

21 Dec 14 at 5:30 pm

All instances of TRANSACTION need to be lower case.

Examples:

CREATE TABLE transaction_reversal AS SELECT * FROM transaction;

in step 2

-- Check current contents of the model.
SELECT 'SELECT record counts' AS "Statement";
SELECT   LPAD(FORMAT(c1.transaction_count,0),19,' ') AS "Debit Transactions"
,        LPAD(FORMAT(c2.transaction_count,0),19,' ') AS "Credit Transactions"
,        LPAD(FORMAT(c3.transaction_count,0),19,' ') AS "All Transactions"
FROM    (SELECT COUNT(*) AS transaction_count FROM transaction WHERE transaction_account = '111-111-111-111') c1 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM transaction WHERE transaction_account = '222-222-222-222') c2 CROSS JOIN
        (SELECT COUNT(*) AS transaction_count FROM transaction) c3;

Jeremy Heiner

1 Apr 15 at 11:44 am

I believe it’s because transaction is a reserved word, and it’s getting capitalized. However, the current instance is lowercase for table names.

michaelmclaughlin

5 Apr 15 at 10:36 pm

Database Tutorial

Lab #12: MySQL

4 Responses to 'Lab #12: MySQL'

Leave a Reply

My Technical Blog

Tutorial Pages

Blogroll