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 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 statuses of roller coasters longer than 3300 or higher than 100.
assistant
SELECT Status FROM roller_coaster WHERE LENGTH > 3300 OR Height > 100
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
List the status shared by more than two roller coaster.
assistant
SELECT Status FROM roller_coaster GROUP BY Status HAVING COUNT( * ) > 2 ;