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
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 names of countries and the average speed of roller coasters from each country.
assistant
SELECT T1.Name , avg(T2.Speed) FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID GROUP BY T1.Name
human
How many roller coasters are there?
assistant
SELECT count(*) FROM roller_coaster
human
What are the lengths and heights of roller coasters?
assistant
SELECT LENGTH , Height FROM roller_coaster
human
What are the speeds of the longest roller coaster?
assistant
SELECT Speed FROM roller_coaster ORDER BY LENGTH DESC LIMIT 1
human
What is the average speed of roller coasters?
assistant
SELECT avg ( Speed ) FROM roller_coaster ;