Database: roller_coaster

CREATE TABLE "roller_coaster" (
"Roller_Coaster_ID" int,
"Name" text,
"Park" text,
"Country_ID" int,
"Length" real,
"Height" real,
"Speed" text,
"Opened" text,
"Status" text,
PRIMARY KEY ("Roller_Coaster_ID"),
FOREIGN KEY ("Country_ID") REFERENCES `country`("Country_ID")
);
CREATE TABLE "country" (
"Country_ID" int,
"Name" text,
"Population" int,
"Area" int,
"Languages" text,
PRIMARY KEY ("Country_ID")
);

Question: How many roller coasters are there?
SQL: SELECT count(*) FROM roller_coaster

Question: List the names of roller coasters by ascending order of length.
SQL: SELECT Name FROM roller_coaster ORDER BY LENGTH ASC

Question: What are the lengths and heights of roller coasters?
SQL: SELECT LENGTH ,  Height FROM roller_coaster

Question: List the names of countries whose language is not "German".
SQL: SELECT Name FROM country WHERE Languages != "German"

Question: Show the statuses of roller coasters longer than 3300 or higher than 100.
SQL: SELECT Status FROM roller_coaster WHERE LENGTH  >  3300 OR Height  >  100

Question: What are the speeds of the longest roller coaster?
SQL: SELECT Speed FROM roller_coaster ORDER BY LENGTH DESC LIMIT 1

Question: What is the average speed of roller coasters?
SQL: SELECT avg(Speed) FROM roller_coaster

Question: Show the different statuses and the numbers of roller coasters for each status.
SQL: SELECT Status ,  COUNT(*) FROM roller_coaster GROUP BY Status

Question: Please show the most common status of roller coasters.
SQL: SELECT Status FROM roller_coaster GROUP BY Status ORDER BY COUNT(*) DESC LIMIT 1

Question: List the status shared by more than two roller coaster.
SQL: SELECT Status FROM roller_coaster GROUP BY Status HAVING COUNT(*)  >  2

Question: Show the park of the roller coaster with the highest speed.
SQL: SELECT Park FROM roller_coaster ORDER BY Speed DESC LIMIT 1

Question: Show the names of roller coasters and names of country they are in.
SQL: SELECT T2.Name ,  T1.Name FROM country AS T1 JOIN roller_coaster AS T2 ON T1.Country_ID  =  T2.Country_ID

Question: Show the names of countries that have more than one roller coaster.
SQL: 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

Question: Show the name and population of the country that has the highest roller coaster.
SQL: 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

Question: Show the names of countries and the average speed of roller coasters from each country.
SQL: 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

Question: How many countries do not have an roller coaster longer than 3000?
SQL: SELECT count(*) FROM country WHERE country_id NOT IN ( SELECT country_id FROM roller_coaster WHERE LENGTH  >  3000 )

Question: What are the country names, area and population which has both roller coasters with speed higher
SQL: 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