%%sql 
SELECT 
    MIN(yearID) AS first_year, MAX(yearID) AS last_year
FROM
    batting;

%%sql
SELECT 
    p1.playerID,
    p1.nameFirst,
    p1.nameLast,
    p1.height,
    SUM(apps.G_all) AS games_played,
    t.name
FROM
    people p1
        INNER JOIN
    (SELECT 
        MIN(people.height) AS min_height
    FROM
        people) p2 ON p1.height = p2.min_height
        INNER JOIN
    appearances AS apps ON p1.playerID = apps.playerID
        INNER JOIN
    teams t ON t.teamID = apps.teamID
GROUP BY p1.playerID , p1.nameFirst , p1.nameLast , p1.height , t.name;

%%sql
SELECT 
    p.nameFirst, p.nameLast, SUM(s.salary) total_salary
FROM
    people p
        INNER JOIN
    salaries s ON s.playerID = p.playerID
        INNER JOIN
    collegeplaying cp ON p.playerID = cp.playerID
        INNER JOIN
    schools sc ON sc.schoolID = cp.schoolID
WHERE
    sc.name_full = 'Vanderbilt University'
GROUP BY p.nameFirst , p.nameLast
ORDER BY total_salary DESC;

%%sql
SELECT 
    CASE
        WHEN POS = 'OF' THEN 'Outfield'
        WHEN POS IN ('P' , 'C') THEN 'Battery'
        ELSE 'Infield'
    END AS POS_Group,
    SUM(PO) AS 'Putouts'
FROM
    fielding
WHERE
    yearID = 2016
GROUP BY POS_Group;

%%sql
SELECT 
    FLOOR(yearID / 10) * 10 AS decade,
    AVG(SO / G) 'K per Game',
    AVG(HR / G) 'HR per Game'
FROM
    teams
WHERE
    yearID >= 1920
GROUP BY decade;

%%sql
SELECT 
    p.nameFirst,
    p.nameLast,
    ROUND(b.SB / (b.SB + b.CS), 2) AS sb_perc
FROM
    batting b
        INNER JOIN
    people p ON p.playerID = b.playerID
WHERE
    b.SB + b.CS >= 20 AND b.yearID = 2016
ORDER BY sb_perc DESC;

%%sql
SELECT 
    t1.teamID, t1.yearID, t1.W
FROM
    teams t1
        INNER JOIN
    (SELECT 
        t2.W, t2.teamID, t2.yearID
    FROM
        teams t2
    WHERE
        t2.WSWin = 'N' AND t2.yearID >= 1970
    ORDER BY t2.W DESC
    LIMIT 1) Max_w_no_WS ON t1.teamID = Max_w_no_WS.teamID
        AND Max_w_no_WS.yearID = t1.yearID 
UNION ALL SELECT 
    t1.teamID, t1.yearID, t1.W
FROM
    teams t1
        INNER JOIN
    (SELECT 
        t2.W, t2.teamID, t2.yearID
    FROM
        teams t2
    WHERE
        t2.WSWin = 'Y' AND t2.yearID >= 1970
            AND t2.G > 150
    ORDER BY t2.W ASC
    LIMIT 1) Min_w_WS ON t1.teamID = Min_w_WS.teamID
        AND Min_w_WS.yearID = t1.yearID;

%%sql
SELECT 
    COUNT(*) / (2019 - 1970) AS perc_most_wins_and_WS_champs
FROM
    teams t1
WHERE
    (t1.yearID , t1.W) IN (SELECT 
            t2.yearID, MAX(t2.W) AS max_wins
        FROM
            teams t2
        GROUP BY t2.yearID)
        AND t1.yearID >= 1970
        AND t1.WSWin = 'Y'
ORDER BY t1.yearID DESC , t1.W DESC;

%%sql
SELECT 
    parks.parkname,
    teams.name,
    homegames.attendance / homegames.games average_attendance
FROM
    homegames
        INNER JOIN
    parks ON parks.ID = homegames.park_ID
        INNER JOIN
    teams ON teams.ID = homegames.team_ID
WHERE
    games > 10 AND yearkey = 2019
ORDER BY average_attendance DESC;

%%sql
SELECT 
    CONCAT(p.nameFirst, ' ', p.nameLast) Manager,
    am1.awardID award,
    am1.yearID year,
    am1.lgID league,
    m.teamID
FROM
    awardsmanagers am1
        INNER JOIN
    people p ON p.playerID = am1.playerID
        INNER JOIN
    managers m ON m.playerID = am1.playerID
        AND m.yearID = am1.yearID
WHERE
    am1.playerID IN (SELECT 
            am2.playerID
        FROM
            awardsmanagers am2
        WHERE
            am2.awardID = 'TSN Manager of the Year'
                AND am2.lgID = 'NL')
        AND am1.playerID IN (SELECT 
            am2.playerID
        FROM
            awardsmanagers am2
        WHERE
            am2.awardID = 'TSN Manager of the Year'
                AND am2.lgID = 'AL')
        AND am1.awardID = 'TSN Manager of the Year'