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;