Imagine that Howard has asked you to write some queries to help him make better use of his data. For each information request below, write a single query that provides the answer set. When a task specifies which columns to show in the result, show only the columns listed and show them in the order specified. Save your queries and name them appropriately. Note: The “School” table is similar to the table you imported in a prior assignment except that the data are from Colorado rather than Arizona. Note: There are two “Price” fields in this database. The Product table has a field named “currentPrice.” This records the price at which each product is currently offered for sale. The OrderDetail table has a field named “price.” This field records the price actually paid for a product on a particular sale, which may differ for several reasons. A particular product may be discounted and therefore sell for a different price than currently listed. More importantly, the current price will likely change over time. As it does, we need to know what price was actually paid for the product when it was purchased.

Task #	Points	Task Description
1	9.5	List the customers (first name, last name, email addresses) who placed orders on July 4, 2013.  Name the query "Independence Day 2013" (without the quotes).
2	12.5	List customers who have purchased products with names beginning with "Trangia".  Show the First name, last name, email address and product name.  If a customer has puchased the same product more than once, show a row for each time the product was purchased.  Name the query "Trangia Buyers" (without the quotes).
3	12.5	Build the invoice detail for saleOrder # 3459.  Show the product name, quantity, price and amount.  Amount is defined to be quantity * price.  Name the query "3459 Detail" (without the quotes).
4	11	Build the Order Summary for saleOrder # 3459.  Show the following fields: orderTime, ShiptoName, itemsOrdered (the total number of individual products on the order), and totalPrice (the total amount of the order).  Name the query "3459 Summary" (without the quotes).
5	12.5	Build  Order Summaries (as described in the prior question) for all saleOrders made in June of 2011. Name the query "June 2011 Summary" (without the quotes).
6	11	Build a query to show the revenue generated by month for the entire dataset.  Show orderYear, orderMonth, and revenue (sum of price times quantity).  Sort the results in reverse chronological order.  Name the query "Revenue by Month" (without the quotes).
7	11	Build a list showing the popularity of products.  Show the product name, current price and total quantity sold (name this column: totalSold).  Order the list in descending order by totalSold.  Name the query "Products by Popularity" (without the quotes).
8	11.5	To the prior query, add a column showing the total revenue attributable to each product.  Name this field "Revenue".  Sort the results by revenue in descending order. Name the query "Products by Revenue" (without the quotes).
9	8.5	Add parameters to the prior query so that when opened, the user is prompted for a start date and an end date.  The prompts should read "start date" and "end date" with not additional words shown to the user.  The query should include all sales made on or after the start date but before the end date.  Name the query "Products by Revenue Date Range" (without the quotes).
Total:	100

Answer rating: 100% (QA)

Questions 1 SELECT Customer firstName Customer lastName Customer email FROM HOME JOIN JOIN SaleOrder View the full answer