--- computer_store.sql
---
--- from https://en.wikibooks.org/wiki/SQL_Exercises/The_computer_store
---
--- An example of a simple SQL database.
---
--- In practice, we'd usually use a language like python to loop over
--- the data rows to insert them into the database, using a SQL library.
---
--- To work with this example on the command line, first make sure that sqlite3
--- is installed. (On a unix system, "apt install sqlite3" as root. On a mac,
--- "brew install sqlite3". Or go to sqlite.org and follow their install instructions.)
--- It's installed on jupypter.bennington and sandyshell.)
---
--- You can run it interactively from the command line.
---
---   $ sqlite3
---   SQLITE ...
---   sqlite> 
---
--- To create the computer_store.db database :
---   $ sqlite3 computer_store.db < computer_store.sql
---
--- Then to work with that database :
---   $ sqlite3 computer_store.db
---   sqlite> SELECT Code FROM Manufacurers WHERE Name="Winchester";
---   6
---

CREATE TABLE Manufacturers (
	Code INTEGER PRIMARY KEY NOT NULL,
	Name CHAR(50) NOT NULL 
);

CREATE TABLE Products (
	Code INTEGER PRIMARY KEY NOT NULL,
	Name CHAR(50) NOT NULL ,
	Price REAL NOT NULL ,
	Manufacturer INTEGER NOT NULL 
		CONSTRAINT fk_Manufacturers_Code REFERENCES Manufacturers(Code)
);

INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');
INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');
INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');
INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');
INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');
INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');
INSERT INTO Manufacturers(Code,Name) VALUES(7,'Bose');

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);