Database Tutorial

Group Objectives

Group assignments are cooperative situations where you should learn from one another. Each team member should engage and type solution components individually. This tends to maximize the student learning opportunity. You should avoid the “expert and followers” paradigm, and become cooperative and interactive problem solvers. The “expert and followers” paradigm is where one person in a group does the work and learns, while others look over the shoulder at the work without truly understanding it. Team members become interactive problem solvers when they collaborate and work together on solving problems.

If something comes up, like an illness or excused absence, you may share files. The caution here is that the student receiving the work must master the concepts in the material before submitting the work to the instructor. The instructor may interview the student to determine their level of mastery and post group credit that may differ from other team members who performed the work.

• Learn how to translate textual descriptions into internal and external tables.
• Learn how to import data.
• Learn how to use advanced aggregation, including functions in the GROUP BY clause.
• Learn how to use the CASE statement in conjunction with aggregatoin functions to produce selective aggregation results.
• Learn how to work with hierarchical queries in an Oracle database.

Individual Objectives

Individual assignments are situations where you apply the generalized concepts acquired in the group exercise, and transfer your skills to another database engine. You may ask questions but you should only receive general concepts, principles, or directions to published materials (such as the product documentation found in the resources). Likewise, those are the types of answers you should provide when asked questions by your peers. You should not share files in this part of the lab.

• Learn how to translate textual descriptions into internal and external tables.
• Learn how to import data.
• Learn how to use advanced aggregation, including functions in the GROUP BY clause.
• Learn how to use the CASE statement in conjunction with aggregatoin functions to produce selective aggregation results.

• Basic Aggregation
• Advanced Aggregation
• Oracle External Tables
• MySQL CSV Upload
• MERGE Statement
• Import Transaction Units
• Basic Transformations
• Hierarchical Queries
• Oracle® Documentation.
• MySQL Documentation

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12

-- Set environment variables.
SET LONG 100000
SET PAGESIZE 0
 
-- Set a local variable of a character large object (CLOB).
VARIABLE ddl_text CLOB
 
-- Get the internal DDL command for the TRANSACTION table from the data dictionary.
SELECT dbms_metadata.get_ddl('TABLE','TRANSACTION') FROM dual;
 
-- Get the internal DDL command for the external TRANSACTION_UPLOAD table from the data dictionary.
SELECT dbms_metadata.get_ddl('TABLE','TRANSACTION_UPLOAD') FROM dual;

1
2
3
4
5

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

1
2
3
4
5
6
7
8
9
10
11
12

COLUMN "Debit Transactions"  FORMAT A20
COLUMN "Credit Transactions" FORMAT A20
COLUMN "All Transactions"    FORMAT A20
 
-- Check current contents of the model.
SELECT 'SELECT record counts' AS "Statement" FROM dual;
SELECT   LPAD(TO_CHAR(c1.transaction_count,'99,999'),19,' ') AS "Debit Transactions"
,        LPAD(TO_CHAR(c2.transaction_count,'99,999'),19,' ') AS "Credit Transactions"
,        LPAD(TO_CHAR(c3.transaction_count,'99,999'),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;

Debit Transactions   Credit Transactions  All Transactions
-------------------- -------------------- --------------------
              4,681                1,170                5,851

1
2
3
4
5
6
7
8

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

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

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

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

SELECT   CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 'Debit'
           WHEN t.transaction_account = '222-222-222-222' THEN 'Credit'
         END AS "TRANSACTION_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(TO_CHAR
        (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),'99,999.00'),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
,        CASE
           WHEN t.transaction_account = '111-111-111-111' THEN 1
           WHEN t.transaction_account = '222-222-222-222' THEN 2
         END;

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

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;

Transaction     Jan        Feb        Mar        F1Q        Apr        May        Jun        F2Q     Jul           Aug        Sep        F3Q        Oct        Nov        Dec        F4Q        YTD
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
Debit             2,671.20   4,270.74   5,371.02  12,312.96   4,932.18   2,216.46   1,208.40   8,357.04   2,404.08   2,241.90   2,197.38   6,843.36   3,275.40   3,125.94   2,340.48   8,741.82  36,255.18
Credit             -690.06  -1,055.76  -1,405.56  -3,151.38  -1,192.50    -553.32    -298.92  -2,044.74    -604.20    -553.32    -581.94  -1,739.46    -874.50    -833.16    -601.02  -2,308.68  -9,244.26
Total             1,981.14   3,214.98   3,965.46   9,161.58   3,739.68   1,663.14     909.48   6,312.30   1,799.88   1,688.58   1,615.44   5,103.90   2,400.90   2,292.78   1,739.46   6,433.14  27,010.92

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

Group Deliverables

Groups should demonstrate to the instructor or lab assistant during class time that they have successfully completed the lab.

• Every team member should be capable of explaining the idea of a CROSS JOIN.
• Every team member should be capable of explaining the idea of what happens when you combine the SUM and CASE functions in a query like this lab’s ad hoc report.
• Every team member should be capable of explaining how null values are treated in selective aggregation.

Individual Deliverables

Individuals should demonstrate to the instructor or lab assistant during class time time that they have successfully completed the lab.

• Every team member should be capable of explaining the idea of a CROSS JOIN.
• Every team member should be capable of explaining the idea of what happens when you combine the SUM and CASE functions in a query like this lab’s ad hoc report.
• Every team member should be capable of explaining how null values are treated in selective aggregation.
• Every team member should be capable of explaining any differences between the SELECT statements for Oracle versus MySQL databases.