IT452 Unit 5 Table Expressions
1. What is the average number of order line items over all orders? The Sales.OrderDetails table has one row for each product for each order. Each row represents a “line item” on a sales invoice for the orderid. Start by writing a query that returns the number (COUNT) of line items (rows) for each orderid. Modify that query to CAST the line item count as decimal (8,4). Then use that query as a derived table to get the average number of line items over all orders.
(Returns 9 rows)
2. Create a view named Sales.LOLJO_Orders that lists all the orderid values for orders placed by customer ‘Customer LOLJO’. (Requires an Inner Join)
(Returns 10 rows)
3. Create a view named Sales.WhoSoldToLOLJO that lists the empid, lastname, and firstname of all employees who have sold to customer ‘Customer LOLJO’. Execute a Select query to show the results of the view.
4. Write a CTE to extract the employee with the most orders. Write the Outer query to display the EmployeeID and Highest Orders Sold Count for that Employee Result: (empid 4, 156 orders)
5. Create an inline table-valued function named Sales.fCustOrders that takes a customer name as an argument and returns the orderid and orderdate values for orders placed by that customer. The orderdate should have the date only, no time (Hint: CAST). Your project should have your CREATE FUNCTION statement. Query the function to see the results from the function for the argument (page 165) for companyname ‘Customer MLTDN’.
6. Create a VIEW called TopSales that shows the TOP 10 percent of sales. Verify the results of the view in a SELECT clause.
7. Encrypt the TopSales View and bind the schema so that it cannot be dropped or altered created in #6. Test it by trying to remove the producID from the underlying Table and show the error
8. Change the TopSales view from # 7 so that it contains a filter that only has qty = 130 and does not allow modifications. Test it with an Update Statement by trying to change the quantity.