CIS 336 STUDY Great Stories/cis336study.com CIS 336 STUDY Great Stories/cis336study.com | Page 77

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.