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
List the names of roller coasters by ascending order of length.
assistant
SELECT Name FROM roller_coaster ORDER BY LENGTH ASC
human
What are the country names, area and population which has both roller coasters with speed higher
assistant
SELECT T1.name , T1.area , T1.population FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID WHERE T2.speed > 60 INTERSECT SELECT T1.name , T1.area , T1.population FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID WHERE T2.speed < 55
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
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
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
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 ;