[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 year’s 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.