# 1. Find out how many games that each baseball team won in 2015.
# Your result should have 30 rows
SELECT t.yearID, t.teamID, t.name, t.W FROM teams t WHERE t.yearID = '2015';
# 2. Find the years in which the Chicago Cubs won at least 95 games.
# Your result should have 7 rows.
SELECT t.yearID, t.teamID, t.name FROM teams t WHERE t.teamID = 'CHN' AND t.W >= 95;
# 3. Find the statistics for how many home runs that Ernie Banks hit
# with a year-by-year breakdown, while he was playing
# for the Chicago Cubs.
# FYI, Ernie Banks was awarded the Presidential Medal of Freedom
# in 2013, for his contribution to sports.
# Your result should have 19 rows
SELECT m.playerID, m.nameFirst, m.nameLast, b.HR, b.yearID FROM batting b INNER JOIN master m ON b.playerID = m.playerID JOIN teams t ON b.yearID = t.yearID AND b.teamID = t.teamID WHERE m.nameFirst = 'Ernie' AND m.nameLast = 'Banks' AND t.teamID = 'CHN';
# 4. Identify each player who hit 40 or more home runs during a year,
# while they were playing with the Chicago Cubs.
# Your result should have 18 rows, and some
# players achieved such an accomplishment several times (and will
# therefore appear in the list multiple times).
SELECT m.playerID, m.nameFirst, m.nameLast, b.HR FROM batting b INNER JOIN master m ON b.playerID = m.playerID JOIN teams t ON b.yearID = t.yearID AND b.teamID = t.teamID WHERE t.teamID = 'CHN' AND b.HR >= 40;
# 5. Have there been any Chicago Cubs players to
# get 100 or more runs in a single season?
# Find all such Cubs players who had such an achievement since 2000.
# Your result should have 7 rows
SELECT m.nameFirst, m.nameLast, b.playerID, b.teamID, t.name, b.R, b.yearID FROM batting b JOIN master m ON m.playerID = b.playerID JOIN teams t ON b.yearID = t.yearID AND b.teamID = t.teamID WHERE b.R > 10 AND t.teamID = 'CHN' AND t.yearID >= 2000;
# 6. Find, for each year since 1960, how many Chicago Cubs players
# there were in each year. Your result should have 56 rows.
SELECT b.yearID, b.teamID, t.name, COUNT(*) 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.teamID = 'CHN' AND t.yearID >= 1960 GROUP BY b.yearID;
# 7. Find the player who had the largest number of doubles in one season.
SELECT m.playerID, b.yearID, m.nameFirst, m.nameLast, b.2B FROM batting b INNER JOIN master m ON b.playerID = m.playerID GROUP BY m.playerID, b.yearID ORDER BY b.2B;
# Hint for questions 8, 9, 10:
# You might need/want to use "HAVING" near the end of your query on each question.
# 8. Find the teams and years in which a team
# won at least 105 games that year.
# Your result should have 18 rows.
# Notice that some players achieved this feat during more than 1 season.
SELECT t.yearID, t.teamID, t.name, SUM(t.W) FROM teams t GROUP BY t.yearID, t.teamID HAVING SUM(t.W) >= 105;
# 9. Find the breakdown of players who had 500 or more home runs during
# their lifetime. Your result should have 26 rows.
SELECT m.playerID, m.nameFirst, m.nameLast, SUM(b.HR) FROM batting b INNER JOIN master m ON b.playerID = m.playerID GROUP BY m.playerID HAVING SUM(b.HR) > 500;
# 10. Just like we adjusted the data type and the indexing for
# some of the batting, master, and teams tables,
# please go ahead and adjust the data types and indexing in
# the pitching table as follows:
alter table pitching modify playerID varchar(20);
alter table pitching modify teamID varchar(20);
# We should also build an index for each of these fields.
alter table pitching add index pitching_playerID(playerID);
alter table pitching add index pitching_teamID(teamID);
# Now find the pitchers who have made
# at least 3000 strikeouts during their career.
# Your result should have 16 rows.
SELECT m.playerID, m.nameFirst, m.nameLast, SUM(p.SO) FROM pitching p INNER JOIN master m ON p.playerID = m.playerID GROUP BY m.playerID HAVING SUM(p.SO) >= 3000;