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
Find all the players' first name and last name who have empty death record.
assistant
SELECT name_first , name_last FROM player WHERE death_year = ;
human
How many players enter hall of fame each year?
assistant
SELECT yearid , count(*) FROM hall_of_fame GROUP BY yearid;
human
List players' first name and last name who have weight greater than 220 or height shorter than 75.
assistant
SELECT name_first , name_last FROM player WHERE weight > 220 OR height < 75
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
What are first and last names of players participating in all star game in 1998?
assistant
SELECT name_first , name_last FROM player AS T1 JOIN all_star AS T2 ON T1.player_id = T2.player_id WHERE YEAR = 1998
human
Which player has the most all star game experiences? Give me the first name, last name and id of the player, as well as the number of times the player participated in all star game.
assistant
SELECT T1.name_first , T1.name_last , T1.player_id , COUNT( * ) FROM player AS T1 JOIN all_star AS T2 ON T1.player_id = T2.player_id GROUP BY T1.player_id ORDER BY COUNT( * ) DESC LIMIT 1 ;