human
Schema:
roller_coaster (Roller_Coaster_ID, Name, Park, Country_ID, Length, Height, Speed, Opened, Status)
country (Country_ID, Name, Population, Area, Languages)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the name and population of the country that has the highest roller coaster.
assistant
SELECT T1.Name , T1.population FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID ORDER BY T2.Height DESC LIMIT 1
human
Show the park of the roller coaster with the highest speed.
assistant
SELECT Park FROM roller_coaster ORDER BY Speed DESC LIMIT 1
human
List the status shared by more than two roller coaster.
assistant
SELECT Status FROM roller_coaster GROUP BY Status HAVING COUNT(*) > 2
human
Please show the most common status of roller coasters.
assistant
SELECT Status FROM roller_coaster GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1
human
Show the different statuses and the numbers of roller coasters for each status.
assistant
SELECT Status , COUNT(*) FROM roller_coaster GROUP BY Status
human
Show the statuses of roller coasters longer than 3300 or higher than 100.
assistant
SELECT Status FROM roller_coaster WHERE LENGTH > 3300 OR Height > 100 ;