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: For a volvo model, how many cylinders does the version with 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;

Question: How many cars have a larger accelerate than the car with the largest horsepower?
SQL: SELECT COUNT(*) FROM CARS_DATA WHERE Accelerate  >  ( SELECT Accelerate FROM CARS_DATA ORDER BY Horsepower DESC LIMIT 1 );

Question: What is the number of cars with a greater accelerate than the one with the most horsepower?
SQL: SELECT COUNT(*) FROM CARS_DATA WHERE Accelerate  >  ( SELECT Accelerate FROM CARS_DATA ORDER BY Horsepower DESC LIMIT 1 );

Question: How many countries has more than 2 car makers ?
SQL: select count(*) from countries as t1 join car_makers as t2 on t1.countryid  =  t2.country group by t1.countryid having count(*)  >  2

Question: What is the number of countries with more than 2 car makers ?
SQL: select count(*) from countries as t1 join car_makers as t2 on t1.countryid  =  t2.country group by t1.countryid having count(*)  >  2

Question: How many cars has over 6 cylinders?
SQL: SELECT COUNT(*) FROM CARS_DATA WHERE Cylinders  >  6;

Question: What is the number of carsw ith over 6 cylinders?
SQL: SELECT COUNT(*) FROM CARS_DATA WHERE Cylinders  >  6;

Question: For the cars with 4 cylinders, which model has the largest horsepower?
SQL: SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Cylinders  =  4 ORDER BY T2.horsepower DESC LIMIT 1;

Question: For all of the 4 cylinder cars, which model has the most horsepower?
SQL: SELECT T1.Model FROM CAR_NAMES AS T1 JOIN CARS_DATA AS T2 ON T1.MakeId  =  T2.Id WHERE T2.Cylinders  =  4 ORDER BY T2.horsepower DESC LIMIT 1;

Question: Among the cars with more than lowest horsepower, which ones do not have more than 3 cylinders? List the car makeid and make name.
SQL: SELECT T2.MakeId ,  T2.Make FROM CARS_DATA AS T1 JOIN CAR_NAMES AS T2 ON T1.Id  =  T2.MakeId WHERE T1.Horsepower  >  (SELECT min(Horsepower) FROM CARS_DATA) AND T1.Cylinders  <=  3;

Question: Among the cars that do not have the minimum horsepower , what are the make ids and names of all those with less than 4 cylinders ?
SQL: select t2.makeid ,  t2.make from cars_data as t1 join car_names as t2 on t1.id  =  t2.makeid where t1.horsepower  >  (select min(horsepower) from cars_data) and t1.cylinders  <  4;

Question: What is the maximum miles per gallon of the car with 8 cylinders or produced before 1980 ?
SQL: select max(mpg) from cars_data where cylinders  =  8 or year  <  1980

Question: What is the maximum mpg of the cars that had 8 cylinders or that were produced before 1980 ?
SQL: select max(mpg) from cars_data where cylinders  =  8 or year  <  1980

Question: Which models are lighter than 3500 but not built by the 'Ford Motor Company'?
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 JOIN CAR_MAKERS AS T4 ON T1.Maker  =  T4.Id WHERE T3.weight  <  3500 AND T4.FullName != 'Ford Motor Company';

Question: What are the different models wthat are lighter than 3500 but were not built by the Ford Motor Company?
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 JOIN CAR_MAKERS AS T4 ON T1.Maker  =  T4.Id WHERE T3.weight  <  3500 AND T4.FullName != 'Ford Motor Company';

Question: What are the name of the countries where there is not a single car maker?
SQL: SELECT CountryName FROM countries EXCEPT SELECT T1.CountryName FROM countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.countryId  =  T2.Country;

Question: What are the names of the countries with no car makers?
SQL: SELECT CountryName FROM countries EXCEPT SELECT T1.CountryName FROM countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.countryId  =  T2.Country;

Question: Which are the car makers which produce at least 2 models and more than 3 car makers ? List the id and the maker .
SQL: select t1.id ,  t1.maker from car_makers as t1 join model_list as t2 on t1.id  =  t2.maker group by t1.id having count(*)  >=  2 intersect select t1.id ,  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 group by t1.id having count(*)  >  3;

Question: What are the ids and makers of all car makers that produce at least 2 models and make more than 3 cars?
SQL: SELECT T1.Id ,  T1.Maker FROM CAR_MAKERS AS T1 JOIN MODEL_LIST AS T2 ON T1.Id  =  T2.Maker GROUP BY T1.Id HAVING count(*)  >=  2 INTERSECT SELECT T1.Id ,  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 GROUP BY T1.Id HAVING count(*)  >  3;

Question: What are the id and names of the countries which have more than 3 car makers or produce the 'fiat' model?
SQL: SELECT T1.countryId ,  T1.CountryName FROM Countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country GROUP BY T1.countryId HAVING count(*)  >  3 UNION SELECT T1.countryId ,  T1.CountryName FROM Countries AS T1 JOIN CAR_MAKERS AS T2 ON T1.CountryId  =  T2.Country JOIN MODEL_LIST AS T3 ON T2.Id  =  T3.Maker WHERE T3.Model  =  'fiat';

Question: What are the ids and names of all countries that either have more than 3 car makers or produce fiat model ?
SQL: select t1.countryid ,  t1.countryname from countries as t1 join car_makers as t2 on t1.countryid  =  t2.country group by t1.countryid having count(*)  >  3 union select t1.countryid ,  t1.countryname from countries as t1 join car_makers as t2 on t1.countryid  =  t2.country join model_list as t3 on t2.id  =  t3.maker where t3.model  =  'fiat';