Please show the commands in MySQL using subquery for the questions. Thanks. TOUR
ID: 3706811 • Letter: P
Question
Please show the commands in MySQL using subquery for the questions. Thanks.
TOURNAMENTS PK TourneylD TourneyDate TourneyLocation TOURNEY MATCHES MatchID Lanes MATCH_GAMES Toury FK CPK GameNumber CPK MatchID OddLaneTeamlD FK 0e EvenLaneTeamID FK TEAMS WinningTeamID FK TeamID PK TeamName CaptainlD BOWLER SCORES MatchiD BOWLERS PK ameNumber CPK BowlerLastName- BowlerFirstName. BowlerMiddlelnit BowlerStreetAddress BowlerCity BowlerState BowlerZipCode BowlerPhoneNumber lerlDCPK RawScore HandicapScore WonGame O TeamID FK 1. Display the bowlers who have never bowled a raw score greater than 100 2. List the tournaments that have not yet been played 3. List the team(s) that play only in odd lanes 4. Show all bowler information who does not belong to any team 5. List the bowlers, the match number, the game number, the handicap score, the tournament date, and the tournament location for bowlers who won a game (i.e. WonGame = 1) with a handicap score of 170 or less at Thunderbird Lanes, Totem Lanes, and Bolero Lanes tournament locationExplanation / Answer
1)SELECT b.*FROM Bowlers bWHERE b.BowlerID IN (SELECT bs.BowlerIDFROM Bowler_Scores bsWHERE bs.rawscore <= 100);
2)SELECT t.*FROM Tournaments AS tWHERE t.tourneyID NOT IN (SELECT tm.TourneyIDFROM Tourney_Matches AS tm);
3)SELECT T.teamnameFROM Teams AS tWHERE t.teamid IN (SELECT tm.OddLaneTeamIDFROM tourney_matches AS tm)AND t.teamid NOT IN (SELECT tm.evenlaneteamIDFROM tourney_matches AS tm);
4)SELECT b.bowlerfirstname, b.bowlerlastnameFROM bowlers bWHERE b.teamid NOT IN (SELECT t.teamidFROM teams t);