Henry Books Case

Instruction:

  1. You must use “SQL View” function of Microsoft Access. If you use “QBE” function of Access for generating queries, you will receive no points.
  2. Make sure to use “Henry Books” database for this assignment. Please download the database form the class website.

Score: 10 points

Deliverable: Access Database with answer queries

Questions 1 from 11 are for “PRACTICE.” Thus, you do not need to turn them in.

  1. List the book code and book title for every book.
  2. List the complete Publisher table (all rows and all columns).
  3. List the name of every publisher located in New York.
  4. List the name of every publisher not located in New York.
  5. List the name of every branch that has at least 10 employees.
  6. List the book code and title of every book that has the type HOR.
  7. List the book code and title of every book that has the type HOR and that is a paperback.
  8. List the book code and title of every book that has the type HOR or that has the publisher code PE.
  9. List the book code, title, and price for each book with a price that is greater than $10 but less than $20.
  10. List the book code and title of every book that has the type MYS and a price oflessthan $20.
  11. Customers that are part of a special program get a 15% discount off regular book prices. To determine the discounted prices, list the book code, tide, and discounted price of every book. (Your calculated column should calculate 85% of the current price, which is 100% less a 15% discount.)

Questions 12 from 21 are for the “ASSIGNMENT.” Thus, you MUST turn them in.

  1. List the book code and tide of every book that has the type FIC, MYS, or ART.
  2. List the book code, tide, and publisher code for all books. Sort the results by title within publisher code.
  3. How many books have the type MYS?
  4. Calculate the average price for each type of book.
  5. For every book, list the book code, book tide, publisher code, and publisher name.
  6. For every book published by Lb Books, list the book tide and book price.
  7. List the book tide and book code for every book published by Scribner that has a book price greater than $10.
  8. Create a new table named Fiction using the data in the BookCode, Tide, PublisherCode, and Price columns in the Book table for those books that have the type FIC.
  9. Use an update query to change the price of any book in the Fiction table with a current price of 8.00 to 8.50.
  10. Use a delete query to delete all books in the Fiction table that have the publishercode PE.