
CREATE TABLE car_makers (
	"Id" INTEGER, 
	"Maker" TEXT, 
	"FullName" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY ("Id"), 
	FOREIGN KEY("Country") REFERENCES countries ("CountryId")
)


CREATE TABLE car_names (
	"MakeId" INTEGER, 
	"Model" TEXT, 
	"Make" TEXT, 
	PRIMARY KEY ("MakeId"), 
	FOREIGN KEY("Model") REFERENCES model_list ("Model")
)


CREATE TABLE cars_data (
	"Id" INTEGER, 
	"MPG" TEXT, 
	"Cylinders" INTEGER, 
	"Edispl" REAL, 
	"Horsepower" TEXT, 
	"Weight" INTEGER, 
	"Accelerate" REAL, 
	"Year" INTEGER, 
	PRIMARY KEY ("Id"), 
	FOREIGN KEY("Id") REFERENCES car_names ("MakeId")
)


CREATE TABLE continents (
	"ContId" INTEGER, 
	"Continent" TEXT, 
	PRIMARY KEY ("ContId")
)


CREATE TABLE countries (
	"CountryId" INTEGER, 
	"CountryName" TEXT, 
	"Continent" INTEGER, 
	PRIMARY KEY ("CountryId"), 
	FOREIGN KEY("Continent") REFERENCES continents ("ContId")
)


CREATE TABLE model_list (
	"ModelId" INTEGER, 
	"Maker" INTEGER, 
	"Model" TEXT, 
	PRIMARY KEY ("ModelId"), 
	FOREIGN KEY("Maker") REFERENCES car_makers ("Id"), 
	UNIQUE ("Model")
)

CREATE TABLE car_makers (
	"Id" INTEGER, 
	"Maker" TEXT, 
	"FullName" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY ("Id"), 
	FOREIGN KEY("Country") REFERENCES countries ("CountryId")
)

/*
2 rows from car_makers table:
Id	Maker	FullName	Country
1	amc	American Motor Company	1
2	volkswagen	Volkswagen	2
*/


CREATE TABLE car_names (
	"MakeId" INTEGER, 
	"Model" TEXT, 
	"Make" TEXT, 
	PRIMARY KEY ("MakeId"), 
	FOREIGN KEY("Model") REFERENCES model_list ("Model")
)

/*
2 rows from car_names table:
MakeId	Model	Make
1	chevrolet	chevrolet chevelle malibu
2	buick	buick skylark 320
*/


CREATE TABLE cars_data (
	"Id" INTEGER, 
	"MPG" TEXT, 
	"Cylinders" INTEGER, 
	"Edispl" REAL, 
	"Horsepower" TEXT, 
	"Weight" INTEGER, 
	"Accelerate" REAL, 
	"Year" INTEGER, 
	PRIMARY KEY ("Id"), 
	FOREIGN KEY("Id") REFERENCES car_names ("MakeId")
)

/*
2 rows from cars_data table:
Id	MPG	Cylinders	Edispl	Horsepower	Weight	Accelerate	Year
1	18	8	307.0	130	3504	12.0	1970
2	15	8	350.0	165	3693	11.5	1970
*/


CREATE TABLE continents (
	"ContId" INTEGER, 
	"Continent" TEXT, 
	PRIMARY KEY ("ContId")
)

/*
2 rows from continents table:
ContId	Continent
1	america
2	europe
*/


CREATE TABLE countries (
	"CountryId" INTEGER, 
	"CountryName" TEXT, 
	"Continent" INTEGER, 
	PRIMARY KEY ("CountryId"), 
	FOREIGN KEY("Continent") REFERENCES continents ("ContId")
)

/*
2 rows from countries table:
CountryId	CountryName	Continent
1	usa	1
2	germany	2
*/


CREATE TABLE model_list (
	"ModelId" INTEGER, 
	"Maker" INTEGER, 
	"Model" TEXT, 
	PRIMARY KEY ("ModelId"), 
	FOREIGN KEY("Maker") REFERENCES car_makers ("Id"), 
	UNIQUE ("Model")
)

/*
2 rows from model_list table:
ModelId	Maker	Model
1	1	amc
2	2	audi
*/
What is the model for the car with a weight smaller than the average?
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)
[{'Model': 'toyota'}, {'Model': 'plymouth'}, {'Model': 'amc'}, {'Model': 'ford'}, {'Model': 'datsun'}, {'Model': 'volkswagen'}, {'Model': 'peugeot'}, {'Model': 'audi'}, {'Model': 'saab'}, {'Model': 'bmw'}, {'Model': 'amc'}, {'Model': 'datsun'}, {'Model': 'chevrolet'}, {'Model': 'toyota'}, {'Model': 'ford'}, {'Model': 'volkswagen'}, {'Model': 'amc'}, {'Model': 'amc'}, {'Model': 'chevrolet'}, {'Model': 'mercury'}, {'Model': 'opel'}, {'Model': 'peugeot'}, {'Model': 'fiat'}, {'Model': 'toyota'}, {'Model': 'datsun'}, {'Model': 'volkswagen'}, {'Model': 'plymouth'}, {'Model': 'toyota'}, {'Model': 'dodge'}, {'Model': 'volkswagen'}, {'Model': 'chevrolet'}, {'Model': 'ford'}, {'Model': 'mazda'}, {'Model': 'volvo'}, {'Model': 'volkswagen'}, {'Model': 'peugeot'}, {'Model': 'renault'}, {'Model': 'ford'}, {'Model': 'datsun'}, {'Model': 'toyota'}, {'Model': 'dodge'}, {'Model': 'toyota'}, {'Model': 'amc'}, {'Model': 'plymouth'}, {'Model': 'volkswagen'}, {'Model': 'amc'}, {'Model': 'toyota'}, {'Model': 'chevrolet'}, {'Model': 'datsun'}, {'Model': 'mazda'}, {'Model': 'ford'}, {'Model': 'mercury'}, {'Model': 'fiat'}, {'Model': 'fiat'}, {'Model': 'opel'}, {'Model': 'audi'}, {'Model': 'volvo'}, {'Model': 'saab'}, {'Model': 'toyota'}, {'Model': 'ford'}, {'Model': 'amc'}, {'Model': 'datsun'}, {'Model': 'ford'}, {'Model': 'toyota'}, {'Model': 'chevrolet'}, {'Model': 'audi'}, {'Model': 'volkswagen'}, {'Model': 'opel'}, {'Model': 'toyota'}, {'Model': 'datsun'}, {'Model': 'dodge'}, {'Model': 'fiat'}, {'Model': 'fiat'}, {'Model': 'honda'}, {'Model': 'subaru'}, {'Model': 'fiat'}, {'Model': 'toyota'}, {'Model': 'ford'}, {'Model': 'amc'}, {'Model': 'pontiac'}, {'Model': 'toyota'}, {'Model': 'volkswagen'}, {'Model': 'datsun'}, {'Model': 'volkswagen'}, {'Model': 'audi'}, {'Model': 'peugeot'}, {'Model': 'volvo'}, {'Model': 'saab'}, {'Model': 'honda'}, {'Model': 'fiat'}, {'Model': 'opel'}, {'Model': 'capri'}, {'Model': 'dodge'}, {'Model': 'renault'}, {'Model': 'chevrolet'}, {'Model': 'chevrolet'}, {'Model': 'volkswagen'}, {'Model': 'honda'}, {'Model': 'volkswagen'}, {'Model': 'datsun'}, {'Model': 'toyota'}, {'Model': 'ford'}, {'Model': 'toyota'}, {'Model': 'honda'}, {'Model': 'buick'}, {'Model': 'renault'}, {'Model': 'plymouth'}, {'Model': 'datsun'}, {'Model': 'volkswagen'}, {'Model': 'pontiac'}, {'Model': 'toyota'}, {'Model': 'ford'}, {'Model': 'chevrolet'}, {'Model': 'dodge'}, {'Model': 'subaru'}, {'Model': 'volkswagen'}, {'Model': 'datsun'}, {'Model': 'bmw'}, {'Model': 'mazda'}, {'Model': 'volkswagen'}, {'Model': 'ford'}, {'Model': 'mazda'}, {'Model': 'datsun'}, {'Model': 'honda'}, {'Model': 'ford'}, {'Model': 'ford'}, {'Model': 'chevrolet'}, {'Model': 'toyota'}, {'Model': 'datsun'}, {'Model': 'dodge'}, {'Model': 'toyota'}, {'Model': 'plymouth'}, {'Model': 'oldsmobile'}, {'Model': 'datsun'}, {'Model': 'audi'}, {'Model': 'saab'}, {'Model': 'volkswagen'}, {'Model': 'honda'}, {'Model': 'ford'}, {'Model': 'volkswagen'}, {'Model': 'mazda'}, {'Model': 'dodge'}, {'Model': 'amc'}, {'Model': 'plymouth'}, {'Model': 'plymouth'}, {'Model': 'datsun'}, {'Model': 'fiat'}, {'Model': 'buick'}, {'Model': 'chevrolet'}, {'Model': 'oldsmobile'}, {'Model': 'pontiac'}, {'Model': 'volkswagen'}, {'Model': 'toyota'}, {'Model': 'chevrolet'}, {'Model': 'datsun'}, {'Model': 'chevrolet'}, {'Model': 'ford'}, {'Model': 'audi'}, {'Model': 'toyota'}, {'Model': 'mazda'}, {'Model': 'datsun'}, {'Model': 'toyota'}, {'Model': 'mazda'}, {'Model': 'dodge'}, {'Model': 'datsun'}, {'Model': 'volkswagen'}, {'Model': 'volkswagen'}, {'Model': 'audi'}, {'Model': 'honda'}, {'Model': 'renault'}, {'Model': 'subaru'}, {'Model': ' volkswagen'}, {'Model': 'datsun'}, {'Model': 'mazda'}, {'Model': 'triumph'}, {'Model': 'ford'}, {'Model': 'honda'}, {'Model': 'plymouth'}, {'Model': 'buick'}, {'Model': 'dodge'}, {'Model': 'chevrolet'}, {'Model': 'plymouth'}, {'Model': 'toyota'}, {'Model': 'plymouth'}, {'Model': 'honda'}, {'Model': 'subaru'}, {'Model': 'datsun'}, {'Model': 'toyota'}, {'Model': 'mazda'}, {'Model': 'plymouth'}, {'Model': 'ford'}, {'Model': 'ford'}, {'Model': 'volkswagen'}, {'Model': 'renault'}, {'Model': 'honda'}, {'Model': 'toyota'}, {'Model': 'datsun'}, {'Model': 'mazda'}, {'Model': 'saab'}, {'Model': 'toyota'}, {'Model': 'datsun'}, {'Model': 'chevrolet'}, {'Model': 'chevrolet'}, {'Model': 'chevrolet'}, {'Model': 'pontiac'}, {'Model': 'dodge'}, {'Model': 'pontiac'}, {'Model': 'ford'}, {'Model': 'volkswagen'}, {'Model': 'mazda'}, {'Model': 'mazda'}, {'Model': 'plymouth'}, {'Model': 'mercury'}, {'Model': 'nissan'}, {'Model': 'honda'}, {'Model': 'toyota'}, {'Model': 'honda'}, {'Model': 'honda'}, {'Model': 'datsun'}, {'Model': 'buick'}, {'Model': 'chrysler'}, {'Model': 'ford'}, {'Model': 'toyota'}, {'Model': 'dodge'}, {'Model': 'chevrolet'}, {'Model': 'ford'}, {'Model': 'volkswagen'}, {'Model': 'dodge'}, {'Model': 'ford'}, {'Model': 'chevrolet'}]