
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
*/
How many car models are produced by each maker ? Only list the count and the maker full name .
SELECT count(*) ,  t2.fullname FROM model_list AS t1 JOIN car_makers AS t2 ON t1.maker  =  t2.id GROUP BY t2.id;
[{'count(*)': 1, 'FullName': 'American Motor Company'}, {'count(*)': 2, 'FullName': 'Volkswagen'}, {'count(*)': 1, 'FullName': 'BMW'}, {'count(*)': 5, 'FullName': 'General Motors'}, {'count(*)': 3, 'FullName': 'Ford Motor Company'}, {'count(*)': 4, 'FullName': 'Chrysler'}, {'count(*)': 1, 'FullName': 'Citroen'}, {'count(*)': 2, 'FullName': 'Nissan Motors'}, {'count(*)': 1, 'FullName': 'Fiat'}, {'count(*)': 1, 'FullName': 'Honda'}, {'count(*)': 1, 'FullName': 'Mazda'}, {'count(*)': 2, 'FullName': 'Daimler Benz'}, {'count(*)': 1, 'FullName': 'Opel'}, {'count(*)': 1, 'FullName': 'Peugeaut'}, {'count(*)': 1, 'FullName': 'Renault'}, {'count(*)': 1, 'FullName': 'Saab'}, {'count(*)': 1, 'FullName': 'Subaru'}, {'count(*)': 2, 'FullName': 'Toyota'}, {'count(*)': 1, 'FullName': 'Triumph'}, {'count(*)': 1, 'FullName': 'Volvo'}, {'count(*)': 1, 'FullName': 'Kia Motors'}, {'count(*)': 1, 'FullName': 'Hyundai'}]