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
What is the average speed of roller coasters?
assistant
SELECT avg(Speed) FROM roller_coaster
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
List the names of roller coasters by ascending order of length.
assistant
SELECT Name FROM roller_coaster ORDER BY LENGTH ASC
human
How many countries do not have an roller coaster longer than 3000?
assistant
SELECT count(*) FROM country WHERE country_id NOT IN ( SELECT country_id FROM roller_coaster WHERE LENGTH > 3000 )
human
What are the lengths and heights of roller coasters?
assistant
SELECT LENGTH , Height FROM roller_coaster
human
How many roller coasters are there?
assistant
SELECT COUNT( * ) FROM roller_coaster ;