In the last chapter you modified some tables for the Wine Depot. Now Barbara would like you to help her create some queries to extract some information from the database. Make the following changes for Barbara using the chl0-04_student_name file you created in Chapter 10.
a. Create a select query that lists the type, winery, and price for all wines on hand from winery #4. Save this query as Ch 11 Wine Depot Case a and then print it.
b. Create a select query that lists the type, winery name, and price for all wines whose buyer name is Michael Jackson. Save this query as Ch 11 Wine Depot Case b and then print it.
c. Create a select query that lists the SKU and type for wines types that contain the word "Blanc". Save this query as Ch 11 Wine Depot Case c and then print it.
d. Edit the query you just created in 2c above. Add fields for cost and vintage and remove the SKU field. Change the criteria of the query from "types that include the word Blanc" to "types that begin with the letter S". Save this query as Ch 11 Wine Depot Case d and then print it.
e. Create a select query that lists type, price, winery name, buyer name, and phone number for all wines with a price greater than $35. Save this query as Ch 11 Wine Depot Case e and then print it.
f. Create a select query that lists type, price, winery name, buyer name, and phone number for all wines with a price greater than $35 but less than $50. Save this query as Ch 11 Wine Depot Case f and then print it.
g. Create a select query that lists SKU, type, winery name, price, quantity, and retail value (a computed field: quantity times price, formatted as currency) for all Chardonnay wines, sorted in descending order by retail value. Save this query as Ch 11 Wine Depot Case g and then print it.
h. Create an update query that changes the price of all wines to be 150% of cost. (Remember to back up your file first.) Save this query as Ch 11 Wine Depot Case h.
i. Create a parameter query that lists SKU, type, winery name, and price.
The query should ask "What type of wine?" Sort the query in descending order by price. Save this query as Ch 11 Wine Depot Case i. Run the query for Pinot Noir wines and then print it.
j. Create a select query that sums the total retail value of the Chardonnay wine inventory, formatted as currency. Be sure to list the wine type in your query. Save this query as Ch 11 Wine Depot Case j and then print it.
k. Create a select query that lists SKU, type, winery name, cost, quantity, and total cost (a computed field: quantity times cost, formatted as currency) for all wines, sorted in ascending order by SKU. Save this query as Ch 11 Wine Depot Case k and then print it.
1. Create a select query that sums the cost of wine inventory by type (in currency format), sorted by type. (Hint: Use the Ch 11 Wine Depot Case k query as the source for your new query.) Save the new query as Ch 11 Wine Depot Case 1 and then print it.
m. Make sure you keep a copy of this file for use in the next chapter.