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 much did the the player with first name Len and last name Barker earn between 1985 to 1990 in total?
assistant
SELECT sum(T1.salary) FROM salary AS T1 JOIN player AS T2 ON T1.player_id = T2.player_id WHERE T2.name_first = 'Len' AND T2.name_last = 'Barker' AND T1.year BETWEEN 1985 AND 1990;
human
What is the highest salary among each team? List the team name, id and maximum salary.
assistant
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;
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
Find the players' first name and last name who won award both in 1960 and in 1961.
assistant
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
human
Which 3 players won the most player awards? List their full name and id.
assistant
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;
human
Which manager won the most manager award? Give me the manager's first name, last name and id.
assistant
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 ;