Super Baseball League Consider the following relational database for the Super B

Question

Super Baseball League

Consider the following relational database for the Super Baseball League. It keeps track of teams in the league, coaches and players on the teams, work experience of the coaches, bats belonging to each team, and which players have played on which teams. Note the following facts about this environment:

•The database keeps track of the history of all of the teams that each player has played on and all of the players who have played on each team.

•The database only keeps track of the current team that a coach works for.

•Team Number, Team Name, and Player Number are each unique attributes across the league.

•Coach Name is only unique within a team (and we assume that a team cannot have two coaches of the same name.)

•Serial Number (for bats) is only unique within a team.

•In the AFFILIATION relation, the Years attribute indicates that number of years that a player played on a team; the Batting Average is for the years that a player played on a team.

Here are the tables:

Using SQL (or using informal relational command language), write commands to i. Retrieve the recordfor coach Adams on team number 12 ii. List the player number and age of every player. iii. List the work experience of every coach on team number 25 iv. List the serial numbers and manufacturers of all of the Vultures' (the name of a team) bats. Find the number ofyears of college coaching experience that coach Taylor of the Vultures has v.

Explanation / Answer

i. SELECT COACHRelation.TeamNumber,COACHRelation.CoachName,COACHRelation.CoachAddress FROM COACHRelation WHERE COACHRelation.TeamNumber=12 and COACHRelation.CoachName='Adams';

ii. SELECT PLAYERRelation.PlayerNumber, PLAYERRelation.Age FROM PLAYERRelation; (Selects all records because no where filter is present)

iii. SELECT WORKEXPERIENCERelation.TeamNumber, WORKEXPERIENCERelation.CoachName, WORKEXPERIENCERelation.ExperienceType, WORKEXPERIENCERelation.YearsOfExperience FROM WORKEXPERIENCERelation WHERE WORKEXPERIENCERelation.TeamNumber=25;

iv. SELECT BATSRelation.SerialNumber, BATSRelation.Manufacturer FROM BATSRelation INNER JOIN TEAMRelation on BATSRelation.TeamNumber=TEAMRelation.TeamNumber WHERE TEAMRelation.TeamName = 'Vultures';

v. SELECT WORKEXPERIENCERelation.YearsOfExperience FROM WORKEXPERIENCERelation INNER JOIN TEAMRelation on WORKEXPERIENCERelation.TeamNumber=TEAMRelation.TeamNumber WHERE TEAMRelation.TeamName = 'Vultures' and WORKEXPERIENCERelation.CoachName = 'Taylor';

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.