Queries

Assignment 1

Open the database Films.accdb.

Create a query showing all of the films which took more than $400,000,000 in 1998, and save this as HighGrossing1998.  Your datasheet should look like this:

For the BoxOffice criteria, type in >400000000

Create another query showing all of the films released in the 1960s, and save this as SixtiesFilms.

Another way of saying this is to ask for all the films released on or after 1960 and before or during 1969.

Your answer should look like this:

The effects of inflation mean that most films are recent ones

Close down both queries and your database.

Assignment 2

Open the database Films.accdb.

Create a query which shows the films in the following order:

· With the most recent films first; and

· Where there are two or more films for the same year, sort these into alphabetical order.

Your final answer should look like this:

The 2006 films, for example, are subsorted alphabetically

Save this query as FilmsOrdered, then close it down.

Assignment 3

Open the database Movies.accdb.

Create a query to show for each director’s year of birth:

· The average profit (box office minus budget) for films; and

· The number of films released.

If you sort the rows by average profit (with the highest number first) you should get:

1961 was a good year …

Save this as Wonder who was born in 1961, and close it down.

A challenge: how would you create a query to list out the names of the directors born in the highest-grossing year?

Assignment 4

Open the database Pets Database.accdb.

Create the following parameter queries:

· List either boy pets or girl pets, saving as parameterPetsOfGender;

· List pets name and type who like a particular food, saving as parameterPetsOfFood;

· List pets of a particular type, saving as parameterPetsOfType.