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
Which team had the least number of attendances in home games in 1980?
assistant
SELECT T2.name FROM home_game AS T1 JOIN team AS T2 ON T1.team_id = T2.team_id_br WHERE T1.year = 1980 ORDER BY T1.attendance ASC LIMIT 1;
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
In 2014, what are the id and rank of the team that has the largest average number of attendance?
assistant
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;
human
Find the number of team franchises that are active (have 'Y' as 'active' information).
assistant
SELECT COUNT( * ) FROM team_franchise WHERE active = "Y" ;