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: How many continents are there?
SQL: SELECT count(*) FROM CONTINENTS;

Question: What is the number of continents?
SQL: SELECT count(*) FROM CONTINENTS;

Question: How many countries does each continent have? List the continent id, continent name and the number of countries.
SQL: SELECT T1.ContId ,  T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.Continent GROUP BY T1.ContId;

Question: For each continent, list its id, name, and how many countries it has?
SQL: SELECT T1.ContId ,  T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.Continent GROUP BY T1.ContId;

Question: How many countries are listed?
SQL: SELECT count(*) FROM COUNTRIES;

Question: How many countries exist?
SQL: SELECT count(*) FROM COUNTRIES;

Question: How many models does each car maker produce? List maker full name, id and the number.
SQL: SELECT T1.FullName ,  T1.Id ,  count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id;

Question: What is the full name of each car maker, along with its id and how many models it produces?
SQL: SELECT T1.FullName ,  T1.Id ,  count(*) FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id;

Question: Which model of the car has the minimum horsepower?
SQL: SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id ORDER BY T2.horsepower ASC LIMIT 1;

Question: What is the model of the car with the smallest amount of horsepower?
SQL: SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id ORDER BY T2.horsepower ASC LIMIT 1;

Question: Find the model of the car whose weight is below the average weight.
SQL: SELECT T1.model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Weight  <  (SELECT avg(Weight) FROM CARS_DATA)

Question: What is the model for the car with a weight smaller than the average?
SQL: SELECT T1.model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Weight  <  (SELECT avg(Weight) FROM CARS_DATA)

Question: Find the name of the makers that produced some cars in the year of 1970?
SQL: SELECT DISTINCT T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker JOIN CAR_NAMES AS T3 ON T2.model  =  T3.model JOIN CARS_DATA AS T4 ON T3.MakeId  =  T4.id WHERE T4.year  =  '1970';

Question: What is the name of the different car makers who produced a car in 1970?
SQL: SELECT DISTINCT T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker JOIN CAR_NAMES AS T3 ON T2.model  =  T3.model JOIN CARS_DATA AS T4 ON T3.MakeId  =  T4.id WHERE T4.year  =  '1970';

Question: Find the make and production time of the cars that were produced in the earliest year?
SQL: SELECT T2.Make ,  T1.Year FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T1.Year  =  (SELECT min(YEAR) FROM CARS_DATA);

Question: What is the maker of the carr produced in the earliest year and what year was it?
SQL: SELECT T2.Make ,  T1.Year FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T1.Year  =  (SELECT min(YEAR) FROM CARS_DATA);

Question: Which distinct car models are the produced after 1980?
SQL: SELECT DISTINCT T1.model FROM MODEL_LIST AS T1 JOIN CAR_NAMES AS T2 ON T1.model  =  T2.model JOIN CARS_DATA AS T3 ON T2.MakeId  =  T3.id WHERE T3.year  >  1980;

Question: What are the different models for the cards produced after 1980?
SQL: SELECT DISTINCT T1.model FROM MODEL_LIST AS T1 JOIN CAR_NAMES AS T2 ON T1.model  =  T2.model JOIN CARS_DATA AS T3 ON T2.MakeId  =  T3.id WHERE T3.year  >  1980;

Question: How many car makers are there in each continents? List the continent name and the count.
SQL: SELECT T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.continent JOIN car_makers AS T3 ON T2.CountryId  =  T3.Country GROUP BY T1.Continent;

Question: What is the name of each continent and how many car makers are there in each one?
SQL: SELECT T1.Continent ,  count(*) FROM CONTINENTS AS T1 JOIN COUNTRIES AS T2 ON T1.ContId  =  T2.continent JOIN car_makers AS T3 ON T2.CountryId  =  T3.Country GROUP BY T1.Continent;

Question: Which of the countries has the most car makers? List the country name.
SQL: SELECT T2.CountryName FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId GROUP BY T1.Country ORDER BY Count(*) DESC LIMIT 1;

Question: What is the name of the country with the most car makers?
SQL: SELECT T2.CountryName FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId GROUP BY T1.Country ORDER BY Count(*) DESC LIMIT 1;

Question: How many car models are produced by each maker ? Only list the count and the maker full name .
SQL: select count(*) ,  t2.fullname from model_list as t1 join car_makers as t2 on t1.maker  =  t2.id group by t2.id;

Question: What is the number of car models that are produced by each maker and what is the id and full name of each maker?
SQL: SELECT Count(*) ,  T2.FullName ,  T2.id FROM MODEL_LIST AS T1 JOIN CAR_MAKERS AS T2 ON T1.Maker  =  T2.Id GROUP BY T2.id;

Question: What is the accelerate of the car make amc hornet sportabout (sw)?
SQL: SELECT T1.Accelerate FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Make  =  'amc hornet sportabout (sw)';

Question: How much does the car accelerate that makes amc hornet sportabout (sw)?
SQL: SELECT T1.Accelerate FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T2.Make  =  'amc hornet sportabout (sw)';

Question: How many car makers are there in france?
SQL: SELECT count(*) FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId WHERE T2.CountryName  =  'france';

Question: What is the number of makers of care in France?
SQL: SELECT count(*) FROM CAR_MAKERS AS T1 JOIN COUNTRIES AS T2 ON T1.Country  =  T2.CountryId WHERE T2.CountryName  =  'france';

Question: How many car models are produced in the usa?
SQL: SELECT count(*) FROM MODEL_LIST AS T1 JOIN CAR_MAKERS AS T2 ON T1.Maker  =  T2.Id JOIN COUNTRIES AS T3 ON T2.Country  =  T3.CountryId WHERE T3.CountryName  =  'usa';

Question: What is the count of the car models produced in the United States?
SQL: SELECT count(*) FROM MODEL_LIST AS T1 JOIN CAR_MAKERS AS T2 ON T1.Maker  =  T2.Id JOIN COUNTRIES AS T3 ON T2.Country  =  T3.CountryId WHERE T3.CountryName  =  'usa';

Question: What is the average miles per gallon(mpg) of the cars with 4 cylinders?
SQL: SELECT avg(mpg) FROM CARS_DATA WHERE Cylinders  =  4;

Question: What is the average miles per gallon of all the cards with 4 cylinders?
SQL: SELECT avg(mpg) FROM CARS_DATA WHERE Cylinders  =  4;

Question: What is the smallest weight of the car produced with 8 cylinders on 1974 ?
SQL: select min(weight) from cars_data where cylinders  =  8 and year  =  1974

Question: What is the minimum weight of the car with 8 cylinders produced in 1974 ?
SQL: select min(weight) from cars_data where cylinders  =  8 and year  =  1974