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
List all club names in descending alphabetical order.
assistant
SELECT name FROM club ORDER BY name DESC
human
List the distinct region of clubs in ascending alphabetical order.
assistant
SELECT DISTINCT Region FROM club ORDER BY Region ASC
human
How many clubs are there?
assistant
SELECT count(*) FROM club
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
human
What is the total number of clubs that have less than 10 medals in total?
assistant
SELECT COUNT( * ) FROM club_rank WHERE Total < 10 ;