[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.