human
Schema:
all_star (player_id, year, game_num, game_id, team_id, league_id, gp, starting_pos)
appearances (year, team_id, league_id, player_id, g_all, gs, g_batting, g_defense, g_p, g_c, g_1b, g_2b, g_3b, g_ss, g_lf, g_cf, g_rf, g_of, g_dh, g_ph, g_pr)
manager_award (player_id, award_id, year, league_id, tie, notes)
player_award (player_id, award_id, year, league_id, tie, notes)
manager_award_vote (award_id, year, league_id, player_id, points_won, points_max, votes_first)
player_award_vote (award_id, year, league_id, player_id, points_won, points_max, votes_first)
batting (player_id, year, stint, team_id, league_id, g, ab, r, h, double, triple, hr, rbi, sb, cs, bb, so, ibb, hbp, sh, sf, g_idp)
batting_postseason (year, round, player_id, team_id, league_id, g, ab, r, h, double, triple, hr, rbi, sb, cs, bb, so, ibb, hbp, sh, sf, g_idp)
player_college (player_id, college_id, year)
fielding (player_id, year, stint, team_id, league_id, pos, g, gs, inn_outs, po, a, e, dp, pb, wp, sb, cs, zr)
fielding_outfield (player_id, year, stint, glf, gcf, grf)
fielding_postseason (player_id, year, team_id, league_id, round, pos, g, gs, inn_outs, po, a, e, dp, tp, pb, sb, cs)
hall_of_fame (player_id, yearid, votedby, ballots, needed, votes, inducted, category, needed_note)
home_game (year, league_id, team_id, park_id, span_first, span_last, games, openings, attendance)
manager (player_id, year, team_id, league_id, inseason, g, w, l, rank, plyr_mgr)
manager_half (player_id, year, team_id, league_id, inseason, half, g, w, l, rank)
player (player_id, birth_year, birth_month, birth_day, birth_country, birth_state, birth_city, death_year, death_month, death_day, death_country, death_state, death_city, name_first, name_last, name_given, weight, height, bats, throws, debut, final_game, retro_id, bbref_id)
park (park_id, park_name, park_alias, city, state, country)
pitching (player_id, year, stint, team_id, league_id, w, l, g, gs, cg, sho, sv, ipouts, h, er, hr, bb, so, baopp, era, ibb, wp, hbp, bk, bfp, gf, r, sh, sf, g_idp)
pitching_postseason (player_id, year, round, team_id, league_id, w, l, g, gs, cg, sho, sv, ipouts, h, er, hr, bb, so, baopp, era, ibb, wp, hbp, bk, bfp, gf, r, sh, sf, g_idp)
salary (year, team_id, league_id, player_id, salary)
college (college_id, name_full, city, state, country)
postseason (year, round, team_id_winner, league_id_winner, team_id_loser, league_id_loser, wins, losses, ties)
team (year, league_id, team_id, franchise_id, div_id, rank, g, ghome, w, l, div_win, wc_win, lg_win, ws_win, r, ab, h, double, triple, hr, bb, so, sb, cs, hbp, sf, ra, er, era, cg, sho, sv, ipouts, ha, hra, bba, soa, e, dp, fp, name, park, attendance, bpf, ppf, team_id_br, team_id_lahman45, team_id_retro)
team_franchise (franchise_id, franchise_name, active, na_assoc)
team_half (year, league_id, team_id, half, div_id, div_win, rank, g, w, l)
Please answer with SQL.
assistant
Sure, I understand.
human
How many players were in the team Boston Red Stockings in 2000?
assistant
SELECT count(*) FROM salary AS T1 JOIN team AS T2 ON T1.team_id = T2.team_id_br WHERE T2.name = 'Boston Red Stockings' AND T1.year = 2000
human
List the maximum scores of the team Boston Red Stockings when the team won in postseason?
assistant
SELECT max(T1.wins) FROM postseason AS T1 JOIN team AS T2 ON T1.team_id_winner = T2.team_id_br WHERE T2.name = 'Boston Red Stockings';
human
How many home games did the team Boston Red Stockings play from 1990 to 2000 in total?
assistant
SELECT sum(T1.games) FROM home_game AS T1 JOIN team AS T2 ON T1.team_id = T2.team_id_br WHERE T2.name = 'Boston Red Stockings' AND T1.year BETWEEN 1990 AND 2000;
human
What is the total number of postseason games that team Boston Red Stockings participated in?
assistant
SELECT count(*) FROM ( SELECT * FROM postseason AS T1 JOIN team AS T2 ON T1.team_id_winner = T2.team_id_br WHERE T2.name = 'Boston Red Stockings' UNION SELECT * FROM postseason AS T1 JOIN team AS T2 ON T1.team_id_loser = T2.team_id_br WHERE T2.name = 'Boston Red Stockings' );
human
What is the number of wins the team Boston Red Stockings got in the postseasons each year in history?
assistant
SELECT count(*) , T1.year FROM postseason AS T1 JOIN team AS T2 ON T1.team_id_winner = T2.team_id_br WHERE T2.name = 'Boston Red Stockings' GROUP BY T1.year
human
How many times did Boston Red Stockings lose in 2009 postseason?
assistant
SELECT COUNT( * ) FROM postseason AS T1 JOIN team AS T2 ON T1.team_id_loser = T2.team_id_br WHERE T2.name = "Boston Red Stockings" AND T1.year = 2009 ;