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
In which season and which stadium did any player have an injury of 'Foot injury' or 'Knee problem'?
assistant
SELECT T1.season , T2.name FROM game AS T1 JOIN stadium AS T2 ON T1.stadium_id = T2.id JOIN injury_accident AS T3 ON T1.id = T3.game_id WHERE T3.injury = 'Foot injury' OR T3.injury = 'Knee problem'
human
How many games are free of injury accidents?
assistant
SELECT count(*) FROM game WHERE id NOT IN ( SELECT game_id FROM injury_accident )
human
Show the name, average attendance, total attendance for stadiums where no accidents happened.
assistant
SELECT name , average_attendance , total_attendance FROM stadium EXCEPT SELECT T2.name , T2.average_attendance , T2.total_attendance FROM game AS T1 JOIN stadium AS T2 ON T1.stadium_id = T2.id JOIN injury_accident AS T3 ON T1.id = T3.game_id
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
What are the ids, scores, and dates of the games which caused at least two injury accidents?
assistant
SELECT T1.id , T1.score , T1.date FROM game AS T1 JOIN injury_accident AS T2 ON T2.game_id = T1.id GROUP BY T1.id HAVING count(*) >= 2
human
Find the id and name of the stadium where the largest number of injury accidents occurred.
assistant
SELECT T1.id , T1.name FROM stadium AS T1 JOIN game AS T2 ON T1.id = T2.stadium_id JOIN injury_accident AS T3 ON T2.id = T3.game_id GROUP BY T1.id ORDER BY COUNT( * ) DESC LIMIT 1 ;