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
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
List the names of the top 5 oldest people.
assistant
SELECT Name FROM People ORDER BY Age DESC LIMIT 5
human
How many distinct hometowns did these people have?
assistant
SELECT count(DISTINCT Hometown) FROM people
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 ;