a . Create a view named SHIPPING _ TIME that lists only customer _ first _ name , customer _ last _ name , order _ date , shipped _ date , and the calculated field days _ to _ fulfill ( use the DATEDIFF function ) showing the number of days between when the customer placed the order and when it was shipped . Show the data from this view .
Now let ‘ s do some queries by adding sorting and filters USING THIS VIEW , WITHOUT CHANGING IT .
b . Use the view to display the data sorted by highest to lowest days to ship c . Use the view to display only the orders that took less than 10 days to ship . d . Use the view to display only the orders that took more than 30 days to ship .
4 . Queries that require joins and aggregate functions can be easier to construct when using a view as a ― temporary ‖ table . Consider a report to show total sales by artist .
a . First create a view called SalesData that displays the order _ id , item _ id , the calculated field ItemTotal ( which is quantity times price ), the title and artist _ id .
b . Display the data in the SalesData view sorted by artist _ id . Does this help you to ― visualize ‖ how to group the data to create the totals ?
c . Create a query USING THIS VIEW and the appropriate aggregate function to display artist _ id and the total sales for each artist .
d . Now join to the artist table in order to display the artist _ name along with the total sales .
5 . Now use this same method to display the total sales per customer .
a . Create a view called SalesData with the appropriate data . At a minimum you will need customer _ id and the calculated item total . DO NOT use the customer table in this view , it will be joined later .
b . Display the data in your view sorted by customer _ id . Does this help you to ― visualize ‖ how to group the data to create the totals ?
c . Create a query USING THIS VIEW and the appropriate aggregate function to display customer _ id and the total sales for each customer .
d . Now join to the customer table in order to display the customer _ name as a single field named Customer along with the total sales . Sort the report by Total sales in descending order .