- Create a folder on the C: drive or your memory stick to contain your
homework assignments for IT 240.
Name it something like it240-smith, where smith
is your last name.
- Download the Access file HenryBooks.mdb
and save it in your it240-smith folder.
- Open the HenryBooks.mdb database (click or double-click on it). If there is a security warning, select open to open the database.
The tab on the left of the window contains several objects. Select Tables (it's probably already selected).
Open the tables Author, Wrote, Book, Publisher, Branch, and Inventory.
Examine the fields and records, both in Design view and Datasheet view. Note
any primary keys and foreign keys. Now click on the Queries object.
- Queries: For each of the following, use SQL to obtain the desired results.
[Note. To get to SQL View: Double click on Create Query in Design View | Close the Show Table window | View | SQL View]
- List the author number and last name for every author.
- List List the name of every publisher not located in Boston.
- List the book code and title of every book that has the type SFI and that is a paperback.
- List the book code and title of every book that has the type SFI, HOR, or ART.
- Calculate the average price of each type of book. List the book type and average price.
- For every book, list the book code, book title, publisher code, and publisher name.
- (more challenging) For authors that have written more than one book, list their names (first, last) and the number of books
they have written.
- Requirements:
- Your seven queries in part 4 above, must be named Q1,...,Q7.
- Your database file must be called smith-H2.mdb, where Smith is your last name.
- Double check that your database file opens properly and contains your seven queries.
- Submit your smith-H2.mdb file using COLweb.