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
What are the positions with both players having more than 20 points and less than 10 points.
assistant
SELECT POSITION FROM player WHERE Points > 20 INTERSECT SELECT POSITION FROM player WHERE Points < 10
human
Show total points of all players.
assistant
SELECT sum(Points) FROM player
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
List the position of players with average number of points scored by players of that position bigger than 20.
assistant
SELECT POSITION FROM player GROUP BY name HAVING avg(Points) >= 20
human
List the position of players and the average number of points of players of each position.
assistant
SELECT POSITION , avg(Points) FROM player GROUP BY POSITION
human
What are the names of all players that got more than the average number of points?
assistant
SELECT name FROM player WHERE points > ( SELECT avg ( points ) FROM player ) ;