#1a.
# all the years and players who hit more than 20 home runs in one season are generated by:
SELECT b.yearID, m.nameFirst, m.nameLast, b.HR FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID INNER JOIN Pitching p ON (p.yearID = b.yearID AND p.playerID = b.playerID) WHERE b.HR > 20;
# There are 17 such players
SELECT m.nameFirst, m.nameLast FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID INNER JOIN Pitching p ON (p.yearID = b.yearID AND p.playerID = b.playerID) WHERE b.HR > 20 GROUP BY m.playerID;
#1b.
# There are 1599 such pitchers
SELECT m.nameFirst, m.nameLast FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID INNER JOIN Pitching p ON (p.yearID = b.yearID AND p.playerID = b.playerID) WHERE b.HR > 0 GROUP BY m.playerID;
#2a.
# The Cincinnati Reds have committed the most errors altogether: 22837 of them.
SELECT t.name, SUM(t.E) FROM Teams t GROUP BY t.name ORDER BY SUM(t.E);
#2b.
# The New York Yankees have hit the most home runs altogether: 14814 of them.
SELECT t.name, SUM(t.HR) FROM Teams t GROUP BY t.name ORDER BY SUM(t.HR);
#2c.
# When restricting attention to errors since 2010, the Chicago Cubs have the most, with 593 errors altogether.
SELECT t.name, SUM(t.E) FROM Teams t WHERE t.yearID >= 2010 GROUP BY t.name ORDER BY SUM(t.E);
# When restricting attention to HR since 2010, the Toronto Blue Jays have the most, with 1003 HR altogether.
SELECT t.name, SUM(t.HR) FROM Teams t WHERE t.yearID >= 2010 GROUP BY t.name ORDER BY SUM(t.HR);
#3a.
# Hughie Jennings has been hit by the most pitches: 287 of them altogether.
SELECT m.nameFirst, m.nameLast, SUM(b.HBP) FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID GROUP BY m.playerID ORDER BY SUM(b.HBP);
#3b.
# Walter Johnson has hit the most batters overall: 203 of them altogether.
SELECT m.nameFirst, m.nameLast, SUM(p.HBP) FROM Pitching p INNER JOIN Master m ON p.playerID = m.playerID GROUP BY m.playerID ORDER BY SUM(p.HBP);
#3c.
# The wildest 10 pitchers are the last 10 in the query here; notice that there is a two-way tie for the 10th place.
SELECT m.nameFirst, m.nameLast, SUM(p.WP) FROM Pitching p INNER JOIN Master m ON p.playerID = m.playerID GROUP BY m.playerID ORDER BY SUM(p.WP);
#4a.
# The Seattle Mariners won 116 games in 2001, and the Chicago Cubs won 116 games in 1906.
SELECT t.name, t.W, t.yearID FROM Teams t ORDER BY t.W;
#4b.
# Bret Boone was the team leader in 2001 for the Seattle Mariners
SELECT m.nameFirst, m.nameLast, b.HR FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID INNER JOIN Teams t ON (t.yearID = b.yearID AND t.teamID = b.teamID) WHERE t.name = 'Seattle Mariners' AND t.yearID = '2001' ORDER BY b.HR;
# Frank Schulte was the team leader in 1906 for the Chicago Cubs
SELECT m.nameFirst, m.nameLast, b.HR FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID INNER JOIN Teams t ON (t.yearID = b.yearID AND t.teamID = b.teamID) WHERE t.name = 'Chicago Cubs' AND t.yearID = '1906' ORDER BY b.HR;
#5.
# The ten best universities, in terms of their alumni hitting home runs, are the top ten in this list;
# note that there are several schools tied for 9th to 17th place.
SELECT m.nameFirst, m.nameLast, SUM(b.HR), s.name_full FROM Batting b INNER JOIN Master m ON b.playerID = m.playerID INNER JOIN CollegePlaying c ON (c.yearID = b.yearID AND c.playerid = b.playerID) INNER JOIN Schools s ON s.schoolID = c.schoolID GROUP BY s.name_full ORDER BY SUM(b.HR);
#6a.
# Tony LaRussa's teams have had the most home runs overall.
SELECT mng.playerID, m.nameFirst, m.nameLast, SUM(b.HR) FROM Batting b INNER JOIN Managers mng ON (b.yearID = mng.yearID AND b.teamID = mng.teamID) INNER JOIN Master m ON mng.playerID = m.playerID GROUP BY mng.playerID ORDER BY SUM(b.HR);
#6b.
# John McGraw's teams have had the most stolen bases overall.
SELECT mng.playerID, m.nameFirst, m.nameLast, SUM(b.SB) FROM Batting b INNER JOIN Managers mng ON (b.yearID = mng.yearID AND b.teamID = mng.teamID) INNER JOIN Master m ON mng.playerID = m.playerID GROUP BY mng.playerID ORDER BY SUM(b.SB);
#7a.
# There are 1486 players who have played at least one game in all three outfield positions.
SELECT f.playerID, COUNT(DISTINCT(f.Pos)) FROM Fielding f WHERE (f.Pos = 'LF' OR f.Pos = 'CF' OR f.Pos = 'RF') GROUP BY f.playerID HAVING (COUNT(DISTINCT(f.Pos)) = 3) ORDER BY f.playerID;
#7b.
# There are 7 players who have played in exactly 11 positions at least one time.
#8.
# There are 4814 left handed batters
SELECT m.playerID, m.bats FROM Master m WHERE m.bats = 'L' GROUP BY m.playerID;
# and 11435 right handed batters
SELECT m.playerID, m.bats FROM Master m WHERE m.bats = 'R' GROUP BY m.playerID;
# and 1150 switchhitters (i.e., bat with both hands)
SELECT m.playerID, m.bats FROM Master m WHERE m.bats = 'B' GROUP BY m.playerID;
# so the percent of lefties is 4814/(4814 + 11435 + 1150) = 0.28
# and the percent of switchhitters is 1150/(4814 + 11435 + 1150) = 0.07
#9a.
# The Baltimore Monumentals had the worst average number of errors per year.
SELECT t.name, SUM(t.E) / COUNT(DISTINCT(t.yearID)) FROM Teams t GROUP BY t.name ORDER BY SUM(t.E) / COUNT(DISTINCT(t.yearID));
#9b.
# The Colorado Rockies had the best average number of home runs per year.
SELECT t.name, SUM(t.HR) / COUNT(DISTINCT(t.yearID)) FROM Teams t GROUP BY t.name ORDER BY SUM(t.HR) / COUNT(DISTINCT(t.yearID));
#10.
# The bases stolen per year are (here given in increasing order
# according to the number stolen per year)
SELECT b.yearID, SUM(b.SB) FROM Batting b GROUP BY b.yearID ORDER BY SUM(b.SB);
#11.
# The number of home runs that Derek Jeter hit each year is (given in
# increasing order according to the number of home runs per year)
SELECT b.yearID, b.HR FROM Batting b INNER JOIN Master m ON m.playerID = b.playerID WHERE m.nameLast = 'Jeter' AND m.nameFirst = 'Derek' GROUP BY b.yearID ORDER BY b.HR;
#12.
# The Yankees home run data is given by:
SELECT b.yearID, b.HR FROM Batting b INNER JOIN Teams t ON (b.teamID = t.teamID AND b.yearID = t.yearID) WHERE t.name = 'New York Yankees' ORDER BY b.HR;
#13.
# The data for home runs and hits is given by:
SELECT sum(b.HR), sum(b.H) FROM Batting b GROUP BY b.playerID ORDER BY sum(b.HR);
#14.
# The number of players born in each state is:
SELECT m.birthState, COUNT(DISTINCT(m.playerID)) FROM Master m GROUP BY m.birthState ORDER BY COUNT(DISTINCT(m.playerID));
# or if we want to limit it to US States and DC, we can do that, for instance, by
SELECT m.birthState, COUNT(DISTINCT(m.playerID)) FROM Master m WHERE m.birthState IN ('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NV','NH','NJ','NM','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') GROUP BY m.birthState ORDER BY COUNT(DISTINCT(m.playerID));