
CREATE TABLE "Manufacturers" (
	"Code" INTEGER, 
	"Name" VARCHAR(255) NOT NULL, 
	"Headquarter" VARCHAR(255) NOT NULL, 
	"Founder" VARCHAR(255) NOT NULL, 
	"Revenue" REAL, 
	PRIMARY KEY ("Code")
)


CREATE TABLE "Products" (
	"Code" INTEGER, 
	"Name" VARCHAR(255) NOT NULL, 
	"Price" DECIMAL NOT NULL, 
	"Manufacturer" INTEGER NOT NULL, 
	PRIMARY KEY ("Code"), 
	FOREIGN KEY("Manufacturer") REFERENCES "Manufacturers" ("Code")
)

CREATE TABLE "Manufacturers" (
	"Code" INTEGER, 
	"Name" VARCHAR(255) NOT NULL, 
	"Headquarter" VARCHAR(255) NOT NULL, 
	"Founder" VARCHAR(255) NOT NULL, 
	"Revenue" REAL, 
	PRIMARY KEY ("Code")
)

/*
2 rows from Manufacturers table:
Code	Name	Headquarter	Founder	Revenue
1	Sony	Tokyo	Andy	120.0
2	Creative Labs	Austin	Owen	100.0
*/


CREATE TABLE "Products" (
	"Code" INTEGER, 
	"Name" VARCHAR(255) NOT NULL, 
	"Price" DECIMAL NOT NULL, 
	"Manufacturer" INTEGER NOT NULL, 
	PRIMARY KEY ("Code"), 
	FOREIGN KEY("Manufacturer") REFERENCES "Manufacturers" ("Code")
)

/*
2 rows from Products table:
Code	Name	Price	Manufacturer
1	Hard drive	240.0000000000	5
2	Memory	120.0000000000	6
*/
Select the code of the product that is cheapest in each product category.
SELECT code ,  name ,  min(price) FROM products GROUP BY name
[{'Code': 7, 'Name': 'CD drive', 'min(price)': 90}, {'Code': 10, 'Name': 'DVD burner', 'min(price)': 180}, {'Code': 11, 'Name': 'DVD drive', 'min(price)': 150}, {'Code': 4, 'Name': 'Floppy disk', 'min(price)': 5}, {'Code': 1, 'Name': 'Hard drive', 'min(price)': 240}, {'Code': 2, 'Name': 'Memory', 'min(price)': 120}, {'Code': 5, 'Name': 'Monitor', 'min(price)': 240}, {'Code': 8, 'Name': 'Printer', 'min(price)': 270}, {'Code': 9, 'Name': 'Toner cartridge', 'min(price)': 66}, {'Code': 3, 'Name': 'ZIP drive', 'min(price)': 150}]