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 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
find the number of players whose points are lower than 30 in each position.
assistant
SELECT count(*) , POSITION FROM player WHERE points < 30 GROUP BY POSITION
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
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
what are the name of players who get more than the average points.
assistant
SELECT name FROM player WHERE points > (SELECT avg(points) FROM player)
human
For each position, what is the average number of points for players in that position?
assistant
SELECT POSITION , avg ( Points ) FROM player GROUP BY POSITION ;