In the last chapter you modified some tables for the Snick's Board Shop. Now Caitlin would like you to help her create some queries to extract information from the database. Make the following changes for Caitlin, using the ch 10-05_student_ name file you created in Chapter 10.
a. Create a select query that lists the category name, manufacturer name, product name, and price for all products from Manufacturer ID 888. Save this query as Query A and then print it.
b. Create a select query that lists the manufacturer name, product name, and price for all products with a Category ID of 1. Save this query as Query B and then print it.
c. Create a select query that lists the product ID, category name, and style for styles that contain the word "Black". Save this query as Query C and then print it.
d. Edit the query you just created in the previous step. Add the manufacturer name field and remove the category field. Change the criteria of the query from styles that contain the word "Black" to styles that contain the word "Blue". Save this query as Query D and then print it.
e. Create a select query that lists product name, price, and quantity for all products with a price greater than $100. Save this query as Query E and then print it.
f. Create a select query that lists product name, price, and quantity for all products with a price greater than $100 but less than $130. Save this query as Query F and then print it.
g. Add a number field "Discount" to the Manufacturer table. Set the field size of this new field to Single and the Format to Percent. Set the Discount to 10% for manufacturers EMT and MOJ. All other manufacturers discount should be set to 0%. Create a select query that lists Product ID, Price, and Discounted Price (a new computed field: Price times 1 - Discount, formatted as currency) for all products sorted in ascending order by Discounted Price. Save this query as Query G and then print it.
h. Create an update query that increases the price of all products by 10%. (Remember to back up your file first.) Save this query as Query H. Open Query G again and then print it with the new prices.
i. Create a parameter query that lists the manufacturer name, product name, price, style, and quantity. The query should state "Enter Manufacturer ID". Sort the query in descending order by product name. Save this query as Query I. Run the query for Manufacturer ID SC and then print it.
j. Create a select query that sums the total retail value of the complete boards in inventory, formatted as currency. Save this query as Query J - 1, and then create the summation query. Save this query as Query J - 2 and then print it.
k. Create a select query that sums the retail value of all products by category (in currency format), sorted by type. (Hint: Use the Query J - 1 query, modify it by removing the criteria, and then save it as Query K - 1, using Query K - 1 as the source for your new query.) Your new query should have two fields: Category Name and Sum of Retail Value. Save the new query as Query K - 2 and then print it.
1. Make sure you keep a copy of this file for use in the next chapter.