practice_sql

COST OF PITCHERS

SELECT
  salaries.yearid AS "Year",
  people.namegiven AS "Player Name",
  ROUND(salaries.salary / pitching.g) AS "Cost per Game",
  pitching.gs AS "Games Started"
FROM salaries
INNER JOIN pitching
	ON salaries.playerid = pitching.playerid
  AND salaries.yearid = pitching.yearid
  AND salaries.teamid = pitching.teamid
INNER JOIN people
	ON salaries.playerid = people.playerid
 WHERE pitching.gs >= 10
 GROUP BY 1, 2, 3, 4
 ORDER BY 1 DESC, 3 DESC


-----

MOST HOME RUNS BY YEAR

SELECT
    batting.yearid AS "Year",
    people.namegiven AS "Player Name",
    batting.hr AS "Home Runs"
FROM batting
INNER JOIN (
    SELECT yearid, MAX(batting.hr) AS max_hr
    FROM batting
    GROUP BY yearid
) AS max_hr_per_year
ON batting.yearid = max_hr_per_year.yearid AND batting.hr = max_hr_per_year.max_hr
INNER JOIN people
ON batting.playerid = people.playerid
ORDER BY 1 DESC, 3 DESC;