Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #12: Selective Aggregation

Learn how to use the CASE operator with aggregation functions to transform and report data. This type of approach is known as selective aggregation.

Lab Instructions

This lab has four unit testing steps placed into one test script.

[56 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

You begin these steps after running the following script:

  • The lab11/apply_oracle_lab11.sql script

You put the following script in your apply_oracle_lab12.sql script, which should be in the following directory:

/home/student/Data/cit225/lab11

Sample script →

This expands to show you how to structure your apply_oracle_lab12.sql script.

-- ------------------------------------------------------------------
-- Call the prior lab.
-- ------------------------------------------------------------------
 
@@/home/student/Data/cit225/oracle/lab11/apply_oracle_lab11.sql
 
-- Open log file.  
SPOOL apply_oracle_lab12.txt
 
-- --------------------------------------------------------
--  Step #1
--  -------
--  Create the CALENDAR table as per the specifications.
-- --------------------------------------------------------
 
-- Insert step #1 statements here.
 
-- --------------------------------------------------------
--  Step #2
--  -------
--  Seed the CALENDAR table.
-- --------------------------------------------------------
 
-- Insert step #2 statements here.
 
-- --------------------------------------------------------
--  Step #3
--  -------
--  Import the data from the transaction_reversal.csv
--  file into the TRANSACTION_REVERSAL table.
-- --------------------------------------------------------
 
-- Insert step #3 statements here.
 
-- --------------------------------------------------------
--  Step #4
--  -------
--  Create a annual financial report using selective 
--  aggregation.
-- --------------------------------------------------------
 
-- Insert step #4 statements here.
 
-- Close log file.
SPOOL OFF
 
-- Make all changes permanent.
COMMIT;

You should complete the following five steps:

  1. [6 points] Create the calendar table as per the specification provided.

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.

Instruction Details →

You should use the following specification to create the calendar table and calendar_s1 sequence.

Table Name: CALENDAR
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
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

  1. Verify the structure of the calendar table with the following query: 

    SET NULL ''
COLUMN table_name   FORMAT A16
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'CALENDAR'
ORDER BY 2;

    It should display the following:

    TABLE NAME	 COLUMN_ID COLUMN NAME		  NULLABLE DATA_TYPE
---------------- --------- ---------------------- -------- ------------
CALENDAR		 1 CALENDAR_ID		  NOT NULL NUMBER(22)
CALENDAR		 2 CALENDAR_NAME	  NOT NULL VARCHAR2(10)
CALENDAR		 3 CALENDAR_SHORT_NAME	  NOT NULL VARCHAR2(3)
CALENDAR		 4 START_DATE		  NOT NULL DATE
CALENDAR		 5 END_DATE		  NOT NULL DATE
CALENDAR		 6 CREATED_BY		  NOT NULL NUMBER(22)
CALENDAR		 7 CREATION_DATE	  NOT NULL DATE
CALENDAR		 8 LAST_UPDATED_BY	  NOT NULL NUMBER(22)
CALENDAR		 9 LAST_UPDATE_DATE	  NOT NULL DATE

  1. Verify the constraints of the calendar table with the following query: 

    COLUMN constraint_name   FORMAT A22  HEADING "Constraint Name"
COLUMN search_condition  FORMAT A36  HEADING "Search Condition"
COLUMN constraint_type   FORMAT A1   HEADING "C|T"
SELECT   uc.constraint_name
,        uc.search_condition
,        uc.constraint_type
FROM     user_constraints uc INNER JOIN user_cons_columns ucc
ON       uc.table_name = ucc.table_name
AND      uc.constraint_name = ucc.constraint_name
WHERE    uc.table_name = UPPER('calendar')
AND      uc.constraint_type IN (UPPER('c'),UPPER('p'))
ORDER BY uc.constraint_type DESC
,        uc.constraint_name;

    It should display the following:

    							    C
Constraint Name        Search Condition 		    T
---------------------- ------------------------------------ -
PK_CALENDAR						    P
SYS_C00169405	       "CALENDAR_NAME" IS NOT NULL	    C
SYS_C00169406	       "CALENDAR_SHORT_NAME" IS NOT NULL    C
SYS_C00169407	       "START_DATE" IS NOT NULL 	    C
SYS_C00169408	       "END_DATE" IS NOT NULL		    C
SYS_C00169409	       "CREATED_BY" IS NOT NULL 	    C
SYS_C00169410	       "CREATION_DATE" IS NOT NULL	    C
SYS_C00169411	       "LAST_UPDATED_BY" IS NOT NULL	    C
SYS_C00169412	       "LAST_UPDATE_DATE" IS NOT NULL	    C

  1. Verify the foreign key constraints with the following query: 

    COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.position = ucc2.position -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'CALENDAR'
ORDER BY ucc1.table_name
,        uc.constraint_name;

    It should display the following:

    Constraint Name:		       References:
 Table.Column				Table.Column
-------------------------------------- ----------------------------------------
FK_CALENDAR1			       REFERENCES
(CALENDAR.CREATED_BY)		       (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CALENDAR2			       REFERENCES
(CALENDAR.LAST_UPDATED_BY)	       (SYSTEM_USER.SYSTEM_USER_ID)

  1. [6 points] Seed the calendar table with data for the year 2009.

Instruction Details →

You should use the following data to seed the calendar table.

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

You should use the following query to verify the data in the calendar table:

-- Query the data insert.
COL calendar_name        FORMAT A10  HEADING "Calendar|Name"
COL calendar_short_name  FORMAT A8  HEADING "Calendar|Short|Name"
COL start_date           FORMAT A9   HEADING "Start|Date"
COL end_date             FORMAT A9   HEADING "End|Date"
SELECT   calendar_name
,        calendar_short_name
,        start_date
,        end_date
FROM     calendar;

It should display the following:

	   Calendar
Calendar   Short    Start     End
Name	   Name     Date      Date
---------- -------- --------- ---------
January    JAN	    01-JAN-09 31-JAN-09
February   FEB	    01-FEB-09 28-FEB-09
March	   MAR	    01-MAR-09 31-MAR-09
April	   APR	    01-APR-09 30-APR-09
May	   MAY	    01-MAY-09 31-MAY-09
June	   JUN	    01-JUN-09 30-JUN-09
July	   JUL	    01-JUL-09 31-JUL-09
August	   AUG	    01-AUG-09 31-AUG-09
September  SEP	    01-SEP-09 30-SEP-09
October    OCT	    01-OCT-09 31-OCT-09
November   NOV	    01-NOV-09 30-NOV-09
December   DEC	    01-DEC-09 31-DEC-09

  1. [10 points] Create a transaction_reversal table that imports a transaction_upload2.csv file from the upload virtual directory; and transfer the data from the transaction_reversal table to the transaction table by using an INSERT statement.

Implementation Note: The first *.csv file contained incorrect values. This one reverses a number of rentals and adds new ones. Download this zip file if you’re not using the course image. The course image already has the file prepositioned with the correct privileges and ownership settings. 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 to populate the column.

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.

Instruction Details →

Create the following externally managed transaction_reversal table.

Table Name: TRANSACTION_REVERSAL
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
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

  1. Create an externally managed transaction_reversal table that accesses the transaction_upload2.csv file through the upload virtual directory:

    Hints for creating the transaction_reversal table. →

    This provides a couple tricks that save some typing.

    Oracle Trick and Technique

    The following shows you how to get key definition syntax from the Oracle data dictionary. You can find the column definitions by getting the syntax for the TRANSACTION table, and the external table components from the definition of the TRANSACTION_UPLOAD table.

    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. You need to move the data from the externally managed transaction_reversal table into the transaction table.

    You can move the table from the Oracle External transaction_reversal table into the transaction table with this trick:

    1
2
3
4

    -- Move the data from TRANSACTION_REVERSAL to TRANSACTION.
INSERT INTO TRANSACTION
(SELECT <column_list>
 FROM    transaction_reversal);
  1. You verify the insert of the data from the transaction_reversal table, which should insert 1,170 rows. 

    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

  1. [24 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 operator 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.

Instruction Details →

The query report layout is shown below in the color formatted table.

Annual Financial Report
Transaction Jan Feb Mar FQ1 Apr May Jun FQ2 Jul Aug Sep FQ3 Oct 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

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.

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;

You can collect quarterly values by using the IN lookup operator, like the following that captures the first quarter of the year.

EXTRACT(MONTH FROM transaction_date) IN (1, 2, 3)

You can collect the yearly results by excluding the WHEN clause that analyzes the month value of the transaction_date column of the transaction table.

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

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Test Case Instructions →

You should embed the verification queries from these instructions inside your apply_lab11_oracle.sql script file. You should call the apply_lab11_oracle.sql script from the sqlplus command-line utility

@apply_oracle_lab11.sql

You should submit your apply_oracle_lab12.sql script file and apply_oracle_lab11.txt log file for a grade.

Written by michaelmclaughlin

August 13th, 2018 at 12:06 pm

Posted in