Baseball Examples Using the Lahman Database

These examples depend on this database:

/anvil/projects/tdm/data/lahman/lahman.db

Manager for the Cincinnati Reds

The Cincinnati Reds (teamID = 'CIN') were very successful in the 1970s. They were so successful that they were often called by the nickname the "Big Red Machine"! Who was their manager in 1976?

SELECT p.nameFirst, p.nameLast FROM Managers as m JOIN People as p ON m.playerID = p.playerID WHERE m.teamID = 'CIN' and m.yearID = 1976;

Tony LaRussa’s Many Years as a Manager

Find the list of teamID and yearID values corresponding to the teams and years in which Tony LaRussa was a Manager

SELECT m.teamID, m.yearID FROM People as p JOIN Managers as m ON p.playerID = m.playerID WHERE p.nameFirst = 'Tony' AND p.nameLast = 'LaRussa';

Stealing Bases

Find all players who stole 100 or more bases during a season. For each such player, list the player’s first name, last name, the number of bases stolen, and the year.

SELECT p.nameFirst, p.nameLast, b.SB, b.yearID FROM People p INNER JOIN Batting b ON p.playerID = b.playerID WHERE b.SB >= 100 AND b.SB != '';

Home Run Leaders

We find the total number of home runs hit by each player in baseball history. We only display the top 20 results.

SELECT p.nameFirst, p.nameLast, b.playerID, SUM(b.HR) as mycounts FROM Batting b JOIN People p ON p.playerID = b.playerID GROUP BY b.playerID ORDER BY mycounts DESC LIMIT 20;

Total Team Salary

Give a table that shows, for each team, the total salary spent on players in that team during each year. Sort the table in the previous question according to the column displaying the total salary.

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);

Strikeouts for Batters and Pitchers

Strikeouts are good for pitchers but bad for batters. During the 2014 season, which pitcher had the most strikeouts, and how many strikeouts did they have? Which batter had the most strikeouts, and how many strikeouts did they have? Be sure to get their full names.

SELECT p.nameFirst, p.nameLast, t.SO FROM Pitching as t JOIN People as p ON p.playerID = t.playerID WHERE t.yearID = 2014 ORDER BY t.SO DESC LIMIT (1);

SELECT p.nameFirst, p.nameLast, b.SO FROM Batting as b JOIN People as p ON p.playerID = b.playerID WHERE b.yearID = 2014 ORDER BY b.SO DESC LIMIT (1);

Most Runs Batted In

In the 2023 season, find the name of the batter with the most runs batted in.

In the Batting table, the runs batted in are found in the RBI column.

 
SELECT p.nameFirst, p.nameLast, SUM(b.RBI) as mysum FROM Batting AS b JOIN People as p ON b.playerID = p.playerID WHERE b.yearID = 2023 GROUP BY b.playerID ORDER BY mysum DESC LIMIT 1;

The 30-30 Club

The "30-30" club is a name commonly given to the group of all players who stole 30 or more bases and hit 30 or more home runs in the same year. For each member of the 30-30 club, list the player’s first name, last name, the relevant year for the achievement, the number of bases stolen in that year, and the number of home runs in that year.

SELECT p.nameFirst, p.nameLast, b.yearID, SUM(b.SB) mysb, SUM(b.HR) myhr FROM People p INNER JOIN Batting b ON p.playerID = b.playerID GROUP BY p.playerID, b.yearID HAVING SUM(b.SB) >= 30 AND SUM(b.HR) >= 30;

Pitchers With Many Wins

Consider only the seasons since 1960. Find the pitchers who had 25 or more wins in a season. List the player’s first name, last name, number of wins, and the relevant year.

SELECT p.nameFirst, p.nameLast, t.W, t.yearID FROM People p INNER JOIN Pitching t ON p.playerID = t.playerID WHERE t.W >= 25 AND yearID >= 1960;

Hall Of Fame Inductions

Find the first and last names of all players inducted into the Hall of Fame since 2000. Be sure to list the year of induction, and sort the results by year.

SELECT p.nameFirst, p.nameLast, h.yearID FROM People p INNER JOIN HallOfFame h ON p.playerID=h.playerID WHERE h.yearID >= 2000 AND h.inducted = 'Y' AND h.category='Player' ORDER BY yearID;

Cy Young Award

Which pitcher has received the Cy Young Award the most times altogether? How many times did he receive this award? Find the name of this player.

SELECT COUNT(*) as mycounts, p.nameFirst, p.nameLast FROM awardsplayers as a JOIN People as p ON a.playerID = p.playerID WHERE a.awardID = 'Cy Young Award' GROUP BY p.playerID ORDER BY mycounts DESC LIMIT 1;

Managers Who Won At Least Seven World Series

Find the two managers who won at least seven World Series.

SELECT COUNT(*) as mycounts, m.playerID FROM Teams as t JOIN Managers as m ON (t.yearID = m.yearID) AND (t.teamID = m.teamID) WHERE t.WSWin = 'Y' GROUP BY m.playerID HAVING mycounts >= 7;

Mike Piazza Batting Average

Calculate Mike Piazza’s batting average during each year of his career.

A player’s batting average during a season is the number of hits (H) divided by the number of times at bat (AB). In 1998, his batting average was 0.328. It is necessary to group the player’s statistics within the year (e.g., in case they were traded during the year). It is also necessary to cast either the hits or the at-bats (or both) as a floating point number (or real number); otherwise, when dividing two integers, the result will be an integer.

 
SELECT CAST(SUM(b.H) AS FLOAT)/SUM(b.AB), b.yearID FROM People as p JOIN Batting as b ON p.playerID = b.playerID WHERE nameFirst = 'Mike' AND nameLast = 'Piazza' GROUP BY b.yearID;