In this lab exercise, we'll work with MS Access's QBE implementation in the sample movies database.

The Movies Database Schema

Start by downloading the movies database (H:/local/cpsc/341/movies.mdb or movies.mdb) and familiarizing yourself with its database schema. It's a rather large but simple database whose contents were skimmed from the internet movies database by a faculty member in the late 1990's. This makes its contents illegal for commercial use and quite dated.

Querying Movie Information

Write MS Access queries to determine the following information:

• What is the oldest movie in the database?
• How many movies are there in the database?
• What are the top 5 movies with respect to "votes"? How about the worst movie?
• Find all the movies in which "Arnold Schwarzenegger" played a role. Which one of these was the most popular? Which one did you like the best? Which of the characters do you think would make the best governor of California?
• Find all the movies that had a lead named Amanda (hint: use LIKE 'Amanda*' as your condition. The * is acts as a wildcard here.).
• What performer performed in the most movies?

Adding New Data

Add the data required to represent three recent movies that you think the database should have. Remember that you'll have to add the main actors/actresses, add the casting information, and make up the votes data. You'll also have to pick some new, unused ID number - anything is fine (e.g., 9000-9002).

Now, export your new additions to a new database and email it to me. I'll use this to upgrade the main file with newer, more useful movies. When you're done adding data, here's how to do the export:

1. Create a new, empty access database file somewhere on your system.
2. For each of the three tables in the movies database schema:
   1. Create a query that retrieves only your new data from the table (e.g., "movie").
   2. Choose Query-MakeTableQuery.
   3. Click on the "Run" button (i.e., the "!" on the toolbar). Enter a new table name when prompted (e.g., "new movie").
   4. Click mouse right on the new table, and select Export, specifying your new database file as the target.

Finding New Information

Find some new fact from the database that you think people in the class will be interested in. I don't care what it is. Perhaps it will be based on the new data that you added.

Checking In

We'll conclude with each person recounting: (1) the new data they added, and how they added it; and (2) the new information they found, and how they found it. Also, use KV to submit a text version of one of your multiple table queries from above on the remote instance of the SQL Server.