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

skier

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

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.

You already know they are all the same type so the other 2 rules left are:

1. Same meaning

2. One is a primary key

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.

GENDER field must be to the left of the LEVEL field

(can be first and last field or 2^nd and 4^th… just to the left of)

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.

No quotes allowed (see description of TYPE table – specifies it’s a yes/no field, not a text field)

No extra fields needed although the below is also acceptable:

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.

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.

Additional room for Expressions, if necessary

Show and order of fields from the query grid to the resulting dynaset must match.

The count field name can be “skier” but must match title on resulting dynaset to be “countofskier”

Can make up own calculated field name, but again must match resulting dynaset

Make sure table is left BLANK for CALCULATED field

Query6
skier	CountOfrun	TOTCOST
1	3	45
2	2	30
3	6	90
4	3	45
5	2	30
6	1	15
7	1	15
9	5	75
10	1	15

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!

NO, since it would create a lower detail level

i.e. there would only be groups of 1 since the run data is unique (i.e a PK)

SCORE ________/50