SQL In-class Assignment

Before we start.

• Download and install DB Browser for SQLite
• Download data (invoicing.cvs and receiving.cvs) from the SQL module in Canvas/Assignments.
• Download the in-class assignment answer sheet.You will complete this and then upload it back to canvas.

Downloads – DB Browser for SQLite (sqlitebrowser.org)

https://sqlitebrowser.org/dl/

The problem:

The company usings standard costs for inventory and are billed actual costs.When inventory is received, the company makes the following:

Dr.Inventory$Standard

Cr.Inventory Suspense$Standard

When the invoice is received, the following entry is made:

Dr. Inventory Suspense$Actual

Cr.Accounts Payable$Actual

At the end of every month, the Inventory Suspense account does not clear out.

• What can cause this balance in the suspense account? (Hint: there are 3 main causes)
• Let’s create 3 reports analyzing these problems. Below are the tables and attributes.

• Open DB Browser, start a new database, choose folder, give it a name,
• import csv tables (invoicing.cvs and receiving.cvs).File/Import.Make sure to click on column names in first line box
• Write and run SQL code for the following Questions

• What items match on PO, Item#, and what is the variance?Use SQL to create a table for matched items that calculates the variance.Add a criteria for PPV > $5. then export it
• What inventory items did we receive but have no invoice?
• What invoice did we receive but have not received the inventory items?

Take screenshots of the above reports and insert them into the answer sheet.You can also export the three tables CSV files.