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 the names of clubs that do not have any players.
assistant
SELECT name FROM CLub WHERE Club_ID NOT IN (SELECT Club_ID FROM player)
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
How many clubs have total medals less than 10?
assistant
SELECT COUNT( * ) FROM club_rank WHERE Total < 10 ;