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

· Prior to starting to write the queries, assume you have corrected all the data integrity issues, if any.

The tables given represent the database system for the Excel Baseball Team franchise. The following tables are being used to collect information about the team and its players:

The TEAM table is a list of the 3 different types of teams that are included in the Excel franchise.

The PLAYER table defines the name of each player, the team they play on as well as their position (field and description).

The GAME table is the schedule for the season. That is, the date a game is being played, against whom, and where. NOTE: Home games are specified as being played in Columbus, OH. FYI: You might think that the table is currently not defined very well since which team (A, B or C) is not designated; and it could possibly be updated in the future. However, the following table does implicitly determine which team was playing that particular game… you just don’t have to worry about that right now (see the below bolded/centered instruction).

The BATSTATS table is loaded with the statistics (at bats, hits, runs batted in and homeruns) for each player per game. Each player does not necessarily play in every possible game, although they can; some players may not even play in a single game, so the records here will always vary according to who plays when. FYI:

REMEMBER THAT ALL OF QUERIES ARE SINGLE TABLE QUERIES ONLY

Using more than one table will be counted as INCORRECT

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

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) of each table AND draw relationship lines between tables (exactly the same as you would see in Access).

		BATSTATS table Primary Key Foreign Key(s)
TEAM table Primary Key Foreign Key(s)
			GAME table Primary Key Foreign Key(s)
	PLAYER table Primary Key Foreign Key(s)

2. (4 pts) When creating my relationships, I accidently related the ID# field on the PLAYER table with the game field on the BATSTATS table. Explain why this was the wrong thing to do.

3. (7 pts) Create an Access query (in the design view given below) to list the game number and team playing for all the home games played by the Excel franchise from May 16, 2009 to June 15, 2009 (inclusive).

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 player identification number and the team letter for all the Excel franchise players whose first names start with the letter J. Be sure to sort the list by team letter first (A to C) then by player name (alphabetically).

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

Additional room for Expressions, if necessary

5. (7 pts) Create an Access query (in the design view given below) to list the players identification number and the name for all the infielders on team A and the outfielders from either team B or C.

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 team letter, to list the number of players on each team. 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

Additional room for Expressions, if necessary

Query6

7. (7 pts) Create an Access query (in the design view given below) to list the player identification number, the game number and the batting average per game that each player played.

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

Additional room for Expressions, if necessary

SCORE ________/50