AU09 CSE200 QUIZ#4

Name _____________________________ Seat# ________ Lab=> Friday ______ 9:30am______ 11:30am

Instructions:

· Put away all books, papers, and calculators. Turn off all beepers and cell phones.

· Answers must be legible or they will be marked incorrect.

· Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query. Remember to put quotes on the like and text values and pound signs around dates.

· Aggregate functions: Group By, Sum, Avg, Min, Max, Count, Where, Expression.

· REMEMBER that all the data is not shown. Be sure your queries will work with additional records.

· Leave the “join on” and “relative to” options on the query design view blank.

The tables given represent the database system for the Excel-lent Adventures Company. The following tables are a collection of information about the people who visit the facilities and the adventures (trip options) they take.

The TRIPS table is a list of all the trips that all of the people are taking. There may be multiple trips per person with no limit on the number of trips each person can take (unlike the Excel version). Each trip option has details associated with it on the OPTIONS table; and each person is associated with a family group name on the FAMILIES table.

The OPTIONS table defines the details about each of the different trip options available, including the type of trip (raft, trail or rock although more types can be added later), the difficulty level, the length in feet and the time in minutes, respectively.

The FAMILIES table is the list of families (i.e. organized groups) that are currently attending the adventure company’s facilities.

REMEMBER THAT ALL OF QUERIES ARE SINGLE TABLE QUERIES ONLY

Name ___________________________ Seat# ________ Lab=> Friday ______ 9:30am______ 11:30am

1. (8 pts) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) and foreign key (s) (if any) for each table AND draw relationship lines between tables (exactly the same as you would see in Access).

FAMILIES table Primary Key Foreign Key(s)		OPTIONS table Primary Key Foreign Key(s)
	TRIPS table Primary Key Foreign Key(s)

2. (4 pts) I want to create a 4^th table based on the first name of the TRIPS table. Some of the fields for this table could include birth date, hair color, eye color, height, weight, etc. Assume the name of this new table is PEOPLE. How would I relate the PEOPLE table to the TRIPS table? That is, how would I create a relationship between the two tables? NOTE: Remember that on the TRIPS table, there are status and family# fields to help determine the differences between two people with the same name. Having duplicate names is a problem that you will need to consider when creating the PEOPLE table.

3. (7 pts) Create an Access query (in the design view given below) to list option number for all the raft and trail trips that last at least an hour (i.e. 60 minutes).

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

4. (7 pts) Create an Access query (in the design view given below) to list the first name and option number for all the trips that parents are taking sorted first by option number (starting with option#1) then by first name (alphabetically).

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

5. (6 pts) Create an Access query (in the design view given below) to list the first name of all the people whose names begin with the letter C. Assume that each name is unique for this problem only and make sure that each name only appears once. NOTE: the resulting dynaset for this problem has 3 records with the first name the only field per record.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

6. (10 pts) Create an Access query (in the design view given below) summarizing by the option#, to list the number of trips taken for each option. You do not have to list the option if no one is going on that type of trip. Show the resulting dynaset (don’t forget to include field names) in the given area below the query grid.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Query6

Additional room for Expressions, if necessary

7. (8 pts) Using the above query, saved and called Query6, and assuming it costs $25 per person for each trip, create an Access query (in the design view given below) to list the option# and the total cost for each trip option.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

SCORE ________/50