WI10 CSE200 QUIZ#4

Name _____________________________ Seat# ________ Lab=> Friday ______ 1:30pm______ 3:30pm

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 Ski Company. The following tables are a collection of information about the people who visit the ski facilities:

The SKIER table is a list of skiers (well a portion of them anyway), their name, gender and level.

The TYPE table defines the details about each of the different levels of skier. Each type as seen in the first column here, has defined Yes/No fields which describe what level is associated with each type. Although not enforced, these associations are recommendations so that skiers can choose the runs that will be fun, enjoyable and not too dangerous for their skill level.

The RUN table is the list ski runs that the skiers can ski down. The length is given in feet and the type of each run is designated as well.

The RUNFUN table is a list of the skiers and the runs that they skied on.

REMEMBER THAT ALL OF QUERIES ARE SINGLE TABLE QUERIES ONLY

Name ___________________________ Seat# ________ Lab=> Friday ______ 1:30pm______ 3:30pm

1.

SKIER table

Primary Key

Foreign Key(s)

(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).

RUNFUN table

Primary Key

Foreign Key(s)

RUN table

Primary Key

Foreign Key(s)

TYPE table

Primary Key

Foreign Key(s)

2. (4 pts) The skier fields (SKIER and RUNFUN tables) and the run fields (RUN and RUNFUN tables) information sure does look very similar. How do you know which fields to connect to which other fields? Of the 3 rules for making relationships, it’s these two (that you need to list) which help in making the determination of the correct relationships.

3. (7 pts) Create an Access query (in the design view given below) to list each skier number and name for all the skiers whose name starts with the letter S (all of the names will not always start with the letter S). Be sure to order the list by females then males; and within each gender, list the level in alphabetical order.

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 types that include either advanced or expert skiers (or both). NOTE: it’s okay if other types are designated, but if advanced/expert are included, that’s what this problem is looking for.

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

Additional room for Expressions, if necessary

5. (8 pts) Create an Access query (in the design view given below) to list the names of all the beginner skiers that are male and all the expert skiers that are female.

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 skier, the total number of runs they ski and the cost of all their runs if each run costs $15. Once you determine the query grid, show the resulting dynaset in the area given below.

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

Query6

Additional room for Expressions, if necessary

7. (6 pts) ) Create an Access query (in the design view given below) summarizing by type, the total length of all the runs in feet for each type. ANSWER THIS QUESTION BELOW: Can I add the run number as part of the resulting dynaset and still have it work as originally given? Why or why not? i.e. be sure to explain!

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

Additional room for Expressions, if necessary

SCORE ________/50