Computer Science Assignment
Need in 7 hours.
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 1 of 7
In this 4-part assignment, students will complete the following tasks:
• Review and fix the Chart of Accounts from the assignment done earlier in the semester.
• Create a Chart of Accounts (COA) table.
• Add your 30 accounts with amounts.
• Use the SELECT and UNION ALL commands to print a trial balance.
A data dictionary with definitions of some of the terms you will need to know for this
assignment is included at the end of this document.
1. Use the chart of accounts you created earlier in the semester.
2. Fix any problems with your chart of accounts.
a. Please check with the instructor if you received feedback you do not understand.
3. Add dollar amounts to each account.
a. Use these totals and subtotals to determine your amounts:
i. Current Assets = 10,090,000
ii. Non-Current Assets = 3,500,000
iii. Total Assets = 13,590,000
iv. Liabilities = 8,750,000
v. Equity (excluding revenue and expenses) = 3,640,000
vi. Revenue (Net Sales) = 8,000,000
vii. Cost of Goods Sold = 4,800,000
viii. All Other Expenses = 2,000,000
ix. Total Liabilities and Equity = 13,590,000
b. Allocate the above amounts to the appropriate accounts on your chart of accounts.
DO NOT split subtotals and totals evenly (e.g., 10,090,000 ÷ 3, etc.) to create your
amounts.
c. Remember your intermediate accounting. There will be deductions for incorrect
account balances (debit or credit), improper account types, accounts out of order
or poor formatting of your trial balance.
i. Refer to your intermediate accounting textbooks if you are not sure.
4. Subsidiary accounts are details for the respective control account. No amounts are needed
for subsidiary accounts at this time.
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 2 of 7
1. Log on to Oracle (This should be downloaded onto your computer)
2. Create a spool file with an appropriate header.
3. Using SQL Data Definition Language (DDL), create a chart of accounts (COA) table. This
part of the assignment is only for the creation of the table structure (columns). Data lines
with your accounts and amounts will be added in part 2 below.
4. Use the following information to create your COA table:
• Naming convention (See definition in data dictionary). This is how you should name
your table:
yourlastname_COA_2019
Replace “yourlastname” with your actual last name. (Examples Smith_COA_2020 or
Wang_COA_2020 or Rodriguez_COA_2020)
• Columns / attributes needed (5):
o ACCT_NUM
o ACCT_NAME
o ACCT_TYPE
o DEBIT_AMT
o CREDIT_AMT
NOTE: The size of each column should be determined based on the number of
characters you expect each column would need. If your column size is too large, then it
will print awkwardly on the page and use too much space in the database. If your
column size is too small, then your data will not fit. If you must abbreviate names
and descriptions, please ensure that your abbreviations are understandable.
• Key SQL commands you will use for Part 2:
o CREATE TABLE command (use this command to create your COA table)
o ALTER TABLE command (use this command if you need to add, modify, rename
or drop a column / attribute)
o DESCRIBE command (use this command to show a record layout of your table).
This will list:
Columns (attributes) you created for your COA table in default order.
Attribute type (NUMBER, VARCHAR2, etc.)
Attribute size NUMBER(4) is a field with 4 numeric characters.
• See the DDL Create Slides for the proper syntax and usage of these commands. Also see
the Assignment 2 “How-To” video.
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 3 of 7
For this part of the assignment, you will use your chart of accounts populated with the amounts
from above.
1. Use SQL Data Manipulation Language (DML) to insert your 30 accounts into the COA
table you created in Part 2 above.
2. For subsidiary accounts, leave the amounts empty.
a. You may use the explicit method to enter NULL values.
b. You may use the implicit method to leave these accounts NULL (empty).
3. Use SELECT command to display the data in your table.
Below is a logical / conceptual view of your table. Please note that your actual views in SQL
will not look like this.
ACCT_NUM ACCT_NAME ACCT_TYPE DEBIT_AMT CREDIT_AMT
You will have 30
lines in your table,
one line for each
account. Please be
sure that you have
only 30 accounts
exactly.
• Key SQL commands you will use for this part (Note that two or more commands are used
together):
o INSERT INTO and VALUES
o UPDATE, SET and WHERE
o DELETE FROM and WHERE
o SELECT * FROM yourtable; (This command will show the data entered into
your table. This is different from DESCRIBE, which only shows the attributes)
See SQL DML Slides as a reminder of how these commands are used.
Note: This part of the assignment is only for the inserting, updating and deleting data
(rows). If you need to make any additional modifications to the table structure
(columns), please refer to the SQL DDL Slides.
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 4 of 7
1. Fix any problems remaining from Parts 1 through 3.
2. Use Data Query Language to create a trial balance from your updated table.
3. Use the UNION ALL command in your SQL to create a line for total debits and credits.
See UNION ALL slides to understand how to add a total line to your report.
4. Save your SPOOL file and submit to Canvas.
• Key SQL commands you will use for this part (Note that two or more commands are
used together):
o INSERT INTO and VALUES
o UPDATE, SET and WHERE
o DELETE FROM and WHERE
o SELECT * FROM yourtable; (This command will show the data entered into
your table. This is different from DESCRIBE, which only shows the
attributes)
• Important Considerations:
o Every account in your chart of accounts other than subsidiary accounts, should
have a debit amount or credit amount, but not both.
o Your 5 subsidiary accounts should remain blank or zero for this assignment.
o You will need to exclude the subsidiary accounts when printing the trial balance.
You can use the SQL sub command, WHERE, to achieve this. For
example, you may use a command combination that looks similar to this:
SELECT …
WHERE ACCT_NUM NOT IN(2101, 2102, 2103, 2104, 2105);
o Total debits MUST equal credits.
o Every student’s chart of accounts is different. You must choose the amounts
based on your own accounts that will add up to the totals and subtotals from the
instructors table (see above). Deductions will result if any of your accounts,
including contra accounts, are incorrectly reflected when balancing to the
instructor totals.
o Before you start inputting amounts, you should use an Excel file to ensure that
you have a balanced set of accounts and that subtotals agree to those given by the
instructor.
o The total Liabilities and Equity include liability, equity, revenue and expense
accounts. If you are not sure how this works, please review you intermediate
accounting textbooks.
o Below is an example of a trial balance. While your trial balance may use different
accounts and amounts from the trial balance below, you can use this as a guide for
how a trial balance should look.
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 5 of 7
o Your trial balance should have 4 columns instead of 3 (ACCT_NUM,
ACCT_NAME, DEBIT_AMT and CREDIT_AMT). You need not concern
yourself with other formatting.
o
You may submit this assignment using one or more SPOOL files. The electronic copies of your
spool file(s) showing the commands you used to create your table, enter data and display a trial
balance should be submitted to Canvas. You will use the SELECT and UNION ALL
commands. If you are not able to complete this assignment in one sitting, you may stop and
save your SPOOL file. Then you can start a new SPOOL file later and resume where you
left off. Please remember to use a different name for each spool file as you learned in the SQL
Assignment 1 – Practice Exercise.
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 6 of 7
The following terms are important to know for this project.
• Naming convention – Just like paper files, electronic files need to be well-organized and
labeled correctly so that they are easily identifiable and accessible. The use of good
naming conventions for tables in a database is extremely important. For your database,
you will use your name as part of every table you create.
• Spool files – Spooling in SQL is essentially “Keylogging” which simply records all
keystrokes, commands, and computer responses. These are to be submitted as a record of
our work. Spool files will be stored on your flash drive. The actual tables and data for
your project will be on the university server. You may use your own naming convention
for your spool files. Just remember that you must name each spool file a slightly
different name. If you use the same name as a previous spool file in error, the old file
will be erased, and the record of your previous work will be lost.
• Syntax – In programming, syntax refers to the proper form and sequence of commands.
Small syntax errors such as forgetting a comma or parenthesis in a command will cause
errors with your SQL statements.
• Table – A file used to collect and maintain information a company wishes to track.
Examples include information about employees, sales, accounts receivable, vendors,
inventory, etc.
• Inventory – Merchandise purchased by your company for resale. Please DO NOT use
inventory accounts for manufacturing companies, such as materials, work-in-progress
and finished goods. These are not appropriate for merchandising businesses.
• Customer – An individual or company that purchases merchandise from your company.
Customers can have subsidiary accounts, which would be detail accounts on the general
ledger related to the accounts receivable control account.
• Vendors – The companies or individuals your company uses to buy merchandise, other
goods and services needed for the business operations. Vendors can have subsidiary
accounts, which would be details accounts on the general ledger related to the accounts
payable control account.
• Chart of Accounts – A listing of all balance sheet and income statement accounts that a
company uses to properly record transactions into its general ledger. The company
assigns account numbers to each account to facilitate coding and classification. A
company has the flexibility to tailor its chart of accounts to best suit its needs, including
adding accounts as needed.
• ACCT_NUM – This will be the general ledger account number corresponding to your
chart of accounts.
• ACCT_TYPE – This represents the account type (i.e., asset, liability, equity, revenue, or
expense) of your account number.
• Control account – A general ledger account that summarizes the balances of a number
of subsidiary accounts and provides a cross-check on them. For example, the accounts
payable control account would represent the total owed to all vendors (accounts payable),
ACCT 4240A
Oracle SQL Project
Assignment 2 (50 Pts.)
Sidney A. Porter, CPA, MSA
Assignment 2 – Page 7 of 7
the accounts receivable control account would represent the total owed to your company
by customers (accounts receivable) and the inventory control account would represent the
summary total amount of all inventory items.
• Subsidiary account – An account used to track information at a very detailed level for
certain types of transactions, such as accounts receivable, accounts payable or inventory.
Each subsidiary account is related to a specific control account, which contains the
aggregate total of all related accounts.
• Contra account – A general ledger account which is intended to have its balance be the
opposite of the normal balance for that account classification. For instance, a contra asset
account is intended to have a credit balance instead of the debit balance normally found
in an asset account.
- Overview of SQL Assignment 2
Part 1 – Getting Started – 5 points
Part 2 – 10 Points
Part 3 – 15 Points
Part 4 – 20 points
Output for Assignment 2:
Data Dictionary