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
What were all the salary values of players in 2010 and 2001?
assistant
SELECT salary FROM salary WHERE YEAR = 2010 UNION SELECT salary FROM salary WHERE YEAR = 2001
human
What is the total salary paid by team Boston Red Stockings in 2010?
assistant
SELECT sum(T1.salary) 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 = 2010
human
List the 3 highest salaries of the players in 2001?
assistant
SELECT salary FROM salary WHERE YEAR = 2001 ORDER BY salary DESC LIMIT 3;
human
What is average salary of the players in the team named 'Boston Red Stockings' ?
assistant
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'
human
What are the first name and last name of the players who were paid salary by team Washington Nationals in both 2005 and 2007?
assistant
SELECT T2.name_first , T2.name_last FROM salary AS T1 JOIN player AS T2 ON T1.player_id = T2.player_id JOIN team AS T3 ON T3.team_id_br = T1.team_id WHERE T1.year = 2005 AND T3.name = "Washington Nationals" INTERSECT SELECT T2.name_first , T2.name_last FROM salary AS T1 JOIN player AS T2 ON T1.player_id = T2.player_id JOIN team AS T3 ON T3.team_id_br = T1.team_id WHERE T1.year = 2007 AND T3.name = "Washington Nationals" ;