DROP TABLE orders;
DROP TABLE product;
DROP TABLE buyer;

DROP TABLE Company;

CREATE TABLE Company (
	CName VARCHAR(40) NOT NULL,
	StockPrice INTEGER NOT NULL,
    Country VARCHAR(40) NOT NULL
);

ALTER TABLE Company
	ADD CONSTRAINT PK_Company
		PRIMARY KEY (CName);

CREATE TABLE Product (
	PName VARCHAR(40) NOT NULL,
    Price DECIMAL(12,2) NOT NULL,
	Category VARCHAR(40) NOT NULL,
	Manufacturer VARCHAR(40) NOT NULL
);

ALTER TABLE Product
	ADD CONSTRAINT PK_Product
		PRIMARY KEY (PName);

ALTER TABLE Product
	ADD FOREIGN KEY (Manufacturer) REFERENCES Company(CName);

INSERT INTO Company VALUES ('GizmoWorks',25,'USA');
INSERT INTO Company VALUES ('Canon',65,'Japan');
INSERT INTO Company VALUES ('Hitachi',15,'Japan');

SELECT * FROM Company;

INSERT INTO Product VALUES ('Gizmo',19.99,'Gadgets','GizmoWorks');
INSERT INTO Product VALUES ('Powergizmo',29.99,'Gadgets','GizmoWorks');
INSERT INTO Product VALUES ('SingleTouch',149.99,'Photography','Canon');
INSERT INTO Product VALUES ('MultiTouch',203.99,'Household','Hitachi');

SELECT * FROM Product;

SELECT PName, Price
FROM   Product, Company
WHERE  Manufacturer = CName
AND Country='Japan'
AND Price <= 200;


SELECT PName, Price
FROM   Product
JOIN   Company ON Manufacturer = Cname AND Country='Japan'
WHERE  Price <= 200;

SELECT Country
FROM   Product,Company
WHERE  Manufacturer=CName
AND Category='Gadgets';

SELECT DISTINCT Country
FROM   Product,Company
WHERE  Manufacturer=CName
AND Category='Gadgets';