Computer Science Project: ACCT 4240A Oracle SQL Project

ACCT 4240A Oracle SQL Project

Assignment 2 (50 Pts.) Sidney A. Porter, CPA, MSA

 

Assignment 2 – Page 1 of 7

Overview of SQL Assignment 2

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.

Part 1 – Getting Started – 5 points

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

Part 2 – 10 Points

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

Part 3 – 15 Points

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

Part 4 – 20 points

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

Output for Assignment 2:

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

Data Dictionary

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

Leave a Comment

Your email address will not be published. Required fields are marked *