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: Show me the number of parks the state of NY has.
SQL: SELECT count(*) FROM park WHERE state  =  'NY';

Question: Which 3 players won the most player awards? List their full name and id.
SQL: SELECT T1.name_first ,  T1.name_last ,  T1.player_id FROM player AS T1 JOIN player_award AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.player_id ORDER BY count(*) DESC LIMIT 3;

Question: Find the first name, last name and id for the top three players won the most player awards.
SQL: SELECT T1.name_first ,  T1.name_last ,  T1.player_id FROM player AS T1 JOIN player_award AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.player_id ORDER BY count(*) DESC LIMIT 3;

Question: List three countries which are the origins of the least players.
SQL: SELECT birth_country FROM player GROUP BY birth_country ORDER BY count(*) ASC LIMIT 3;

Question: What are the three countries that the least players are from?
SQL: SELECT birth_country FROM player GROUP BY birth_country ORDER BY count(*) ASC LIMIT 3;

Question: Find all the players' first name and last name who have empty death record.
SQL: SELECT name_first ,  name_last FROM player WHERE death_year = '';

Question: What are the first name and last name of the players whose death record is empty?
SQL: SELECT name_first ,  name_last FROM player WHERE death_year = '';

Question: How many players born in USA are right-handed batters? That is, have the batter value 'R'.
SQL: SELECT count(*) FROM player WHERE birth_country = 'USA' AND bats  =  'R';

Question: Count the number of players who were born in USA and have bats information 'R'.
SQL: SELECT count(*) FROM player WHERE birth_country = 'USA' AND bats  =  'R';

Question: What is the average height of the players from the college named 'Yale University'?
SQL: SELECT avg(T1.height) FROM player AS T1 JOIN player_college AS T2 ON T1.player_id  =  T2.player_id JOIN college AS T3 ON T3.college_id  =  T2.college_id WHERE T3.name_full  =  'Yale University';

Question: Find the average height of the players who belong to the college called 'Yale University'.
SQL: SELECT avg(T1.height) FROM player AS T1 JOIN player_college AS T2 ON T1.player_id  =  T2.player_id JOIN college AS T3 ON T3.college_id  =  T2.college_id WHERE T3.name_full  =  'Yale University';

Question: What is the highest salary among each team? List the team name, id and maximum salary.
SQL: SELECT T1.name ,  T1.team_id ,  max(T2.salary) FROM team AS T1 JOIN salary AS T2 ON T1.team_id  =  T2.team_id GROUP BY T1.team_id;

Question: For each team, return the team name, id and the maximum salary among the team.
SQL: SELECT T1.name ,  T1.team_id ,  max(T2.salary) FROM team AS T1 JOIN salary AS T2 ON T1.team_id  =  T2.team_id GROUP BY T1.team_id;

Question: What are the name and id of the team offering the lowest average salary?
SQL: SELECT T1.name ,  T1.team_id FROM team AS T1 JOIN salary AS T2 ON T1.team_id = T2.team_id GROUP BY T1.team_id ORDER BY avg(T2.salary) ASC LIMIT 1;

Question: Which team offers the lowest average salary? Give me the name and id of the team.
SQL: SELECT T1.name ,  T1.team_id FROM team AS T1 JOIN salary AS T2 ON T1.team_id = T2.team_id GROUP BY T1.team_id ORDER BY avg(T2.salary) ASC LIMIT 1;

Question: Find the players' first name and last name who won award both in 1960 and in 1961.
SQL: SELECT T1.name_first , T1.name_last FROM player AS T1 JOIN player_award AS T2 WHERE T2.year  =  1960 INTERSECT SELECT T1.name_first , T1.name_last FROM player AS T1 JOIN player_award AS T2 WHERE T2.year  =  1961

Question: Which players won awards in both 1960 and 1961? Return their first names and last names.
SQL: SELECT T1.name_first , T1.name_last FROM player AS T1 JOIN player_award AS T2 WHERE T2.year  =  1960 INTERSECT SELECT T1.name_first , T1.name_last FROM player AS T1 JOIN player_award AS T2 WHERE T2.year  =  1961