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 name of players who get more than the average points.
assistant
SELECT name FROM player WHERE points > (SELECT avg(points) FROM player)
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
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 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
How many different position for players are listed?
assistant
SELECT COUNT( DISTINCT POSITION ) FROM player ;