IT452 Unit 5 Table Expressions

Description

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.
(6 rows)

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’.
(4 rows)

6. Create a VIEW called TopSales that shows the TOP 10 percent of sales. Verify the results of the view in a SELECT clause.
(216 rows)

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.