Expert Answer:SQL Queries

Answer & Explanation:Please see the attached pdf file. I only need queries from 3.2 to onward. There is a databse file that i will send once the question is assigned.Thanks
acctg_333_f19_sql_assignment.pdf

Unformatted Attachment Preview

SQL Assignment
ACCTG 333 – Fall 2019
Professor Perols
Due Friday, November 8, 2019
Why should you learn SQL?
Technology is drastically changing the accounting industry…and 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 to be completed on your computer (PC or Mac). 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 and ask me questions, but you may also need to work on
the assignment outside of class (recommended). My instructional student assistant (ISA) will
also be available in class to answer questions. You may also email me specific questions.
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 textbook (chapter 4) and online help https://www.w3schools.com/sql/.
The SQL assignment is due by Friday, November 8th, as indicated in the syllabus. You will
submit your work on Blackboard (see the SQL Assignment folder on Blackboard). You will
submit your SQL queries, i.e., the SQL SELECT statements that you create. You will not submit
your output, i.e., the data that your queries return from the RDBMS.
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 on learning SQL rather than
installing databases. To install the application, browse to the download page for SQLiteStudio
and download and run the installer for your operating system (use the executable versions
unless you want to run SQLiteStudio from a thumb drive to use on different computers). Now
open SQLiteStudio (if you are on a Mac you may have to set the security permissions to allow
the SQLiteStudio to run) and then connect to the Northwind Database (see SQL Assignment
Page 1 of 4
folder on Blackboard). To connect to Northwind, first download Northwind_Original.db to your
computer. Then open SQLiteStudio and select Database (in the upper left corner)  Add a
Database. Under file, select the folder symbol on the right, browse to the downloaded
database, select the database, and click OK. The database may not be visible after connecting
to the database. If this happens then go to View and make sure that Databases are selected.
To start creating SQL select queries in SQLiteStudio, open the query editor by going to Tools 
Open SQL Editor.
Select Queries
Again, to complete the following 10 questions, you need to consult the textbook (chapter 4)
and online help https://www.w3schools.com/sql/. 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 rows using WHERE:
3.1) Use the OrderDetails table and create a query that displays order line items (rows):
a) with OrderID 10251.
b) with unit prices more than $200.
c) that are not for product 38.
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 line item total, UnitPrice*Quantity*(1‐Discount), of more than $6,000.
g) with quantity more than 100 or unit price more than 200.
3.2) 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) Use the OrderDetails table and create a query that displays:
a) all unique (distinct) products sold (only show Product IDs in the results).
b) all unique products sold with unit prices minus discounts of more than $50.
Page 2 of 4
3.4) Sort (order by) and Select Top (note SQLite uses limit):
Use the products table and create a query that shows:
a) products in descending order sorted by UnitsInStock.
b) the first five products in the products table.
c) the bottom 15 products in terms of 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 F.
4) Aliases and Case:
Create new columns (fields) in result sets.
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: a) Copy and paste the query in 4a and create a view called ProductAvailability based on this query. b) Using the ProductAvailability view from 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: the ProductID, 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), the total quantity sold (name this field SumOfQuantitySold), and the number of times it has been sold (name this field NumberOfSales). 7) Join tables: 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. Page 3 of 4 8) 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 100. c) Create the same output as 8.b, but use the ProductSales2019 view (from 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 and ProductName 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 the OrderDetails unit price after discounts), (iii) UnitPrice from the products table, and (iv) a new calculated field that calculates the percentage difference between AveragePriceReceived and UnitPrice in the products table. Name this field PercentPriceDifference. Page 4 of 4 ... Purchase answer to see full attachment

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more