human
Schema:
gymnast (Gymnast_ID, Floor_Exercise_Points, Pommel_Horse_Points, Rings_Points, Vault_Points, Parallel_Bars_Points, Horizontal_Bar_Points, Total_Points)
people (People_ID, Name, Age, Height, Hometown)
Please answer with SQL.
assistant
Sure, I understand.
human
List the distinct hometowns that are not associated with any gymnast.
assistant
SELECT DISTINCT Hometown FROM people EXCEPT SELECT DISTINCT T2.Hometown FROM gymnast AS T1 JOIN people AS T2 ON T1.Gymnast_ID = T2.People_ID
human
What are the hometowns of gymnasts and the corresponding number of gymnasts?
assistant
SELECT T2.Hometown , COUNT(*) FROM gymnast AS T1 JOIN people AS T2 ON T1.Gymnast_ID = T2.People_ID GROUP BY T2.Hometown
human
List the total points of gymnasts in descending order.
assistant
SELECT Total_Points FROM gymnast ORDER BY Total_Points DESC
human
How many gymnasts are there?
assistant
SELECT count(*) FROM gymnast
human
List the names of gymnasts in ascending order by their heights.
assistant
SELECT T2.Name FROM gymnast AS T1 JOIN people AS T2 ON T1.Gymnast_ID = T2.People_ID ORDER BY T2.Height ASC
human
Return the names of the gymnasts.
assistant
SELECT T2.Name FROM gymnast AS T1 JOIN people AS T2 ON T1.Gymnast_ID = T2.People_ID ;