CIS 336 STUDY It's Your Life/cis336study.com CIS 336 STUDY It's Your Life/cis336study.com | Page 73

3. The Senior Customer Service Manager has requested the ability to create a report at any time that will show shipped orders that took some specified number of days to fulfill.
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.

3. The Senior Customer Service Manager has requested the ability to create a report at any time that will show shipped orders that took some specified number of days to fulfill.

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.