CREATE TABLE continents (
    ContId NUMBER PRIMARY KEY,
    Continent TEXT
);
CREATE TABLE countries (
    CountryId NUMBER PRIMARY KEY,
    CountryName TEXT,
    Continent NUMBER,
    FOREIGN KEY ("Continent") REFERENCES continents("ContId")
);
CREATE TABLE car_makers (
    Id NUMBER PRIMARY KEY,
    Maker TEXT,
    FullName TEXT,
    Country TEXT,
    FOREIGN KEY ("Country") REFERENCES countries("CountryId")
);
CREATE TABLE model_list (
    ModelId NUMBER PRIMARY KEY,
    Maker NUMBER,
    Model TEXT,
    FOREIGN KEY ("Maker") REFERENCES car_makers("Id")
);
CREATE TABLE car_names (
    MakeId NUMBER PRIMARY KEY,
    Model TEXT,
    Make TEXT,
    FOREIGN KEY ("Model") REFERENCES model_list("Model")
);
CREATE TABLE cars_data (
    Id NUMBER PRIMARY KEY,
    MPG TEXT,
    Cylinders NUMBER,
    Edispl NUMBER,
    Horsepower TEXT,
    Weight NUMBER,
    Accelerate NUMBER,
    Year NUMBER,
    FOREIGN KEY ("Id") REFERENCES car_names("MakeId")
);

Question: What are all the makers and models?
SQL: SELECT Maker ,  Model FROM MODEL_LIST;

Question: What are the makers and models?
SQL: SELECT Maker ,  Model FROM MODEL_LIST;

Question: What are the countries having at least one car maker? List name and id.
SQL: SELECT T1.CountryName ,  T1.CountryId FROM COUNTRIES AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country GROUP BY T1.CountryId HAVING count(*)  >=  1;

Question: What are the names and ids of all countries with at least one car maker?
SQL: SELECT T1.CountryName ,  T1.CountryId FROM COUNTRIES AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country GROUP BY T1.CountryId HAVING count(*)  >=  1;

Question: What is the number of the cars with horsepower more than 150?
SQL: SELECT count(*) FROM CARS_DATA WHERE horsepower  >  150;

Question: What is the number of cars with a horsepower greater than 150?
SQL: SELECT count(*) FROM CARS_DATA WHERE horsepower  >  150;

Question: What is the average weight of cars each year?
SQL: SELECT avg(Weight) ,  YEAR FROM CARS_DATA GROUP BY YEAR;

Question: What is the average weight and year for each year?
SQL: SELECT avg(Weight) ,  YEAR FROM CARS_DATA GROUP BY YEAR;

Question: Which countries in europe have at least 3 car manufacturers?
SQL: SELECT T1.CountryName FROM COUNTRIES AS T1 JOIN CONTINENTS AS T2 ON T1.Continent  =  T2.ContId JOIN CAR_MAKERS AS T3 ON T1.CountryId  =  T3.Country WHERE T2.Continent  =  'europe' GROUP BY T1.CountryName HAVING count(*)  >=  3;

Question: What are the names of all European countries with at least 3 manufacturers?
SQL: SELECT T1.CountryName FROM COUNTRIES AS T1 JOIN CONTINENTS AS T2 ON T1.Continent  =  T2.ContId JOIN CAR_MAKERS AS T3 ON T1.CountryId  =  T3.Country WHERE T2.Continent  =  'europe' GROUP BY T1.CountryName HAVING count(*)  >=  3;

Question: What is the maximum horsepower and the make of the car models with 3 cylinders?
SQL: SELECT T2.horsepower ,  T1.Make FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.cylinders  =  3 ORDER BY T2.horsepower DESC LIMIT 1;

Question: What is the largest amount of horsepower for the models with 3 cylinders and what make is it?
SQL: SELECT T2.horsepower ,  T1.Make FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.cylinders  =  3 ORDER BY T2.horsepower DESC LIMIT 1;

Question: Which model saves the most gasoline? That is to say, have the maximum miles per gallon.
SQL: SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id ORDER BY T2.mpg DESC LIMIT 1;

Question: What is the car model with the highest mpg ?
SQL: select t1.model from car_names as t1 join cars_data as t2 on t1.makeid  =  t2.id order by t2.mpg desc limit 1;

Question: What is the average horsepower of the cars before 1980?
SQL: SELECT avg(horsepower) FROM CARS_DATA WHERE YEAR  <  1980;

Question: What is the average horsepower for all cars produced before 1980 ?
SQL: select avg(horsepower) from cars_data where year  <  1980;

Question: What is the average edispl of the cars of model volvo?
SQL: SELECT avg(T2.edispl) FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T1.Model  =  'volvo';

Question: What is the average edispl for all volvos?
SQL: SELECT avg(T2.edispl) FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T1.Model  =  'volvo';

Question: What is the maximum accelerate for different number of cylinders?
SQL: SELECT max(Accelerate) ,  Cylinders FROM CARS_DATA GROUP BY Cylinders;

Question: What is the maximum accelerate for all the different cylinders?
SQL: SELECT max(Accelerate) ,  Cylinders FROM CARS_DATA GROUP BY Cylinders;

Question: Which model has the most version(make) of cars?
SQL: SELECT Model FROM CAR_NAMES GROUP BY Model ORDER BY count(*) DESC LIMIT 1;

Question: What model has the most different versions?
SQL: SELECT Model FROM CAR_NAMES GROUP BY Model ORDER BY count(*) DESC LIMIT 1;

Question: How many cars have more than 4 cylinders?
SQL: SELECT count(*) FROM CARS_DATA WHERE Cylinders  >  4;

Question: What is the number of cars with more than 4 cylinders?
SQL: SELECT count(*) FROM CARS_DATA WHERE Cylinders  >  4;

Question: how many cars were produced in 1980?
SQL: SELECT count(*) FROM CARS_DATA WHERE YEAR  =  1980;

Question: In 1980, how many cars were made?
SQL: SELECT count(*) FROM CARS_DATA WHERE YEAR  =  1980;

Question: How many car models were produced by the maker with full name American Motor Company?
SQL: SELECT count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker WHERE T1.FullName  =  'American Motor Company';

Question: What is the number of car models created by the car maker American Motor Company?
SQL: SELECT count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker WHERE T1.FullName  =  'American Motor Company';

Question: Which makers designed more than 3 car models? List full name and the id.
SQL: SELECT T1.FullName ,  T1.Id FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id HAVING count(*)  >  3;

Question: What are the names and ids of all makers with more than 3 models?
SQL: SELECT T1.FullName ,  T1.Id FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id HAVING count(*)  >  3;

Question: Which distinctive models are produced by maker with the full name General Motors or weighing more than 3500?
SQL: SELECT DISTINCT T2.Model FROM CAR_NAMES AS T1 JOIN MODEL_LIST AS T2 ON T1.Model  =  T2.Model JOIN CAR_MAKERS AS T3 ON T2.Maker  =  T3.Id JOIN CARS_DATA AS T4 ON T1.MakeId  =  T4.Id WHERE T3.FullName  =  'General Motors' OR T4.weight  >  3500;

Question: What are the different models created by either the car maker General Motors or weighed more than 3500?
SQL: SELECT DISTINCT T2.Model FROM CAR_NAMES AS T1 JOIN MODEL_LIST AS T2 ON T1.Model  =  T2.Model JOIN CAR_MAKERS AS T3 ON T2.Maker  =  T3.Id JOIN CARS_DATA AS T4 ON T1.MakeId  =  T4.Id WHERE T3.FullName  =  'General Motors' OR T4.weight  >  3500;

Question: In which years cars were produced weighing no less than 3000 and no more than 4000 ?
SQL: select distinct year from cars_data where weight between 3000 and 4000;

Question: What are the different years in which there were cars produced that weighed less than 4000 and also cars that weighted more than 3000 ?
SQL: select distinct year from cars_data where weight between 3000 and 4000;

Question: What is the horsepower of the car with the largest accelerate?
SQL: SELECT T1.horsepower FROM CARS_DATA AS T1 ORDER BY T1.accelerate DESC LIMIT 1;

Question: What is the horsepower of the car with the greatest accelerate?
SQL: SELECT T1.horsepower FROM CARS_DATA AS T1 ORDER BY T1.accelerate DESC LIMIT 1;

Question: For model volvo, how many cylinders does the car with the least accelerate have?
SQL: SELECT T1.cylinders FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Model  =  'volvo' ORDER BY T1.accelerate ASC LIMIT 1;