[Solved] CIS560 Homework 1

Submit a query for each of the following questions. All queries will be solutions against the WideWorldlmporters database, and only use the Sales.Orders table.

Question 1

15 points Write a query to show the monthly totals for orders placed in 2015. The results should have no more than 12 rows and include the following three columns.

• Month The month of year, values 1 12.
• CustomerCount The number of unique customers who placed an order in the month.
• OrderCount The total number of orders placed in the month.

The resulting rows should be sorted by month in ascending order.

Question 2

15 points Write a query that provides lifetime customer information for each customer that has placed an order. Since we are interested in lifetime information, all orders in Sales.Orders are to be considered. The result should include the following columns:

• CustomerlD As it exists in the table Orders
• OrderCount The total number of orders placed by the customer.
• FirstOrderDate The date of the first order placed.
• LastOrderDate The date of the last order placed.
• CustomerCategory A character value that contains one of three values:
• Few Orders Returned if the customer had fewer than 25 orders.
• Growing Customer Returned if the customer had at least 25 and fewer than 100 orders.
• Large Customer Returned if the customer had at least 100 orders.

The resulting rows should be sorted by CustomerlD in ascending order.

Question 3

20 points Write a query that returns paginated results from the Sales.Orders table. The rows are sorted by OrderlD in ascending order, and the number of rows is limited to a provided page size and determined by which page is requested.

Input

The inputs for the query should each be made as variables at the top of your solution and are as follows:

All orders with an order date between @FirstOrderDate and @LastOrderDate are candidates for the result set, however, only those rows that should belong on the page defined by @Page and @PageSize are returned. For example, if there are 1,000 orders with an order date that falls between @FirstOrderDate and @LastOrderDate, and @PageSize is 100 with @Page as 1, then the first 100 of the 1,000 orders will be returned. If @Page = 2, then the orders 101 200 will be returned, and so forth.

Output

The columns from the table to include are:

• OrderlD As it exists in the table Orders
• OrderDate As it exists in the table Orders
• CustomerlD As it exists in the table Orders
• SalespersonPersonlD As it exists in the table Orders

Again, the rows are sorted by OrderlD in ascending order.