Best of Baseball Solutions

Hey there everyone!
Haven’t seen anyone sharing their code for the “Off Platform Project: The Best Of Baseball Awards”.
I’d like to share mine and invite others to do the same!
I ended up not using inner joins, results might be a bit different.

--INSERT INTO films (name, release_year)
--VALUES ('Drive', 2011);
--('Call Me By Your Name', 2017),
--('Monster Inc.', 2001);

--ALTER TABLE films
--ADD COLUMN runtime VARCHAR,
--ADD COLUMN category VARCHAR,
--ADD COLUMN rating FLOAT,
--ADD COLUMN box_office_earnings INTEGER;

--UPDATE films SET category = 'Comedy', rating = 4.5, runtime = 92 WHERE name = 'Monster Inc.';

--ALTER TABLE films ADD CONSTRAINT single_name UNIQUE (name);

--ALTER TABLE films ALTER COLUMN name SET NOT NULL;

--Heaviest hitters: Miami Marlins
--SELECT AVG(people.weight), teams.name FROM people, batting, teams WHERE people.playerid = batting.playerid AND batting.teamid = teams.teamid GROUP BY teams.name ORDER BY 1 DESC;

--Shorterst Sluggers: Middletown Mansfields
--SELECT AVG(people.height), teams.name FROM people, batting, teams WHERE people.playerid = batting.playerid AND batting.teamid = teams.teamid GROUP BY teams.name ORDER BY 1 ASC;

--Biggest Spenders: New York Yankees
--WITH sums AS (SELECT SUM(salaries.salary), teams.name, salaries.yearid FROM people, salaries, teams WHERE people.playerid = salaries.playerid AND salaries.teamid = teams.teamid GROUP BY teams.name, salaries.yearid ORDER BY 3 DESC) SELECT MAX(sum), yearid FROM sums GROUP BY yearid ORDER BY yearid;

--Most Bang For Their Buck In 2010
--SELECT ROUND(SUM(salaries.salary)/teams.w), teams.name, salaries.yearid FROM salaries, teams WHERE salaries.teamid = teams.teamid AND teams.yearid = salaries.yearid AND salaries.yearid=2010 GROUP BY teams.name, salaries.yearid, teams.w ORDER BY 1 ASC;

--Priciest Starter: Couldn't complete, had to copy from solution 
-- SELECT 
-- 	people.namefirst,
-- 	people.namelast, 
-- 	salaries.salary/pitching.g as cost_per_game, 
-- 	salaries.yearid, 
-- 	pitching.g
-- 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.g > 10
-- ORDER BY 
-- 	salaries.salary/pitching.g 
-- 	DESC;


--Most pro players school: University of Texas at Austin
--SELECT schools.name_full, collegeplaying.schoolid, COUNT(people.playerid) FROM schools, collegeplaying, people WHERE people.playerid = collegeplaying.playerid AND schools.schoolid = collegeplaying.schoolid GROUP BY schools.schoolid, schools.name_full, collegeplaying.schoolid ORDER BY COUNT(people.playerid) DESC;