human
Schema:
stadium (id, name, Home_Games, Average_Attendance, Total_Attendance, Capacity_Percentage)
game (stadium_id, id, Season, Date, Home_team, Away_team, Score, Competition)
injury_accident (game_id, id, Player, Injury, Number_of_matches, Source)
Please answer with SQL.
assistant
Sure, I understand.
human
For each injury accident, find the date of the game and the name of the injured player in the game, and sort the results in descending order of game season.
assistant
SELECT T1.date , T2.player FROM game AS T1 JOIN injury_accident AS T2 ON T1.id = T2.game_id ORDER BY T1.season DESC
human
How many games has each stadium held?
assistant
SELECT T1.id , count(*) FROM stadium AS T1 JOIN game AS T2 ON T1.id = T2.stadium_id GROUP BY T1.id
human
What are the maximum, minimum and average home games each stadium held?
assistant
SELECT max(home_games) , min(home_games) , avg(home_games) FROM stadium
human
How many games are held after season 2007?
assistant
SELECT count(*) FROM game WHERE season > 2007
human
List the dates of games by the home team name in descending order.
assistant
SELECT Date FROM game ORDER BY home_team DESC
human
List the season, home team, away team of all the games.
assistant
SELECT season , home_team , away_team FROM game ;