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;