[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

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 customers 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 rows 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:

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:

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