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
What are the distinct hometowns of gymnasts with total points more than 57.5?
assistant
SELECT DISTINCT T2.Hometown FROM gymnast AS T1 JOIN people AS T2 ON T1.Gymnast_ID = T2.People_ID WHERE T1.Total_Points > 57.5
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 names of the top 5 oldest people.
assistant
SELECT Name FROM People ORDER BY Age DESC LIMIT 5
human
What are the hometowns that are shared by at least two gymnasts?
assistant
SELECT T2.Hometown FROM gymnast AS T1 JOIN people AS T2 ON T1.Gymnast_ID = T2.People_ID GROUP BY T2.Hometown HAVING COUNT(*) >= 2
human
Show the hometowns shared by people older than 23 and younger than 20.
assistant
SELECT Hometown FROM people WHERE Age > 23 INTERSECT SELECT Hometown FROM people WHERE Age < 20
human
How many distinct hometowns did these people have?
assistant
SELECT COUNT( DISTINCT Hometown ) FROM people ;