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
List the name of the stadium where both the player 'Walter Samuel' and the player 'Thiago Motta' got injured.
assistant
SELECT 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.player = 'Walter Samuel' INTERSECT SELECT 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.player = 'Thiago Motta'
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
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
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
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
Which stadium name contains the substring 'Bank'?
assistant
SELECT name FROM stadium WHERE name LIKE "%Bank%" ;