Write the following queries in SQL (a) Print all teamIDs where the team played a
ID: 3877079 • Letter: W
Question
Write the following queries in SQL
(a) Print all teamIDs where the team played against the Yankees but not against the Braves.
(b) Print all tuples (playerID1, playerID2, team1,team2) where playerID1 and playerID2 are not on the same team. Avoid listing self-references or duplicates, e.g. do not allow (1,1,”Braves”) or both (2,5,”Phillies”) and (5,2,”Phillies”).
(c) Print all tuples (teamID1, league1, teamID2, league2, date) where teamID1 and teamID2 played against each other in a World Series game. Although there is no direct information about the World Series games in the relations, we can infer that when two teams from different leagues play each other, it is a World Series game. So, in this relation, league1 and league2 should be different leagues.
(d) List all cities that have a team in only one league. For example, there are currently two leagues (National and American). Although not shown in this instance, New York is home to the Mets in the National league as well as the Yankees in the American league (Chicago also has one in each league, for those of you who are baseball fans). Remember that your query must work over all instances of this schema, even if there are more than two leagues in the instance.
Players playerName team Javy Lope:z Cliff Lee Derek Jeter Skip Schumaker Cardinals Dominic Brown Phillies position Catcher Pitcher Infielder Infielder Outfielder birth Year 1970 1978 1974 1980 1987 playerlD Braves Phillies Yankees 2 4 5 Teams teamID teamName home leagueName Phillies Philadelphia NationalExplanation / Answer
answer 4 : select a.home as cities from teams a group by a.home having count(home)<2
answer 3 : select hometeamid as teamid1,guestteamid as teamid2,'national' as league1,'american' as league2,date from games where hometeamid in (select teamid from teams where leaguename='national') and guestteamid in (select teamid from teams where leaguename='american')
union
select hometeamid as teamid1,guestteamid as teamid2,'national' as league1,'american' as league2,date from games where hometeamid in (select teamid from teams where leaguename='american') and guestteamid in (select teamid from teams where leaguename='national')
answer 2 : select a.playerid as playerid1,b.playerid as playerid2,a.team as teamid1,b.team as teamid2 from players a, players b where a.team<>b.team
answer1: Select distinct teamid from
(Select hometeamid as teamid from games where hometeamid in (Select distinct hometeamid from games where guestteamid='3') and guestteamid<>'2'
union
Select guestteamid as teamid from games where guestteamid in (Select distinct guestteamid from games where hometeamid='3') and hometeamid<>'2')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.