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

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.

The two fields do not have the same meaning; they might not have the same type but you can’t tell since the game field doesn’t have a value big enough to assume leading zeroes.

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): GAMES
JOIN TYPE:				RELATIVE TO:
Field	Gamenum	Date	Versus	City	State
Table	Games	Games	Games	Games	Games
Total
Sort	X	X	X	X	X
Show
Criteria		See below		“Columbus”	“OH”
OR
OR

Additional room for Expressions, if necessary

Between #5/16/2009# And #6/15/2009#

Can also put: >=#5/16/2009# and <=#6/15/2009#

Can also put: >#5/15/2009# and <#6/16/2009# and all variations of

Can also: put the date field twice, take the AND out, and put the two dates on the same line (one per field)

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): Players
JOIN TYPE:				RELATIVE TO:
Field	ID	team	name
Table	Players	Players	Players
Total		Ascending	Ascending
Sort
Show	X	X	X
Criteria			Like “*, J*”
OR
OR

Additional room for Expressions, if necessary

Field ordering graded here as the team field must be to the left of the name field

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): Players
JOIN TYPE:				RELATIVE TO:
Field	ID	Name	Team	field
Table	Players	Players	Players	Players
Total
Sort
Show	X	X	X	X
Criteria			“A”	“infield”
OR			“B” or “C”	“outfield”
OR			See below

Additional room for Expressions, if necessary

Instead of using the “or”, the “C” can be on the last line but must also have “outfield” for the field listed again on the last line as well.

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): Players
JOIN TYPE:			RELATIVE TO:
Field	Team	ID
Table	Players	Players
Total	Group By	Count
Sort
Show	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

Query6
team	CountOfID
A	8
B	3
C	5

Grade show line

Field order is an issue since must match what given

in the query

Can use any field on the table for ID but must

match the field name on the resulting dynaset

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): BatStats
JOIN TYPE:				RELATIVE TO:
Field	Player	Game	Batavg:
Table	BatStats	BatStats
Total
Sort
Show	X	X	X
Criteria
OR
OR

Additional room for Expressions, if necessary

Batavg: [hits]/[@bats]

Optional table name okay; for example, [batstats]![hits]

Must leave table box blank for the expression

SCORE ________/50