SQL Lab 4: Answer

How many lifetime hits does Barry Bonds have?

SELECT SUM(stats.hits)
FROM players INNER JOIN stats ON players.id = stats.player_id
WHERE players.first_name = 'Barry'
AND players.last_name = 'Bonds';

What are the first and last names of the players who played for the 2020 Chicago Cubs?

SELECT players.first_name, players.last_name
FROM stats 
  INNER JOIN teams ON teams.id = stats.team_id
  INNER JOIN players ON players.id = stats.player_id
WHERE teams.year = 2020
AND teams.name = "Chicago Cubs";

Who hit the most home runs in 2019, and what team did they play for?

SELECT teams.name, players.first_name, players.last_name, stats.home_runs
FROM stats 
  INNER JOIN teams ON teams.id = stats.team_id
  INNER JOIN players ON players.id = stats.player_id
WHERE teams.year = 2019
ORDER BY stats.home_runs DESC
LIMIT 1;

Who was the leading home run hitter for each team in 2019?

-- *** this "accidentally" works but selected columns
--   do not appear in the group by,
--   so some SQL engines will raise a warning:
--   "Field of aggregated query neither grouped nor aggregated"

-- SELECT teams.name, players.first_name, players.last_name, MAX(stats.home_runs)
-- FROM stats 
--   INNER JOIN teams ON teams.id = stats.team_id
--   INNER JOIN players ON players.id = stats.player_id
-- WHERE teams.year = 2019
-- GROUP BY teams.name;

-- *** this is a more accurate solution.
--   note the difference in the results between the queries
--   (some teams have more than 1 leading home run hitter in 2019).

SELECT teams.name, players.first_name, players.last_name, stats.home_runs
FROM (
  SELECT stats.team_id AS team_id, MAX(stats.home_runs) AS home_runs
  FROM stats 
  INNER JOIN teams ON teams.id = stats.team_id
  WHERE teams.year = 2019
  GROUP BY stats.team_id
) AS max_run_stats
INNER JOIN stats ON stats.team_id = max_run_stats.team_id
AND stats.home_runs = max_run_stats.home_runs
INNER JOIN teams ON stats.team_id = teams.id
INNER JOIN players ON players.id = stats.player_id
ORDER BY teams.name;

-- ***