In this Query, you have to list pair of players with their playerID and playerName who play for the exact same teams.If a player plays for 3 teams, the other has
to play for exact same 3 teams. No less, no more. If two players currently do not play for any team, they should also be included. The query should return
(playerID1, playername1, playerID2, playerName2) with no repetition such as if player 1 info comes before player 2, there should not be another tuple with player 2
info coming before player 1.
For example if player A plays for CSK and MI, and player b plays for CSK, MI, and DD you should not get them. They both have to play for CSK, and MI and no one else.
Prerequisites:
Tables:
player(playerID: integer, playerName: string)
team(teamID: integer, teamName: string, sport: string)
plays(playerID: integer, teamID: integer)
Example data:
PLAYER
playerID playerName
1 Dravid
2 Sachin
3 Dhoni
4 Yuvraj
5 Ponting
TEAM
teamID teamName sport
1 CSK CRICKET
2 DD CRICKET
3 MI CRICKET
4 RR CRICKET
5 KKR CRICKET
PLAYS
playerID TeamID
1 1
1 2
1 3
2 1
2 3
3 1
3 3
So you should get this as answer-
2, SACHIN, 3, DHONI
4, YUVRAJ, 5, PONTING
2, SACHIN, 3 DHONI is answer because both play for exclusively CSK and MI 4, Yuvraj, 5, Ponting is an answer because both players play for no teams which should be
in output.
I have written query upto
select distinct player.playerid,player.playername,plays.teamid,team.teamname,team.sport from player left outer
join plays on player.playerid=plays.playerid
left outer join team on plays.teamid = team.teamid where plays.teamid is null