proj1.sql

-- Before running drop any existing views
DROP VIEW IF EXISTS q0;
DROP VIEW IF EXISTS q1i;
DROP VIEW IF EXISTS q1ii;
DROP VIEW IF EXISTS q1iii;
DROP VIEW IF EXISTS q1iv;
DROP VIEW IF EXISTS q2i;
DROP VIEW IF EXISTS q2ii;
DROP VIEW IF EXISTS q2iii;
DROP VIEW IF EXISTS q3i;
DROP VIEW IF EXISTS q3ii;
DROP VIEW IF EXISTS q3iii;
DROP VIEW IF EXISTS q4i;
DROP VIEW IF EXISTS q4ii;
DROP VIEW IF EXISTS q4iii;
DROP VIEW IF EXISTS q4iv;
DROP VIEW IF EXISTS q4v;

-- Question 0
CREATE VIEW q0(era) AS
  SELECT MAX(era) FROM pitching
;

-- Question 1i
CREATE VIEW q1i(namefirst, namelast, birthyear) AS
  SELECT namefirst, namelast, birthyear FROM people WHERE weight > 300
;

-- Question 1ii
CREATE VIEW q1ii(namefirst, namelast, birthyear) AS
  SELECT namefirst, namelast, birthyear FROM people WHERE namefirst LIKE '% %' ORDER BY namefirst ASC, nameLast ASC
;

-- Question 1iii
CREATE VIEW q1iii(birthyear, avgheight, count) AS
  SELECT birthyear, AVG(height), COUNT(playerID) FROM people GROUP BY birthyear ORDER BY birthyear ASC
;

-- Question 1iv
CREATE VIEW q1iv(birthyear, avgheight, count) AS
  SELECT birthyear, AVG(height), COUNT(playerID) FROM people GROUP BY birthyear HAVING AVG(height) > 70 ORDER BY birthyear ASC
;

-- Question 2i
CREATE VIEW q2i(namefirst, namelast, playerid, yearid) AS
  SELECT people.namefirst, people.namelast, people.playerID, HallofFame.yearID FROM HallofFame LEFT OUTER JOIN people ON HallofFame.playerID = people.playerID WHERE HallofFame.inducted = 'Y' ORDER BY HallofFame.yearID DESC, people.playerID ASC
;

-- Question 2ii
CREATE VIEW q2ii(namefirst, namelast, playerid, schoolid, yearid) AS
  SELECT people.namefirst, people.namelast, people.playerID, CollegePlaying.schoolID, HallofFame.yearID FROM HallofFame LEFT OUTER JOIN people ON HallofFame.playerID = people.playerID LEFT OUTER JOIN CollegePlaying ON HallofFame.playerID = CollegePlaying.playerID LEFT OUTER JOIN Schools ON CollegePlaying.schoolID = Schools.schoolID WHERE HallofFame.inducted = 'Y' AND Schools.schoolState = 'CA' ORDER BY HallofFame.yearID DESC, CollegePlaying.schoolID ASC, people.playerID ASC
;

-- Question 2iii
CREATE VIEW q2iii(playerid, namefirst, namelast, schoolid) AS
  SELECT people.playerID, people.namefirst, people.namelast, CollegePlaying.schoolID FROM HallofFame LEFT OUTER JOIN people ON HallofFame.playerID = people.playerID LEFT OUTER JOIN CollegePlaying ON HallofFame.playerID = CollegePlaying.playerID WHERE HallofFame.inducted = 'Y' ORDER BY people.playerID DESC, CollegePlaying.schoolID ASC
;

-- Question 3i
CREATE VIEW q3i(playerid, namefirst, namelast, yearid, slg) AS
  SELECT people.playerID, people.namefirst, people.namelast, batting.yearid, (CAST(((batting.H - batting.H2B - batting.H3B - batting.HR) + (2 * batting.H2B) + (3 * batting.H3B) + (4 * batting.HR)) AS FLOAT) / CAST(batting.AB AS FLOAT)) FROM batting LEFT OUTER JOIN people ON batting.playerID = people.playerID WHERE batting.AB > 50 ORDER BY (CAST(((batting.H - batting.H2B - batting.H3B - batting.HR) + (2 * batting.H2B) + (3 * batting.H3B) + (4 * batting.HR)) AS FLOAT) / CAST(batting.AB AS FLOAT)) DESC, batting.yearID ASC, people.playerID ASC LIMIT 10
;

-- Question 3ii
CREATE VIEW q3ii(playerid, namefirst, namelast, lslg) AS
  SELECT MIN(people.playerID), MIN(people.nameFirst), MIN(people.nameLast), (CAST(((SUM(batting.H) - SUM(batting.H2B) - SUM(batting.H3B) - SUM(batting.HR)) + (2 * SUM(batting.H2B)) + (3 * SUM(batting.H3B)) + (4 * SUM(batting.HR))) AS FLOAT) / CAST(SUM(batting.AB) AS FLOAT)) FROM batting LEFT OUTER JOIN people ON batting.playerID = people.playerID GROUP BY batting.playerID HAVING SUM(batting.AB) > 50 ORDER BY (CAST(((SUM(batting.H) - SUM(batting.H2B) - SUM(batting.H3B) - SUM(batting.HR)) + (2 * SUM(batting.H2B)) + (3 * SUM(batting.H3B)) + (4 * SUM(batting.HR))) AS FLOAT) / CAST(SUM(batting.AB) AS FLOAT)) DESC, batting.playerID ASC LIMIT 10
;

-- Question 3iii
CREATE VIEW q3iii(namefirst, namelast, lslg) AS
  SELECT MIN(people.nameFirst), MIN(people.nameLast), (CAST(((SUM(batting.H) - SUM(batting.H2B) - SUM(batting.H3B) - SUM(batting.HR)) + (2 * SUM(batting.H2B)) + (3 * SUM(batting.H3B)) + (4 * SUM(batting.HR))) AS FLOAT) / CAST(SUM(batting.AB) AS FLOAT)) FROM batting LEFT OUTER JOIN people ON batting.playerID = people.playerID GROUP BY batting.playerID HAVING (CAST(((SUM(batting.H) - SUM(batting.H2B) - SUM(batting.H3B) - SUM(batting.HR)) + (2 * SUM(batting.H2B)) + (3 * SUM(batting.H3B)) + (4 * SUM(batting.HR))) AS FLOAT) / CAST(SUM(batting.AB) AS FLOAT)) > (SELECT (CAST(((SUM(batting.H) - SUM(batting.H2B) - SUM(batting.H3B) - SUM(batting.HR)) + (2 * SUM(batting.H2B)) + (3 * SUM(batting.H3B)) + (4 * SUM(batting.HR))) AS FLOAT) / CAST(SUM(batting.AB) AS FLOAT)) FROM batting LEFT OUTER JOIN people ON batting.playerID = people.playerID GROUP BY batting.playerID HAVING batting.playerID = 'mayswi01') AND SUM(batting.AB) > 50
;

-- Question 4i
CREATE VIEW q4i(yearid, min, max, avg) AS
  SELECT yearid, MIN(salary), MAX(salary), AVG(salary) FROM Salaries GROUP BY yearid ORDER BY yearid ASC
;

-- Question 4ii Make each column (binid, low, high, count) as a separate helper table. Also use WITH
CREATE VIEW q4ii(binid, low, high, count) AS
  WITH temp1(yearID, min, max, avg) AS (SELECT yearid, MIN(salary), MAX(salary), AVG(salary) FROM Salaries GROUP BY yearid HAVING yearid = '2016'),
  temp2(binid, low, high) AS (SELECT binids.binid, ((((temp1.max-temp1.min)/(10*1.0)) * binids.binid) + min),((((temp1.max-temp1.min)/(10*1.0)) * (binids.binid+1)) + min) FROM binids LEFT OUTER JOIN temp1 ON TRUE),
  temp3(salary) AS (SELECT salary FROM Salaries WHERE yearID = '2016'),
  temp4(salary,binid) AS (SELECT temp3.salary, temp2.binid FROM temp3 LEFT OUTER JOIN temp2 ON (temp3.salary >= temp2.low AND temp3.salary < temp2.high)),
  temp5(binid, amount) AS (SELECT binids.binid, COUNT(salary) FROM binids LEFT OUTER JOIN temp4 ON binids.binid = temp4.binid GROUP BY binids.binid HAVING binids.binid <9),
  specialrange(low,high) AS (SELECT low, high FROM temp2 WHERE binid = 9),
  temp6(binid, amount) AS (SELECT 9, COUNT(*) FROM temp3 WHERE temp3.salary <= (SELECT high FROM specialrange) AND temp3.salary >= (SELECT low FROM specialrange)),
  temp7(binid,amount) AS (SELECT * FROM temp5 UNION SELECT * FROM temp6)
  SELECT temp2.binid,temp2.low,temp2.high,temp7.amount FROM temp2 LEFT OUTER JOIN temp7 ON temp2.binid = temp7.binid
;

-- Question 4iii
CREATE VIEW q4iii(yearid, mindiff, maxdiff, avgdiff) AS
  SELECT A.yearid, (A.min-B.min), (A.max-B.max), (A.avg-B.avg) FROM q4i as A LEFT OUTER JOIN q4i AS B on A.yearid = B.yearid + 1 WHERE (A.min-B.min) IS NOT NULL ORDER BY A.yearid ASC
;

-- Question 4iv Find the players that have the max in each year separately!
CREATE VIEW q4iv(playerid, namefirst, namelast, salary, yearid) AS
  WITH tempTable(yearID,largest) AS (SELECT yearID, MAX(salary) FROM salaries GROUP BY yearID HAVING yearID = '2000' OR yearID = '2001'),
  tempTable2(yearID, playerID, salary) AS (SELECT yearID, playerID, salary FROM salaries WHERE yearID = '2000' OR yearID = '2001'),
  tempTable3(yearID, playerID,salary) AS (SELECT tempTable2.yearID, tempTable2.playerID, tempTable2.salary FROM tempTable2 LEFT OUTER JOIN tempTable ON tempTable2.yearID = tempTable.yearID WHERE tempTable2.salary = tempTable.largest)
  SELECT people.playerID, people.namefirst, people.namelast, tempTable3.salary, tempTable3.yearID FROM tempTable3 LEFT OUTER JOIN people ON tempTable3.playerID = people.playerID
;
-- Question 4v
CREATE VIEW q4v(team, diffAvg) AS
  WITH temp1(playerID,yearID,teamID) AS (SELECT playerID, yearID, teamID FROM allstarfull WHERE yearID = '2016'),
  temp2(yearID, playerID, salary) AS (SELECT yearID, playerID, salary FROM Salaries WHERE yearID = '2016')
  SELECT temp1.teamID, MAX(temp2.salary) - MIN(temp2.salary) FROM temp1 LEFT OUTER JOIN temp2 ON temp1.playerID = temp2.playerID GROUP BY temp1.teamID
;