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 different statuses and the numbers of roller coasters for each status.
assistant
SELECT Status , COUNT(*) FROM roller_coaster GROUP BY Status
human
Show the names of countries that have more than one roller coaster.
assistant
SELECT T1.Name FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID GROUP BY T1.Name HAVING COUNT(*) > 1
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
human
Show the names of roller coasters and names of country they are in.
assistant
SELECT T2.Name , T1.Name FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID
human
List the names of roller coasters by ascending order of length.
assistant
SELECT Name FROM roller_coaster ORDER BY LENGTH ASC ;