[Solved] CIS560 Homework 3

Submit a query for each of the following questions. All queries will be solutions against the WideWorldlmporters database. The questions make specific request regarding implementation requirements, but you may need additional constructs as well. For example, if the question is asking for a correlated subquery to calculate a required field, you may also need joins in your solution.

Question 1

10 points – Write a query to return all orders from January 2016 placed by customers in the “Computer Store” category. Your solution should use a self-contained subquery to identify the customers belonging to the “Computer Store” category.

Required Tables

• CustomerCategories
• Customers
• Orders
• OrderLines

Result Columns

• OrderlD — Identifier of the order as it exists in Orders.
• OrderDate — The date of the order as it exists in Orders.
• CustomerlD — The identifier of the customer placing the order.
• OrderTotal — The total for the order calculated using the Quantity and UnitPrice from OrderLines.

The results should be sorted with the highest order total first, then by OrderlD in ascending order to make the results deterministic. Partial Results

With the correct solution, the first two rows should be as follows:

OrderlD OrderDate CustomerlD OrderTotal

64519	2016-01-08	1036	18405.00
65309	2016-01-21	1055	12643.00

11/12/2018 Homework 3

Question 2

10 points – Write a query to return suppliers from the category of “Novelty Goods Supplier” for which no stock items are kept. Your solution should use a correlated subquery to indicate whether a given supplier has stock items.

Required Tables

• SupplierCategories
• Suppliers
• Stockltems
• Cities
• StateProvinces

Result Columns

• SupplierlD — Identifier of the supplier as it exists in Suppliers.
• SupplierName — Name of the supplier as it exists in Suppliers.
• City — Name of the postal city of the supplier as it appears in CityName of Cities.
• State — Postal state of the supplier as it appears in StateProviceCode of StateProvinces for the postal city.
• PostalCode — As it appears in the PostalPostalCode of Suppliers.

The results should be sorted by supplier name in ascending order.

Partial Results

With the correct solution, the first row should be as follows:

SupplierlD SupplierName City State PostalCode

8 Lucerne Publishing Jonesborough TN 37659

Question 3

10 points – Write a query that returns all orders for customer with a CustomerlD of 90, along with the order total and number of days since the customer’s previous order. You should use a correlated subquery to calculate the number of days since the previous order.

Required Tables

• Orders
• OrderLines

Result Columns

• OrderlD — Identifier of the order as it exists in Orders.
• OrderDate — The date of the order as it exists in Orders.

11/12/2018 Homework 3

• OrderTotal — The total for the order calculated using the Quantity and UnitPrice from OrderLines.
• DaysSincePreviousOrder — The days since the previous order for the customer. In addition to using a correlated subquery, you should use the DATEDIFF (https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql) Its first argument should be DAY. The second argument would be the earliest of the two dates compared, which in our case is the date of the previous order. The third argument would be the row’s order date, which would be OrderDate from Sales.Orders.
• The results should be sorted by OrderlD in ascending order.

Partial Results

With the correct solution, the first two rows should be as follows:

OrderlD	OrderDate	OrderTotal	DaysSincePreviousOrder
1455	2013-01-29	365.00	NULL
1890	2013-02-06	915.00	8

Question 4

10 points – Write a query to return sales information for each customer who placed an order in 2015. Your solution should use a derived table to calculate the OrderCount and Sales for each CustomerlD. Required Tables

• Customers
• Orders
• OrderLines

Result Columns

• CustomerlD — Identifier of the customer as it exists in Customers.
• CustomerName — The name of the customer as it exists in Customers.
• OrderCount — The number of orders place in 2015 by each customer.
• Sales — The total sales for each customer in 2015. Sales are calculated using the Quantity and UnitPrice from OrderLines.

The results should be sorted with the customer having the highest sales first, then by CustomerlD in ascending order to make the results deterministic. Partial Results

With the correct solution, the first two rows should be as follows:

CustomerlD	CustomerName	OrderCount	Sales
820	Knut Svensson	55	150701.50
996	Laszlo Gardenier	38	150506.70

Question 5

11/12/2018 Homework 3

10 points – Use the same requirements as with Question 4 and write another query using a common table expression rather than a derived table to calculate the OrderCount and Sales for each CustomerlD. The results should be equivalent to those in Question 4.

Submission

Please submit your solution to each question in a single SQL file clearly marking each question with a comment line, or each in a separate file with a file name clearly indicating the question it solves. Include a comment line above each solution indicating which question it answers. Please do not submit your results, only the SQL solutions.

Five Questions – 10 Each