CIS 336 STUDY Future Starts Here/cis336study.com CIS 336 STUDY Future Starts Here/cis336study.com | Page 72

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.