CREATE VIEW NJITID$\_$Player$\_$History AS

WITH player$\_$career AS $($

SELECT

playerID,

COUNT$($DISTINCT yearID$)$ AS num$\_$years,

COUNT$($DISTINCT teamID$)$ AS num$\_$teams,

SUM$($HR$)$ AS runs,

ROUND$($SUM$($H$)/$ NULLIF$($SUM$($AB$),0),3)$ AS career$\_$ba$,$

SUM$($salary$)$ AS tot$\_$sal,

AVG$($salary$)$ AS avg$\_$sal,

MIN$($salary$)$ AS min$\_$Salary,

MAX$($salary$)$ AS max$\_$salary,

ROUND$(($MAX$($salary$)-$ MIN$($salary$))/$ NULLIF$($MIN$($salary$),0),3)$ AS perct$\_$incr,

COUNT$($DISTINCT CASE WHEN lgID $=\text{'}$MLB$\text{'}$ THEN yearID ELSE NULL END$)$ AS max$\_$appear,

MAX$($CASE WHEN lgID $=\text{'}$MLB$\text{'}$ THEN yearID ELSE NULL END$)$ AS max$\_$col

FROM

Batting

LEFT JOIN Salaries USING $($playerID$,$ yearID$)$

GROUP BY

playerID

$),$ player$\_$info AS $($

SELECT

playerID,

nameGiven AS fullname,

SUM$(401$k$)$ AS Total$\_401$K$,$

MAX$($CASE WHEN lgID $=\text{'}$MLB$\text{'}$ THEN yearID ELSE NULL END$)$ AS last$\_$year$\_$played

FROM

People

LEFT JOIN Fielding USING $($playerID$)$

GROUP BY

playerID, fullname

$),$ player$\_$college AS $($

SELECT

playerID,

COUNT$($DISTINCT schoolID$)$ AS Num$\_$schools,

MAX$($yearID$)$ AS max$\_$col

FROM

CollegePlaying

GROUP BY

playerID

$),$ player$\_$pitching AS $($

SELECT

playerID,

SUM$($W$)$ AS tot$\_$win,

SUM$($SO$)$ AS tot$\_$so$,$

ROUND$(($SUM$($SO$)+$ SUM$($BB$))/$ NULLIF$($SUM$($IPouts$)/3,0),3)$ AS car$\_$pfr

FROM

Pitching

GROUP BY

playerID

$),$ player$\_$awards AS $($

SELECT

playerID,

COUNT$(*)$ AS tot$\_$award$\_$play

FROM

AwardsPlayer

GROUP BY

playerID

$),$ manager$\_$awards AS $($

SELECT

playerID,

COUNT$(*)$ AS tot$\_$award$\_$man

FROM

AwardsManager

GROUP BY

playerID

$),$ hall$\_$of$\_$fame AS $($

SELECT

playerID,

MAX$($CASE WHEN inducted $=\text{'}$Y$\text{'}$ THEN yearid ELSE NULL END$)$ AS iyear,

COUNT$(*)$ AS nomcount,

MAX$($inducted$)$ AS inducted

FROM

HallOfFame

GROUP BY

playerID

$)$

SELECT

player$\_$info.playerID,

player$\_$info.fullname,

player$\_$info.Total$\_401$K$,$

player$\_$career.num$\_$years,

player$\_$career.num$\_$teams,

player$\_$career.runs,

player$\_$career.career$\_$ba$,$

player$\_$career.tot$\_$sal,

player$\_$career.avg$\_$sal,

player$\_$career.min$\_$Salary,

player$\_$career.max$\_$salary,

player$\_$career.perct$\_$incr,

player$\_$college.Num$\_$schools,

player$\_$college.max$\_$col,

player$\_$career.last$\_$year$\_$played,

player$\_$pitching.tot$\_$win,

player$\_$pitching.tot$\_$so$,$

player$\_$pitching.car$\_$w$,$

player$\_$pitching.car$\_$l$,$

player$\_$pitching.car$\_$era,

player$\_$pitching.car$\_$whip,

player$\_$pitching.car$\_$so$\_$per$\_$nine