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
How many countries have roller coaster?
assistant
SELECT COUNT( distinct T1.Name ) FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID;
human
How many countries have more than one roller coaster?
assistant
SELECT COUNT( * ) FROM ( 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 names of the 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 number of roller coasters of Australia and Finland.
assistant
SELECT T1.Name , COUNT( * ) FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID = T2.Country_ID GROUP BY T1.Name HAVING COUNT( * ) > 1 ;