Create and test all your queries inside Recess Save each queryin your access file using the query sumber as the queryname (so you will have queries ramadol rough I saved in Ace 00 NOT BE to generate your statement Pastel your sot into the word document under the roton Database information database keep track of the story of the teams to eached on team number manager number team.coachubernder ruber wenique attributes to the age database keep track of the hunory of all the coaching tones at each coach has performed Managers were separate from coaches Manager noted. There is one to one relationship between team and manager but do not have to have mange werlalumber (for boting only fortem in the affiliation table, the Arts tribute indicristhenber of years that appeared on a team, the batting average for the years that a perdonator The WORKPERIENCE table cep track of each coat's worthory with am. The WEXCurrent field indicates which is the coach current caching. A coach can only hold the sme potion with a mence 1 Listall team names of teams that have manager 2 strames of all players and her cute metamore Only include players who are Currently 29 List the names and number of players who were for more than two years but less than five years with the current team 4 List the names and cities of all the teams with more than I current players List the results in evindihabetical order byta ut all of the coachnames we have to work ach coach per team between 2 and 10 years List the total number of years of experience of Coach Taylor with the Baper 7. list the number of different types of experience antrenamedCount that Coach Adams had not including his currento tation day the coach's name List all the Coachnames that have had more than 10 yearstieperience across the 9 List the number of different manufacturen who make bats for the leagues teamDeplay the result in an attributin named Number in the same position with a team once. 1. 2 3. 4. 5. 6. 7. 8. List all team names of teams that currently have a manager. List names of all players and their associated current team name. Only include players who are currently active and have a batting average above 290. List the names and numbers of all players who have played for more than two years but less than five years with their current team. List the names and cities of all the teams with more than 3 current players. List the results in reverse alphabetical order by team name. List all of the coach names who have had average work experience as a "pitching coach' per team between 2 and 10 years. List the total number of years of experience of Coach Taylor with the Expos. List the number of different types of experience (as an attribute named: ExpCount) that Coach Adams has had not including his current position. In addition, display the coach's name. List all the Coach names that have had more than 10 years cumulative experience across all their jobs. List the number of different manufacturers who make bats for the league's teams. Display the result in an attribute named NumBatMan. 10. Find the names of the players who played for any team for at least six years (note that this could be over multiple stints with the same team). 11. List the average player age for each team where the team's average age is greater than 26. Include the team name in your response. 12. List the names of the league's oldest players whose names begin with the letter 's. 13. List the names of ALL teams, and their managers name (if they have one) 14. Use a level 1 nested query to display PlayerNum, Player Name and Age of players who have an average batting average of over 250 across all teams they have played for. 15. List player names and their overall batting average across all teams they have played for. Restrict your result to players who are older than 18 and have played at least one game with the Expos. List the CoachNames of coaches who have coached for every Team. (this is a division problem) 9. 16. H AFFILIATION - Access Table Tools File Home Tables * AFFILIATION A BAT E COACH MANAGER PLAYER 2 TEAM Create External Data Database Tools PLAYERNUM. TEAMNUM. AffilYrs 358 15 358 20 523 12 1131 20 1779 15 1779 24 2007 24 4280 15 4280 20 4319 15 5410 12 6564 20 6564 24 8093 12 8093 15 8093 20 8093 23 8093 24 8366 20 0 Help Fields Table Tell me what you want to do AffilBatAvg - AffilDateStart AffilateEnd AffilCurrentT. Click to Add- 5 0.300 1998-01-01 2002-12-31 3 0.320 2003-01-01 12 0.257 1994-01-01 1 0.283 2005-01-01 6 0.246 1997-01-01 2002-12-31 3 0.240 2003-01-01 3 0.290 2003-01-01 1 0.195 2002-01-01 2002-12-31 3 0.227 2003-01-01 4 0.298 2002-01-01 5 0.307 2001-01-01 12 0.310 1991-01-01 2002-12-31 3 0.280 2003-01-01 5 0.250 1986-01-01 1990-12-31 3 0.300 1991-01-01 1993-12-31 2 0.240 1994-01-01 1995-12-31 2 0.290 1996-01-01 1997-12-31 8 0.265 1998-01-01 7 0.283 1999-01-01 WORKEXPERIENCE OKKIIKIKKKKKKKKD Record: 5 of 19 in Search H Table Tools BAT - Access Tell me what you want to do Help File Home Tables AFFILIATION Create External Data Database Tools Fields Table TEAMNUM - SERIALNUM - BatManuf - Click to Add. B01 Acme 12 B09 General 12 B13 Acme 15 B01 Acme 23 B03 United 23 B04 Modern 24 B18 General 24821 United COACH MANAGER BE PLAYER TEAM 0 WORKEXPERIENCE Table Tools Table File Home Tables AFFILIATION Fields Tell me what you want to do CoachNum . Click to Add COACH - Access Create External Data Database Tools Help COACHNAME - CoachPhone . CoachAddress Adams 555-1524 + Baker 555-3690 + Dooley 555-7321 + Jackson 555-2444 Johnson 222-3423 + Simpson 234-2342 555-4820 Victor 555-9327 Wilson 222-2222 1 2 3 BAT COACH MANAGER PLAYER TEAM WORKEXPERIENCE Taylor 4 5 6 7 8 9 0 BE 111 MANAGER - Access Table Tools File Home Create Table Tables AFFILIATION + + External Data Database Tools Help Fields MgrNum MgrName - MgrWinPctg - Click to Add 1 shoobotter 0.4 2 carmaletto 0.5 3 shinster 0.2 (New) 0 BAT COACH MANAGER PLAYER TEAM WORKEXPERIENCE - PLAYER - Access Table Tools File Home Create Table + Tables AFFILIATION BAT + + + COACH + External Data Database Tools Help Fields PlayerNum , PLAYERNAM - PlayerAge - Click to Add 358 Bevens 21 523 Bullock 32 1131 Bean 28 1779 Jones 25 2007 Dobbs 4280 Cohen 25 4319 Ross 24 5410 Smith 27 6564 Linton 24 8093 Smyth 21 8366 Gomez 33 0 27 MANAGER PLAYER + TEAM + WORKEXPERIENCE + + Table Tools TEAM - Access File Home Create Help Tables AFFILIATION External Data Database Tools TEAMNUM - TEAMNAME - TeamCity 12 Dodgers Los Angeles 15 Giants San Francisco 20 Yankees New York 23 Expos Montreal 24 Tigers Detroit Fields Table Tell me what you Mgr Num - Click to Add- 1 2 BAT 3 B COACH + E MANAGER 0 BE PLAYER E TEAM BE WORKEXPERIENCE Table Tools Home Create File Tables AFFILIATION BAT COACH WORKEXPERIENCE - Access External Data Database Tools TEAMNUM - WEXP_Type 12 Batting Coach 15 3rd base coach 20 Batting Coach 23 Pitching Coach 24 Batting Coach 12 Batting Coach 12 Pitching Coach 24 Batting Coach 23 3rd base coach 23 Batting Coach 24 Pitching Coach 0 1 MANAGER BE Help Fields Table Tell me what you want to do WEXP_Yrs WEXP_Curre - CoachNum - Click to Add 10 5 1 2 1 7 1 1 3 2 2 8 3 11 7 15 7 12 8 0 PLAYER 1 TEAM 3 WORKEXPERIENCE Create and test all your queries inside Recess Save each queryin your access file using the query sumber as the queryname (so you will have queries ramadol rough I saved in Ace 00 NOT BE to generate your statement Pastel your sot into the word document under the roton Database information database keep track of the story of the teams to eached on team number manager number team.coachubernder ruber wenique attributes to the age database keep track of the hunory of all the coaching tones at each coach has performed Managers were separate from coaches Manager noted. There is one to one relationship between team and manager but do not have to have mange werlalumber (for boting only fortem in the affiliation table, the Arts tribute indicristhenber of years that appeared on a team, the batting average for the years that a perdonator The WORKPERIENCE table cep track of each coat's worthory with am. The WEXCurrent field indicates which is the coach current caching. A coach can only hold the sme potion with a mence 1 Listall team names of teams that have manager 2 strames of all players and her cute metamore Only include players who are Currently 29 List the names and number of players who were for more than two years but less than five years with the current team 4 List the names and cities of all the teams with more than I current players List the results in evindihabetical order byta ut all of the coachnames we have to work ach coach per team between 2 and 10 years List the total number of years of experience of Coach Taylor with the Baper 7. list the number of different types of experience antrenamedCount that Coach Adams had not including his currento tation day the coach's name List all the Coachnames that have had more than 10 yearstieperience across the 9 List the number of different manufacturen who make bats for the leagues teamDeplay the result in an attributin named Number in the same position with a team once. 1. 2 3. 4. 5. 6. 7. 8. List all team names of teams that currently have a manager. List names of all players and their associated current team name. Only include players who are currently active and have a batting average above 290. List the names and numbers of all players who have played for more than two years but less than five years with their current team. List the names and cities of all the teams with more than 3 current players. List the results in reverse alphabetical order by team name. List all of the coach names who have had average work experience as a "pitching coach' per team between 2 and 10 years. List the total number of years of experience of Coach Taylor with the Expos. List the number of different types of experience (as an attribute named: ExpCount) that Coach Adams has had not including his current position. In addition, display the coach's name. List all the Coach names that have had more than 10 years cumulative experience across all their jobs. List the number of different manufacturers who make bats for the league's teams. Display the result in an attribute named NumBatMan. 10. Find the names of the players who played for any team for at least six years (note that this could be over multiple stints with the same team). 11. List the average player age for each team where the team's average age is greater than 26. Include the team name in your response. 12. List the names of the league's oldest players whose names begin with the letter 's. 13. List the names of ALL teams, and their managers name (if they have one) 14. Use a level 1 nested query to display PlayerNum, Player Name and Age of players who have an average batting average of over 250 across all teams they have played for. 15. List player names and their overall batting average across all teams they have played for. Restrict your result to players who are older than 18 and have played at least one game with the Expos. List the CoachNames of coaches who have coached for every Team. (this is a division problem) 9. 16. H AFFILIATION - Access Table Tools File Home Tables * AFFILIATION A BAT E COACH MANAGER PLAYER 2 TEAM Create External Data Database Tools PLAYERNUM. TEAMNUM. AffilYrs 358 15 358 20 523 12 1131 20 1779 15 1779 24 2007 24 4280 15 4280 20 4319 15 5410 12 6564 20 6564 24 8093 12 8093 15 8093 20 8093 23 8093 24 8366 20 0 Help Fields Table Tell me what you want to do AffilBatAvg - AffilDateStart AffilateEnd AffilCurrentT. Click to Add- 5 0.300 1998-01-01 2002-12-31 3 0.320 2003-01-01 12 0.257 1994-01-01 1 0.283 2005-01-01 6 0.246 1997-01-01 2002-12-31 3 0.240 2003-01-01 3 0.290 2003-01-01 1 0.195 2002-01-01 2002-12-31 3 0.227 2003-01-01 4 0.298 2002-01-01 5 0.307 2001-01-01 12 0.310 1991-01-01 2002-12-31 3 0.280 2003-01-01 5 0.250 1986-01-01 1990-12-31 3 0.300 1991-01-01 1993-12-31 2 0.240 1994-01-01 1995-12-31 2 0.290 1996-01-01 1997-12-31 8 0.265 1998-01-01 7 0.283 1999-01-01 WORKEXPERIENCE OKKIIKIKKKKKKKKD Record: 5 of 19 in Search H Table Tools BAT - Access Tell me what you want to do Help File Home Tables AFFILIATION Create External Data Database Tools Fields Table TEAMNUM - SERIALNUM - BatManuf - Click to Add. B01 Acme 12 B09 General 12 B13 Acme 15 B01 Acme 23 B03 United 23 B04 Modern 24 B18 General 24821 United COACH MANAGER BE PLAYER TEAM 0 WORKEXPERIENCE Table Tools Table File Home Tables AFFILIATION Fields Tell me what you want to do CoachNum . Click to Add COACH - Access Create External Data Database Tools Help COACHNAME - CoachPhone . CoachAddress Adams 555-1524 + Baker 555-3690 + Dooley 555-7321 + Jackson 555-2444 Johnson 222-3423 + Simpson 234-2342 555-4820 Victor 555-9327 Wilson 222-2222 1 2 3 BAT COACH MANAGER PLAYER TEAM WORKEXPERIENCE Taylor 4 5 6 7 8 9 0 BE 111 MANAGER - Access Table Tools File Home Create Table Tables AFFILIATION + + External Data Database Tools Help Fields MgrNum MgrName - MgrWinPctg - Click to Add 1 shoobotter 0.4 2 carmaletto 0.5 3 shinster 0.2 (New) 0 BAT COACH MANAGER PLAYER TEAM WORKEXPERIENCE - PLAYER - Access Table Tools File Home Create Table + Tables AFFILIATION BAT + + + COACH + External Data Database Tools Help Fields PlayerNum , PLAYERNAM - PlayerAge - Click to Add 358 Bevens 21 523 Bullock 32 1131 Bean 28 1779 Jones 25 2007 Dobbs 4280 Cohen 25 4319 Ross 24 5410 Smith 27 6564 Linton 24 8093 Smyth 21 8366 Gomez 33 0 27 MANAGER PLAYER + TEAM + WORKEXPERIENCE + + Table Tools TEAM - Access File Home Create Help Tables AFFILIATION External Data Database Tools TEAMNUM - TEAMNAME - TeamCity 12 Dodgers Los Angeles 15 Giants San Francisco 20 Yankees New York 23 Expos Montreal 24 Tigers Detroit Fields Table Tell me what you Mgr Num - Click to Add- 1 2 BAT 3 B COACH + E MANAGER 0 BE PLAYER E TEAM BE WORKEXPERIENCE Table Tools Home Create File Tables AFFILIATION BAT COACH WORKEXPERIENCE - Access External Data Database Tools TEAMNUM - WEXP_Type 12 Batting Coach 15 3rd base coach 20 Batting Coach 23 Pitching Coach 24 Batting Coach 12 Batting Coach 12 Pitching Coach 24 Batting Coach 23 3rd base coach 23 Batting Coach 24 Pitching Coach 0 1 MANAGER BE Help Fields Table Tell me what you want to do WEXP_Yrs WEXP_Curre - CoachNum - Click to Add 10 5 1 2 1 7 1 1 3 2 2 8 3 11 7 15 7 12 8 0 PLAYER 1 TEAM 3 WORKEXPERIENCE