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
How many clubs have total medals less than 10?
assistant
SELECT count(*) FROM club_rank WHERE Total < 10
human
Show the names of clubs that have players with position 'Right Wing'.
assistant
SELECT T1.name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID = T2.Club_ID WHERE T2.Position = 'Right Wing'
human
How many clubs are there?
assistant
SELECT count(*) FROM club
human
List all club names in ascending order of start year.
assistant
SELECT name FROM club ORDER BY Start_year ASC
human
List all club names in descending alphabetical order.
assistant
SELECT name FROM club ORDER BY name DESC
human
What are the different regions of clubs in ascending alphabetical order?
assistant
SELECT DISTINCT Region FROM club ORDER BY Region ASC ;