CIS 336 All Assignments CIS 336 All Assignments | Page 55

d. Display the data using the customer table to verify that the change has been made.
Show all commands in your answer sheet along with the output of the commands.
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.
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?