# STAT 29000
# Project 9 solutions
# by Mark Daniel Ward
# 1a. The 17 players are:
SELECT m.nameFirst, m.nameLast FROM Master m INNER JOIN SchoolsPlayers s on m.playerID=s.playerID WHERE s.schoolID='purdue';
# 1b. Now we add the years when they each played for Purdue:
SELECT m.nameFirst, m.nameLast, s.yearMin, s.yearMax FROM Master m INNER JOIN SchoolsPlayers s on m.playerID=s.playerID WHERE s.schoolID='purdue';
# 1c. Finally, we add columns for the Major League year and for the home runs in each year. We also add the team name, since some players switched teams in a year.
SELECT m.nameFirst, m.nameLast, s.yearMin, s.yearMax, b.yearID, b.HR, b.teamID FROM Master m INNER JOIN SchoolsPlayers s on m.playerID=s.playerID INNER JOIN Batting b on b.playerID=m.playerID WHERE s.schoolID='purdue';
# 2a. The 167 players are:
SELECT m.nameFirst, m.nameLast, s.schoolName, s.schoolCity, s.schoolState FROM Master m INNER JOIN SchoolsPlayers p on m.playerID = p.playerID INNER JOIN Schools s on p.schoolID=s.schoolID WHERE s.schoolState = 'IN';
# 2b. Now we add the years when they each played for their universities:
SELECT m.nameFirst, m.nameLast, s.schoolName, s.schoolCity, s.schoolState, p.yearMin, p.yearMax FROM Master m INNER JOIN SchoolsPlayers p on m.playerID = p.playerID INNER JOIN Schools s on p.schoolID=s.schoolID WHERE s.schoolState = 'IN';
# 2c. Finally, we add columns for the Major League year and for the home runs in each year. We also add the team name, since some players switched teams in a year.
SELECT m.nameFirst, m.nameLast, s.schoolName, s.schoolCity, s.schoolState, p.yearMin, p.yearMax, b.yearID, b.HR, b.teamID FROM Master m INNER JOIN SchoolsPlayers p on m.playerID = p.playerID INNER JOIN Schools s on p.schoolID=s.schoolID INNER JOIN Batting b on b.playerID=m.playerID WHERE s.schoolState = 'IN';
# 3a. The 24 teams, including their years and number of wins, are:
SELECT t.yearID, t.name, t.W FROM Teams t WHERE t.W >= 105;
# 3b. Now we add the manager names.
SELECT t.yearID, t.name, t.W, m.nameFirst, m.nameLast FROM Teams t INNER JOIN Managers mng ON mng.yearID=t.yearID and mng.teamID=t.teamID INNER JOIN Master m ON m.playerID=mng.playerID WHERE t.W >= 105;
# 4a. The Chicago Cubs were league champions 10 times:
SELECT t.yearID, t.name, t.teamID FROM Teams t WHERE LgWin = 'Y' AND name='Chicago Cubs';
# 4b. Now we add the manager names.
SELECT t.yearID, t.name, t.teamID, m.nameFirst, m.nameLast FROM Teams t INNER JOIN Managers mng ON mng.yearID=t.yearID INNER JOIN Master m ON m.playerID=mng.playerID AND mng.teamID=t.teamID WHERE LgWin = 'Y' AND name='Chicago Cubs';
# 5a. There have been 20 times when a player stole 100 or more bases in a season.
SELECT m.nameFirst, m.nameLast, b.SB, b.yearID FROM Master m INNER JOIN Batting b ON m.playerID = b.playerID WHERE b.SB >= 100;
# 5b. Now we add the team names.
SELECT m.nameFirst, m.nameLast, b.SB, b.yearID, t.name FROM Master m INNER JOIN Batting b ON m.playerID = b.playerID INNER JOIN Teams t ON b.yearID=t.yearID AND b.teamID=t.teamID WHERE b.SB >= 100;
# 6a. There are 13 players inducted into the Hall of Fame since 2008.
SELECT m.nameFirst, m.nameLast, h.yearID FROM Master m INNER JOIN HallOfFame h ON m.playerID=h.playerID WHERE h.yearID >= 2008 AND h.inducted = 'Y' AND h.category='player';
# 6b. Now we sort the results by year.
SELECT m.nameFirst, m.nameLast, h.yearID FROM Master m INNER JOIN HallOfFame h ON m.playerID=h.playerID WHERE h.yearID >= 2008 AND h.inducted = 'Y' AND h.category='player' ORDER BY yearID;
# 6c. Now we count the number of inducted players by year.
SELECT h.yearID, COUNT(*) FROM HallOfFame h WHERE h.yearID >= 2008 AND h.inducted = 'Y' AND h.category='player' GROUP BY h.yearID;
# 7a. Altogether, we see that a pitcher had 20 or more wins in a season 1183 times.
SELECT m.nameFirst, m.nameLast, p.W, p.yearID FROM Master m INNER JOIN Pitching p ON m.playerID = p.playerID WHERE p.W >= 20;
# 7b. Here are the counts of the number of players, per year, to get this achievement.
SELECT p.yearID, COUNT(*) FROM Pitching p WHERE p.W >= 20 GROUP BY p.yearID;
# 8a. There are 4 members of the 40-40 club
SELECT m.nameFirst, m.nameLast, b.yearID, b.SB, b.HR, t.name FROM Master m INNER JOIN Batting b ON m.playerID = b.playerID INNER JOIN Teams t ON b.yearID=t.yearID AND b.teamID=t.teamID WHERE b.SB >= 40 AND b.HR >= 40;
# 8b. There are 58 members of the 30-30 club, BUT ONLY if we consider players' achievements on the same team within a year.
# Therefore, the following code misses two players, each of whom were traded midseason, so their achievements were each split over two teams.
SELECT m.nameFirst, m.nameLast, b.yearID, b.SB, b.HR, t.name FROM Master m INNER JOIN Batting b ON m.playerID = b.playerID INNER JOIN Teams t ON b.yearID=t.yearID AND b.teamID=t.teamID WHERE b.SB >= 30 AND b.HR >= 30;
# There are different ways to get those extra two players. One way is:
SELECT m.nameFirst, m.nameLast, b.yearID, SUM(b.SB) mysb, SUM(b.HR) myhr FROM Master m INNER JOIN Batting b ON m.playerID = b.playerID GROUP BY m.nameFirst, m.nameLast, b.yearID HAVING SUM(b.SB) >= 30 AND SUM(b.HR) >= 30;
# 8c. Now we tabulate the number of players and years in the 30-30 club according to the team (using the first version of the code from 8b, i.e., just using the 58 players)
SELECT t.name, COUNT(*) FROM Batting b INNER JOIN Teams t ON b.yearID=t.yearID AND b.teamID=t.teamID WHERE b.SB >= 30 AND b.HR >= 30 GROUP BY t.name;
# 9a. Here are the players who earned 20 million dollars or more in a season.
SELECT m.nameFirst, m.nameLast, s.yearID, s.salary, t.teamID, t.name FROM Master m INNER JOIN Salaries s ON m.playerID = s.playerID INNER JOIN Teams t ON t.yearID = s.yearID AND t.teamID = s.teamID WHERE s.salary >= 20000000;
# 9b. Here we give a table of the years, sums of salaries, and team names, but only restricting attention to the players with 20 million or more.
SELECT s.yearID, SUM(s.salary), t.name FROM Salaries s INNER JOIN Teams t ON t.yearID = s.yearID AND t.teamID = s.teamID WHERE s.salary >= 20000000 GROUP BY t.name, s.yearID;
# same question, but now summing over all players (not just those with 20 million or more)
SELECT s.yearID, SUM(s.salary), t.name FROM Salaries s INNER JOIN Teams t ON t.yearID = s.yearID AND t.teamID = s.teamID GROUP BY t.name, s.yearID;
# 9c. Here we give a table of the years, sums of salaries, and team names, but only restricting attention to the players with 20 million or more.
SELECT s.yearID, SUM(s.salary), t.name FROM Salaries s INNER JOIN Teams t ON t.yearID = s.yearID AND t.teamID = s.teamID WHERE s.salary >= 20000000 GROUP BY t.name, s.yearID ORDER BY SUM(s.salary);
# same question, but now summing over all players (not just those with 20 million or more)
SELECT s.yearID, SUM(s.salary), t.name FROM Salaries s INNER JOIN Teams t ON t.yearID = s.yearID AND t.teamID = s.teamID GROUP BY t.name, s.yearID ORDER BY SUM(s.salary);
# 10a. There are 25 pitchers with 300 or more saves during their careers.
SELECT m.nameFirst, m.nameLast, SUM(p.SV) FROM Master m INNER JOIN Pitching p ON m.playerID = p.playerID GROUP BY m.playerID HAVING SUM(p.SV) >= 300;
# 10b. There are 69 pitchers with 2000 or more strikeouts during their careers.
SELECT m.nameFirst, m.nameLast, SUM(p.SO) FROM Master m INNER JOIN Pitching p ON m.playerID = p.playerID GROUP BY m.playerID HAVING SUM(p.SO) >= 2000;