CREATE TABLE Customers
(
CustomerID NUMBER(10) PRIMARY KEY,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
City VARCHAR2(50),
State CHAR(2),
Zip VARCHAR2(10)
);

-- Generate ID using sequence and trigger
CREATE SEQUENCE Customers_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER Customers_seq_tr
 BEFORE INSERT ON Customers FOR EACH ROW
 WHEN (NEW.CustomerID IS NULL)
BEGIN
 SELECT Customers_seq.NEXTVAL INTO :NEW.CustomerID FROM DUAL;
END;
/

CREATE TABLE Products
(
ProductID NUMBER(3) CHECK (ProductID > 0) PRIMARY KEY,
ProductName VARCHAR2(20),
RecommendedPrice NUMBER(15,4),
Category VARCHAR2(10)
);

-- Generate ID using sequence and trigger
CREATE SEQUENCE Products_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER Products_seq_tr
 BEFORE INSERT ON Products FOR EACH ROW
 WHEN (NEW.ProductID IS NULL)
BEGIN
 SELECT Products_seq.NEXTVAL INTO :NEW.ProductID FROM DUAL;
END;
/

CREATE TABLE Sales
(
SaleID NUMBER(10) PRIMARY KEY,
ProductID NUMBER(3) CHECK (ProductID > 0) NOT NULL REFERENCES Products(ProductID),
CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
SalePrice DECIMAL NOT NULL,
SaleDate DATE NOT NULL
);

-- Generate ID using sequence and trigger
CREATE SEQUENCE Sales_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER Sales_seq_tr
 BEFORE INSERT ON Sales FOR EACH ROW
 WHEN (NEW.SaleID IS NULL)
BEGIN
 SELECT Sales_seq.NEXTVAL INTO :NEW.SaleID FROM DUAL;
END;
/

INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('DVD',105,'LivingRoom');
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Microwave',98,'Kitchen');
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Monitor',200,'Office');
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Speakers',85,'Office');
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Refrigerator',900,'Kitchen');
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('VCR',165,'LivingRoom');
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('CoffeePot',35,'Kitchen');


INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('John','Miller','Asbury','NY','23433');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Fred','Hammill','Basham','AK','85675');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Stan','Mellish','Callahan','WY','38556');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Adrian','Caparzo','Denver','CO','12377');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Mike','Horvath','Easton','IN','47130');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Irwin','Wade','Frankfurt','KY','45902');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('George','Marshall','Gallipoli','ND','34908');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Frank','Costello','Honolulu','HI','23905');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Billy','Costigan','Immice','SC','75389');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Shelly','Sipes','Lights','AZ','35263');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Chirsty','Melton','Spade','CA','97505');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Amanda','Owens','Flask','CN','50386');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Brittany','Smits','Bourbon','KY','24207');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kristy','Bryant','Tarp','FL','58960');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kelly','Street','TableTop','ID','57732');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Tricia','Hill','Camera','ME','46738');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Holly','Raines','Compact','MS','35735');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Natalie','Woods','Woods','IN','87219');
INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Wendy','Hilton','Action','KY','47093');


INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130, to_date('2/6/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,97,to_date('1/7/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,3,200,to_date('8/8/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,4,80,to_date('4/9/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,899,to_date('10/10/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,6,150,to_date('10/11/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,7,209,to_date('12/12/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,8,90,to_date('5/13/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,9,130,to_date('6/14/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,14,85,to_date('6/19/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,15,240,to_date('9/20/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,16,99,to_date('7/21/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,17,87,to_date('3/22/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,18,99,to_date('1/23/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,19,150,to_date('3/24/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,900,to_date('3/10/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,6,86,to_date('8/11/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,88,to_date('8/12/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,198,to_date('12/13/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,9,150,to_date('5/14/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,14,99,to_date('7/19/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,15,104,to_date('9/20/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,16,270,to_date('2/21/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,17,90,to_date('7/22/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,to_date('3/6/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,102,to_date('4/7/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,3,114,to_date('11/8/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,4,1000,to_date('5/9/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,1100,to_date('10/10/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,6,285,to_date('6/11/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,87,to_date('10/12/2005', 'mm/dd/yyyy'));
INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,300,to_date('7/13/2005', 'mm/dd/yyyy'));