Create four MySQL queries that will produce results/output to answer these quest
ID: 3544786 • Letter: C
Question
Create four MySQL queries that will produce results/output to answer these questions. The 3 Databases used are shown in the attachment as well as in code. I believe only the 2nd and 3rd databases (teams and games) are relevant to these questions. I will give 1,500 points if you can answer atleast two of them correctly.
1) Print all teamIDs where the team played against the Phillies but not against the Braves.
2) Print all tuples (playerID1, playerID2, team) where playerID1 and playerID2 are (or have been) 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').
3) 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.
4) List all cities that have a team in all leagues. 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.
Databases:
Databases Code:
CREATE TABLE `players` (
`playerID` tinyint(4) default NULL,
`playerName` varchar(50) default NULL,
`team` varchar(50) default NULL,
`position` varchar(50) default NULL,
`birthYear` int default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `players` (`playerName`,`birthYear`,`position`,`playerID`,`team`) VALUES ('Javy Lopez','1970','Catcher','1','Braves');
INSERT INTO `players` (`playerName`,`birthYear`,`position`,`playerID`,`team`) VALUES ('Cliff Lee','1978','Pitcher','2','Phillies');
INSERT INTO `players` (`playerName`,`birthYear`,`position`,`playerID`,`team`) VALUES ('Derek Jeter','1974','Infielder','3','Yankees');
INSERT INTO `players` (`playerName`,`birthYear`,`position`,`playerID`,`team`) VALUES ('Skip Schumaker','1980','Infielder','4','Cardinals');
INSERT INTO `players` (`playerName`,`birthYear`,`position`,`playerID`,`team`) VALUES ('Dominic Brown','1987','Outfielder','5','Phillies');
CREATE TABLE `teams` (
`teamID` tinyint(4) default NULL,
`teamName` varchar(50) default NULL,
`home` varchar(50) default NULL,
`leagueName` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('1','Phillies','Philadelphia','National');
INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('2','Braves','Atlanta','National');
INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('3','Yankees','New York','American');
INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('4','Twins','Minnesota','American');
INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('5','Rangers','Texas','American');
INSERT INTO `teams`(`teamID`,`teamName`,`home`,`leagueName`) VALUES ('6','Cubs','Chicago','National');
CREATE TABLE `games` (
`gameID` tinyint(4) default NULL,
`homeTeamID` tinyint(4) default NULL,
`guestTeamID` tinyint(4) default NULL,
`date` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `games`(`gameID`,`homeTeamID`,`guestTeamID`,`date`) VALUES ('1','3','6','04/21/2010');
INSERT INTO `games`(`gameID`,`homeTeamID`,`guestTeamID`,`date`) VALUES ('2','1','4','04/21/2010');
INSERT INTO `games`(`gameID`,`homeTeamID`,`guestTeamID`,`date`) VALUES ('3','2','5','04/30/2010');
INSERT INTO `games`(`gameID`,`homeTeamID`,`guestTeamID`,`date`) VALUES ('4','6','3','05/02/2010');
INSERT INTO `games`(`gameID`,`homeTeamID`,`guestTeamID`,`date`) VALUES ('5','4','5','05/02/2010');
INSERT INTO `games`(`gameID`,`homeTeamID`,`guestTeamID`,`date`) VALUES ('6','1','5','05/06/2010');
Explanation / Answer
Here are the answer to all 4 questions: Please rate. Thanks
-- 1
SELECT DISTINCT hometeamid FROM games WHERE
guestteamid = (SELECT teamid FROM teams WHERE teamname = 'Phillies')
AND guestteamid != (SELECT teamid FROM teams WHERE teamname = 'Braves')
UNION DISTINCT
SELECT DISTINCT guestteamid FROM games WHERE
hometeamid = (SELECT teamid FROM teams WHERE teamname = 'Phillies')
AND hometeamid != (SELECT teamid FROM teams WHERE teamname = 'Braves');
-- 2
SELECT a.playerid "playerID1", b.playerid "playerID2", a.team
FROM players a INNER JOIN players b
ON a.team = b.team
AND a.playerid != b.playerid
AND a.playerid < b.playerid;
-- 3
SELECT a.hometeamid "teamID1", b.leaguename "league1", a.guestteamid "teamID2", c.leaguename "league2", a.date
FROM games a INNER JOIN teams b
ON a.hometeamid = b.teamid
INNER JOIN teams c
ON a.guestteamid = c.teamid
WHERE
b.leaguename != c.leaguename;
-- 4
SELECT DISTINCT a.home
FROM teams a INNER JOIN teams b
ON a.home = b.home
AND a.teamname != b.teamname
AND a.leaguename != b.leaguename;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.