[Solved] CIS560 Homework 2

Submit a query for each of the following questions. All queries will be solutions against the WideWorldlmporters database. You are only to use constructs covered in class through the topic of JOINs. Your solutions should not include subqueries.

Question 1

Write a query that will return sales information for each customer category and year. The columns required in the result set are:

• OrderYear The year the orders were placed.
• CustomerCategoryName As it appears in the table CustomerCategories.
• CustomerCount The number of unique customers placing orders for each CustomerCategoryName and
• OrderCount The number of orders placed for each CustomerCategoryName and
• Sales The subtotal from the orders placed, calculated from Quantity and UnitPrice of the table OrderLines.
• AverageSalesPerCustomer The average sales per customer for each CustomerCategoryName and

The results should be sorted in ascending order, first by order year, then by customer category name.

Question 2

Write a query that will return sales for 2014 and 2015, with each years sales in a separate column. We should only have one row for each customer who placed an order in 2014 or 2015.

The columns required in the result set are:

• CustomerlD As it is in the table Customers.
• CustomerName As it is in the table Customers.
• 2014Sales The total sales for the customer in 2014.
• 2015Sales The total sales for the customer in 2015.
• TotalSales The total sales for the customer in both years.

The three sales columns are calculated from Quantity and UnitPrice of the table Sales.OrderLines. Also note that an identifier starting with a digit is irregular, which requires square brackets or double quotes as a delimiter.

11/12/2018 Homework 2

The results should be sorted with the highest total sales at the top, and if two customers have the same total, use the CustomerlD in ascending order to make the results deterministic.

Question 3

Write a query to return 2015 sales information for each supplier. We would like to include all suppliers in the result set, regardless of whether their products were sold in 2015.

Sales are determined using Sales.Orders and Sales.OrderLines as in the previous two questions. However, since we are asking for this information from the perspective of the supplier, you also need to use the tables Warehouse.Stockltems and Purchasing.Suppliers. To better understand a table structure, see Browsing Table Structures.

The columns required in the result set are:

• SupplierlD As it appears in the table Suppliers.
• SupplierName As it appears in the table Suppliers.
• OrderCount The number of orders placed on products for each supplier.
• Sales The subtotal from the orders placed, calculated from Quantity and UnitPrice of the table OrderLines.

The results should be sorted such that the supplier with the highest sales is at the top. If two suppliers have the same sales, next use the order count with the highest count at the top. If two suppliers have the same sales and order count, use the supplier name in ascending order as the final tie breaker. This will ensure a deterministic result.

Submission

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