human
Schema:
club (Club_ID, name, Region, Start_year)
club_rank (Rank, Club_ID, Gold, Silver, Bronze, Total)
player (Player_ID, name, Position, Club_ID, Apps, Tries, Goals, Points)
competition (Competition_ID, Year, Competition_type, Country)
competition_result (Competition_ID, Club_ID_1, Club_ID_2, Score)
Please answer with SQL.
assistant
Sure, I understand.
human
which country did participated in the most number of Tournament competitions?
assistant
SELECT country FROM competition WHERE competition_type = 'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1
human
What is the average points of players from club with name 'AIB'.
assistant
SELECT avg(T2.Points) FROM club AS T1 JOIN player AS T2 ON T1.Club_ID = T2.Club_ID WHERE T1.name = 'AIB'
human
How many clubs are there?
assistant
SELECT count(*) FROM club
human
How many clubs have total medals less than 10?
assistant
SELECT count(*) FROM club_rank WHERE Total < 10
human
What are the maximum and minimum number of silver medals for clubs.
assistant
SELECT max(Silver) , min(Silver) FROM club_rank
human
What is the average number of gold medals for clubs?
assistant
SELECT avg ( Gold ) FROM club_rank ;