Database Tutorial

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.

Implementation Note: The first *.csv file contained incorrect values. This one reverses a number of rentals and adds new ones. Download this zip file. A merge is not necessary. You may accomplish the addition by using an INSERT statement. You need to ignore the TRANSACTION_ID value and use a transaction_reversal_s1.nextval value.

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

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

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

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

Run this query:

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;

You should get the following results.

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

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.

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;

The numbers that let you verify the accuracy of your query are:

Transaction     Jan        Feb        Mar        F1Q        Apr        May        Jun        F2Q        Jul        Aug        Sep        F3Q        Oct        Nov        Dec        F4Q        YTD
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
Debit             2,520.00   4,029.00   5,067.00  11,616.00   4,653.00   2,091.00   1,140.00   7,884.00   2,268.00   2,115.00   2,073.00   6,456.00   3,090.00   2,949.00   2,208.00   8,247.00  34,203.00
Credit             -651.00    -996.00  -1,326.00  -2,973.00  -1,125.00    -522.00    -282.00  -1,929.00    -570.00    -522.00    -549.00  -1,641.00    -825.00    -786.00    -567.00  -2,178.00  -8,721.00
Total             1,869.00   3,033.00   3,741.00   8,643.00   3,528.00   1,569.00     858.00   5,955.00   1,698.00   1,593.00   1,524.00   4,815.00   2,265.00   2,163.00   1,641.00   6,069.00  25,482.00