CREATE TABLE Countries (
	id 			INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	country_name   CHAR(200)
);

/*    create Customers table into the database */
CREATE TABLE Customers (
	id 			INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	company_name		CHAR(100),
	name		CHAR(100),
	address			CHAR(200),
	city			CHAR(50),
	states          CHAR(50),
	country_id      INTEGER
);  	


CREATE TABLE Categories (
	id 			INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name		CHAR(100),
	description		CHAR(200)
);  	




CREATE TABLE Products (
	id 			INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name		CHAR(100),
	description		CHAR(200),
	price			DOUBLE,
	category_id    INTEGER NOT NULL
);  	




/*    create Orders table into the database */
CREATE TABLE SaleOrder (
	id 			INTEGER	NOT NULL PRIMARY KEY AUTOINCREMENT,
	
	reference_number  INTEGER NOT NULL,
	customer_id		INTEGER NOT NULL,
	total_cost		DOUBLE
);  	
	




/* create the table storing details of an order */
CREATE TABLE SaleOrderDetail (
	id		INTEGER	NOT NULL PRIMARY KEY AUTOINCREMENT,
	
	saleorder_id	INT NOT NULL,
	product_id	INTEGER NOT NULL,
	Quatity		INT NOT NULL
);


INSERT INTO Countries (country_name)
VALUES ('United States');

INSERT INTO Countries (country_name)
VALUES ('Germany');

INSERT INTO Countries (country_name)
VALUES ('France');

INSERT INTO Countries (country_name)
VALUES ('Italy');

INSERT INTO Countries (country_name)
VALUES ('England');

INSERT INTO Countries (country_name)
VALUES ('Mexico');

INSERT INTO Countries (country_name)
VALUES ('Japan');

INSERT INTO Countries (country_name)
VALUES ('China');

INSERT INTO Countries (country_name)
VALUES ('Vietnam');


/*
----------------------------------------------------------------
This script shows how to fill tables with data using
	INSERT sql statements
----------------------------------------------------------------	
*/
INSERT INTO Customers (company_name, name, address, city)
VALUES ('Alfreds Futterkiste',  'Maria Anders',  'Obere Str. 57',  'Berlin');


INSERT INTO Customers (company_name, name, address, city)
VALUES ('Berglunds snabbköp', 'Christina Berglund',  'Berguvsvägen 8',  'Luleå');


INSERT INTO Customers (company_name, name, address, city)
VALUES ('Centro comercial Moctezuma', 'Francisco Chang',  'Sierras de Granada 9993',  'México D.F.');


INSERT INTO Customers (company_name, name, address, city)
VALUES ('Ernst Handel',  'Roland Mendel',  'Kirchgasse 6',  'Graz');



/* insert some data into Productss table */
INSERT INTO Categories (name, description)
VALUES ('Desktop/Laptop', null);

INSERT INTO Categories (name, description)
VALUES ('Accessories', null);


/* insert some data into Productss table */
INSERT INTO Products (name, price, category_id)
VALUES ('HP Desktop', 1000, 1);

INSERT INTO Products (name, price, category_id)
VALUES ('Sony Laptop', 1800, 1);

INSERT INTO Products (name, price, category_id)
VALUES ('Cisco Router', 200, 2);

INSERT INTO Products (name, price, category_id)
VALUES ('ATI Video Card', 300, 2);