SQL Assignment

ACCTG 333 Fall 2021 (Professor Perols)

Why should you learn SQL?

Technology is drastically changing the accounting industryand structured query language (SQL) is one of the technologies that students should know (AICPA 2019). SQL as the standard language used to communicate with relational database management systems (RDBMS). A majority of business data are stored in RDBMS. To access and analyze these data, accountants need knowledge of relational databases and SQL. This assignment provides you an opportunity to apply (SQL) in the data processing cycle.

Assignment Overview

This is an individual assignment. You are allowed to discuss the assignment with others in the class, but you may not copy. You will have class time to work on the assignment. You may also work on the assignment remotely and ask me questions via email. You will submit your work in on Canvas (more details to follow). You will submit our SQL query statements only. You will not submit the output (i.e., the data returned by your SQL queries). I highly recommend copying and pasting your SQL statements into a word or notepad file and saving the file in multiple places (e.g., on a computer, email, cloud, external hard drive, etc.). If you have already experienced losing your hard work at some point, then you know how important having one or more backups isat all times. Note that you should execute (run) the query for each question only once and you do not need to store your queries in SQLiteStudio.

The exercises below are designed to walk you through the core components of SQL SELECT statements. SELECT statements are used to query databases. SQL can also be used for other database tasks such as creating, deleting, and modifying tables, columns, constraints, etc. and updating and deleting data in existing tables. To complete these exercises, you need to consult the Chapter 4 PowerPoint and online help https://www.w3schools.com/sql/. Keywords are included in the assignment to help you locate the related online help for each exercise.

SQLiteStudio

To access data in a RDBMS you will typically either create SQL commands in the RDBMS that you are accessing or connect to the RDBMS using an Open Database Connectivity (ODBC) connection from your data analytics tool (e.g., Python, SAS, etc.) or another RDBMS (e.g. Microsoft ACCESS) and create SQL code in this application. In this assignment you will use SQLiteStudio. The installation of SQLiteStudio is very easy compared to larger RDBMSs such as MySQL, SQL Server, and Oracle. SQLiteStudio is also available for a number of operating systems, including MacOS and Windows. This will allow us to focus more on learning SQL rather than installing databases. To install the application and connect to the Northwind database see the detailed instructions on Canvas.

Select Queries

Again, to complete the following 10 questions, you need to consult online help https://www.w3schools.com/sql/. Keywords are included in the assignment to help you locate the related online help for each exercise. Execute (run) the query for each question only once. When the questions for this assignment do not specify which rows to show in the results, assume that all rows should be shown. Similarly, when the questions do not specify which columns to show in the results, assume that all columns should be shown. When the questions specify that only certain columns should be included in the results, this does not imply that other columns cannot be used in the query (they should simply not be part of the output).

1. Select all data in a table:

Use the OrderDetails table and create a query that displays all the data in this table.

2. Select specific columns:

Use the OrderDetails table and create a query that displays ProductID and Quantity only.

3. Select specific rows (note that each query in the assignment is separate and run only one time. That is, 3.1a and 3.1b are different queries they are simply grouped in question 3.2 because they have a similar approach/concept for learning:

3.1) WHERE. Use the OrderDetails table and create a query that displays order line items:

a) with OrderID 10252.

b) with unit prices more than $200.

c) that are not for product 39.

d) with unit prices more than $50 and quantity more than 25.

e) with unit prices between 50 and 75 (inclusive of both 50 and 75).

f) with quantity more than 100 or unit price more than 200.

3.2) WHERE. Use the OrderHeaders table and create a query that displays all orders (rows):

a) placed in 2019 (you can assume that there is no later orders).

b) that were shipped after they were required.

c) that have not yet been shipped (shipped date is null).

3.3) DISTINCT. Use the OrderDetails table and create a query that displays:

a) all unique products sold (only show Product IDs in the results).

b) all unique products sold with a unit price of more than $50. (only show Product IDs in the results).

3.4) ORDER BY and/or SELECT TOP (note that SQLiteStudio uses LIMIT instead).

Use the products table and create a query that shows:

a) products sorted by UnitsInStock in descending order.

b) the first four products in the products table (no ordering).

c) the bottom 12 products in terms of UnitsInStock (i.e., fewest UnitsInStock).

3.5) LIKE and WILDCARDS.

Use the Customers table and create a query that shows all customers with:

a) "manager" in the ContactTitle.

b) company names that start with E.

4. ALIASES and CASE. Create new columns (fields) in the results.

Note that aliases are temporaryand not stored for reuse in a query.

a) Use the Products table and create a query that shows ProductName, UnitsInStock, UnitsOnOrder, ReorderLevel, and a calculated field named UnitsAvailable defined as UnitsInStock + UnitsOnOrder.

b) Use the Products table and create a query that shows ProductName, UnitsInStock, UnitsOnOrder, ReorderLevel, the calculated field UnitsAvailable (copy and paste the query you created in 4a), and a new calculated field named ReorderNeeded that displays Yes if UnitsAvailable < ReorderLevel and otherwise No.

5. Create VIEWS.

Note that views are virtual tables. If you need to troubleshoot a view at some point you can simply delete it and recreate it.

a) Copy and paste the query in 4a and create a view called ProductAvailability based on this query.

b. Using the ProductAvailability view that you created in 5a, create a query that only shows products that need to be ordered (that have UnitsAvailable less than the ReorderLevel).

6. GROUP and AGGREGATE (e.g., COUNT, AVG, SUM):

a) Using the Products table, create a query that shows for each supplier: the SupplierID and the number of products associated with the supplier (name this field NumberOfItems).

b) Using the OrderDetails table, create a query that shows for each order the OrderID and the total quantity sold (name this field TotalQuantity).

c) Using the OrderDetails table show for each product: (i) the ProductID, (ii) the average sales unit price (name this field AverageUnitPrice; you can simply calculate the average for each product across the different order detail rows and you do not need to adjust the average for the quantity sold in each order), (iii) the total quantity sold (name this field SumOfQuantitySold), and (iv) the number of times it has been sold (name this field NumberOfSales; you can simply calculate the number of sales orders).

7. INNER JOIN:

a) Using the OrderHeaders and OrderDetails tables, show OrderID (from the OrderHeaders table), OrderDate, ProductID, and Quantity. You can assume that all OrderHeaders have OrderDetails and that all OrderDetails have OrderHeaders.

b) Copy and paste query 7.a and only show orders placed in 2019.

8. INNER JOIN, WHERE, GROUP, and HAVING:

a) Copy and paste query 7.b, but only show each ProductID and for each product show a new calculated field named SumOfQuantitySoldIn2019 defined as the total quantity sold in 2019. Store this query as a view called ProductSales2019.

b) Copy and paste query 7.b, but only show products with SumOfQuantitySoldIn2019 greater than 200.

c) Create the same output you did in 8.b, but use the ProductSales2019 view (that you created in 8.a) instead of the OrderHeaders and OrderDetails tables.

9. LEFT JOIN (same as LEFT OUTER JOIN):

a) Using the Products table and ProductSales2019 view, show for all products, the quantity sold in 2019. Display the (i) ProductID and (ii) ProductName for all products in the Products table, and (iii) SumOfQuantitySoldIn2019 (include null values for products not sold in 2019).

b) Using the Products table and ProductSales2019 view, show ProductID, ProductName, and SumOfQualitySoldIn2019 for all Products that were not sold in 2019.

10. INNER JOINS: Join three tables:

Using the OrderDetails, OrderHeaders, and Products tables, determine for each product the average price received in 2019 and compare this amount to the list price (the unit price in the Products table). In your output display (i) ProductID, (ii) AveragePriceReceived (i.e., average sales unit price in 2019 for each product where sales unit price is UnitPrice*(1-Discount) from the OrderDetails table), (iii) UnitPrice from the products table, and (iv) a new calculated field that calculates the percentage difference between AveragePriceReceived and the UnitPrice in the products table. Name this field PercentPriceDifference.