MUST BE DONE IN SQL Players(playerlD, playerName, team, position, birthYear) pla
ID: 3877096 • Letter: M
Question
MUST BE DONE IN SQL
Players(playerlD, playerName, team, position, birthYear) playerID is a player identifier used in MLB, and all players throughout the history of baseball have a unique ID playerName is player's name team is the name of the MLB team the player is currently playing on (or the last team the player played for if they are not currently playing) position is the position of the player birthYear is the year that player was born · · Teams(teamlD, teamName, home, leagueName) · team!D is a unique ID internal to MLB. o teamName is the name of the team » home is the home city of the team leagueName is the league the team is in, i.e. either "National" or "American", which stands for "National League" and "American League", respectively Games(gamelD, homeTeamID, guestTeamlD, date) · o · game!D is a unique ID used internally in MLB homeTeamID is the ID of the hometeam guestTeamID is the ID of the visiting team date is the date of the game.Explanation / Answer
a) Below query will list the teamID of the all the team who played with Yankees but not with braves. Two subqueries in where clause are used to include Yankees and exclude braves.
SELECT homeTeamID FROM Games
WHERE guestTeamID IN (SELECT teamID FROM Teams WHERE teamName = 'Yankees')
AND guestTeamID NOT IN (SELECT teamID FROM Teams WHERE teamName <> 'Braves');
b) Below query will list the playerIDs and their corresponding teams who played against each other. Self join on Team table is used to get hometeam and guest team name.
SELECT te.playerID As 'playerID1', t.playerID AS 'playerID2', homeTeamID, guestTeamID
FROM Games AS a
INNER JOIN Teams AS te
ON teamID = homeTeamID
INNER JOIN Teams AS t
ON teamID = guetTeamID
INNER JOIN Players
ON team = TeamID;
c) This query will list the team name with their corresponding league name who played against each other.
SELECT homeTeamID, t.leagueName, guestTeamID, te.leagueName
FROM Games
INNER JOIN Teams AS t
homeTeamID = t.teamID
INNER JOIN Teams AS te
ON te.teamID = guestTeamID
WHERE t.leagueName <> te.leagueName;
d) This query will select all the cities which have a team in only one league. Group by clause along with Having is used to get the cities who have team in only one league.
SELECT home, COUNT(DISTINCT leagueName)
FROM Teams
GROUP BY home
HAVING COUNT(DISTINCT leagueName) = 1;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.