CREATE TABLE all_star (
    player_id TEXT,
    year NUMBER,
    game_num NUMBER,
    game_id TEXT,
    team_id TEXT,
    league_id TEXT,
    gp NUMBER,
    starting_pos NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE appearances (
    year NUMBER,
    team_id TEXT,
    league_id TEXT,
    player_id TEXT,
    g_all NUMBER,
    gs NUMBER,
    g_batting NUMBER,
    g_defense NUMBER,
    g_p NUMBER,
    g_c NUMBER,
    g_1b NUMBER,
    g_2b NUMBER,
    g_3b NUMBER,
    g_ss NUMBER,
    g_lf NUMBER,
    g_cf NUMBER,
    g_rf NUMBER,
    g_of NUMBER,
    g_dh NUMBER,
    g_ph NUMBER,
    g_pr NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id"),
    FOREIGN KEY ("team_id") REFERENCES team("team_id")
);
CREATE TABLE manager_award (
    player_id TEXT,
    award_id TEXT,
    year NUMBER,
    league_id TEXT,
    tie TEXT,
    notes NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE player_award (
    player_id TEXT,
    award_id TEXT,
    year NUMBER,
    league_id TEXT,
    tie TEXT,
    notes TEXT,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE manager_award_vote (
    award_id TEXT,
    year NUMBER,
    league_id TEXT,
    player_id TEXT,
    points_won NUMBER,
    points_max NUMBER,
    votes_first NUMBER
);
CREATE TABLE player_award_vote (
    award_id TEXT,
    year NUMBER,
    league_id TEXT,
    player_id TEXT,
    points_won NUMBER,
    points_max NUMBER,
    votes_first NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE batting (
    player_id TEXT,
    year NUMBER,
    stint NUMBER,
    team_id TEXT,
    league_id TEXT,
    g NUMBER,
    ab NUMBER,
    r NUMBER,
    h NUMBER,
    double NUMBER,
    triple NUMBER,
    hr NUMBER,
    rbi NUMBER,
    sb NUMBER,
    cs NUMBER,
    bb NUMBER,
    so NUMBER,
    ibb NUMBER,
    hbp NUMBER,
    sh NUMBER,
    sf NUMBER,
    g_idp NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE batting_postseason (
    year NUMBER,
    round TEXT,
    player_id TEXT,
    team_id TEXT,
    league_id TEXT,
    g NUMBER,
    ab NUMBER,
    r NUMBER,
    h NUMBER,
    double NUMBER,
    triple NUMBER,
    hr NUMBER,
    rbi NUMBER,
    sb NUMBER,
    cs NUMBER,
    bb NUMBER,
    so NUMBER,
    ibb NUMBER,
    hbp NUMBER,
    sh NUMBER,
    sf NUMBER,
    g_idp NUMBER,
    FOREIGN KEY ("team_id") REFERENCES team("team_id"),
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE player_college (
    player_id TEXT,
    college_id TEXT,
    year NUMBER,
    FOREIGN KEY ("college_id") REFERENCES college("college_id"),
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE fielding (
    player_id TEXT,
    year NUMBER,
    stint NUMBER,
    team_id TEXT,
    league_id TEXT,
    pos TEXT,
    g NUMBER,
    gs NUMBER,
    inn_outs NUMBER,
    po NUMBER,
    a NUMBER,
    e NUMBER,
    dp NUMBER,
    pb NUMBER,
    wp NUMBER,
    sb NUMBER,
    cs NUMBER,
    zr NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE fielding_outfield (
    player_id TEXT,
    year NUMBER,
    stint NUMBER,
    glf NUMBER,
    gcf NUMBER,
    grf NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE fielding_postseason (
    player_id TEXT,
    year NUMBER,
    team_id TEXT,
    league_id TEXT,
    round TEXT,
    pos TEXT,
    g NUMBER,
    gs NUMBER,
    inn_outs NUMBER,
    po NUMBER,
    a NUMBER,
    e NUMBER,
    dp NUMBER,
    tp NUMBER,
    pb NUMBER,
    sb NUMBER,
    cs NUMBER,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE hall_of_fame (
    player_id TEXT,
    yearid NUMBER,
    votedby TEXT,
    ballots NUMBER,
    needed NUMBER,
    votes NUMBER,
    inducted TEXT,
    category TEXT,
    needed_note TEXT,
    FOREIGN KEY ("player_id") REFERENCES player("player_id")
);
CREATE TABLE home_game (
 

Question: what is the full name and id of the college with the largest number of baseball players?
SQL: SELECT T1.name_full ,  T1.college_id FROM college AS T1 JOIN player_college AS T2 ON T1.college_id  =  T2.college_id GROUP BY T1.college_id ORDER BY count(*) DESC LIMIT 1;

Question: Find the full name and id of the college that has the most baseball players.
SQL: SELECT T1.name_full ,  T1.college_id FROM college AS T1 JOIN player_college AS T2 ON T1.college_id  =  T2.college_id GROUP BY T1.college_id ORDER BY count(*) DESC LIMIT 1;

Question: What is average salary of the players in the team named 'Boston Red Stockings' ?
SQL: SELECT avg(T1.salary) FROM salary AS T1 JOIN team AS T2 ON T1.team_id  =  T2.team_id_br WHERE T2.name  = 'Boston Red Stockings'

Question: Compute the average salary of the players in the team called 'Boston Red Stockings'.
SQL: SELECT avg(T1.salary) FROM salary AS T1 JOIN team AS T2 ON T1.team_id  =  T2.team_id_br WHERE T2.name  = 'Boston Red Stockings'

Question: What are first and last names of players participating in all star game in 1998?
SQL: SELECT name_first ,  name_last FROM player AS T1 JOIN all_star AS T2 ON T1.player_id  =  T2.player_id WHERE YEAR  =  1998

Question: List the first and last name for players who participated in all star game in 1998.
SQL: SELECT name_first ,  name_last FROM player AS T1 JOIN all_star AS T2 ON T1.player_id  =  T2.player_id WHERE YEAR  =  1998

Question: What are the first name, last name and id of the player with the most all star game experiences? Also list the count.
SQL: SELECT T1.name_first , T1.name_last ,  T1.player_id ,   count(*) FROM player AS T1 JOIN all_star AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.player_id ORDER BY count(*) DESC LIMIT 1;

Question: Which player has the most all star game experiences? Give me the first name, last name and id of the player, as well as the number of times the player participated in all star game.
SQL: SELECT T1.name_first , T1.name_last ,  T1.player_id ,   count(*) FROM player AS T1 JOIN all_star AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.player_id ORDER BY count(*) DESC LIMIT 1;

Question: How many players enter hall of fame each year?
SQL: SELECT yearid ,  count(*) FROM hall_of_fame GROUP BY yearid;

Question: Count the number of players who enter hall of fame for each year.
SQL: SELECT yearid ,  count(*) FROM hall_of_fame GROUP BY yearid;

Question: What is the average number of attendance at home games for each year?
SQL: SELECT YEAR ,  avg(attendance) FROM home_game GROUP BY YEAR;

Question: For each year, return the year and the average number of attendance at home games.
SQL: SELECT YEAR ,  avg(attendance) FROM home_game GROUP BY YEAR;

Question: In 2014, what are the id and rank of the team that has the largest average number of attendance?
SQL: SELECT T2.team_id ,  T2.rank FROM home_game AS T1 JOIN team AS T2 ON T1.team_id  =  T2.team_id WHERE T1.year  =  2014 GROUP BY T1.team_id ORDER BY avg(T1.attendance) DESC LIMIT 1;

Question: Find the id and rank of the team that has the highest average attendance rate in 2014.
SQL: SELECT T2.team_id ,  T2.rank FROM home_game AS T1 JOIN team AS T2 ON T1.team_id  =  T2.team_id WHERE T1.year  =  2014 GROUP BY T1.team_id ORDER BY avg(T1.attendance) DESC LIMIT 1;

Question: What are the manager's first name, last name and id who won the most manager award?
SQL: SELECT T1.name_first ,  T1.name_last ,  T2.player_id FROM player AS T1 JOIN manager_award AS T2 ON T1.player_id  =  T2.player_id GROUP BY T2.player_id ORDER BY count(*) DESC LIMIT 1;

Question: Which manager won the most manager award? Give me the manager's first name, last name and id.
SQL: SELECT T1.name_first ,  T1.name_last ,  T2.player_id FROM player AS T1 JOIN manager_award AS T2 ON T1.player_id  =  T2.player_id GROUP BY T2.player_id ORDER BY count(*) DESC LIMIT 1;

Question: How many parks are there in the state of NY?
SQL: SELECT count(*) FROM park WHERE state  =  'NY';