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
What are the id and name of the stadium where the most injury accidents happened?
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
human
Which stadium name contains the substring 'Bank'?
assistant
SELECT name FROM stadium WHERE name LIKE '%Bank%'
human
What is the average attendance of stadiums with capacity percentage higher than 100%?
assistant
SELECT average_attendance FROM stadium WHERE capacity_percentage > 100
human
How many games are held after season 2007?
assistant
SELECT count(*) FROM game WHERE season > 2007
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 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 ;