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.