oracle.sql

CREATE TABLE TBL_STOCK (
	Product_ID Varchar2(6),
	Product_Name Varchar2(20),
	Quantity_On_Hand Number(11),
	Product_Unit_Price Number(11,2),
	Reorder_Level Number(11),
	CONSTRAINT PK00 PRIMARY KEY(Product_ID),
	CONSTRAINT UQ01 UNIQUE(Product_Name),
	CONSTRAINT CH02 CHECK(Quantity_On_Hand >= 0),
	CONSTRAINT CH03 CHECK(Product_Unit_Price >= 0),
	CONSTRAINT CH04 CHECK(Reorder_Level >= 0)
);

CREATE TABLE TBL_SALES (
	Sales_ID Varchar2(6), 
	Sales_Date Date,
	Product_ID Varchar2(6),
	Quantity_Sold Number(11),
	Sales_Price_Per_Unit Number(11,2),
	CONSTRAINT PK10 PRIMARY KEY(Sales_ID),
	CONSTRAINT FK11 FOREIGN KEY(Product_ID) REFERENCES TBL_STOCK(Product_ID),
	CONSTRAINT CH12 CHECK(Quantity_Sold >= 0),
	CONSTRAINT CH13 CHECK(Sales_Price_Per_Unit >= 0)
);

INSERT INTO TBL_STOCK VALUES('RE1001', 'REDMI Note 3', 20, 12000, 5);
INSERT INTO TBL_STOCK VALUES('ip1002', 'Iphone 5S', 10, 21000, 2);
INSERT INTO TBL_STOCK VALUES('PA1003', 'Panasonic P55', 50, 5500, 5);

CREATE SEQUENCE SEQ_SALES_ID START WITH 1000 INCREMENT BY 1;
CREATE SEQUENCE SEQ_PRODUCT_ID START WITH 1004 INCREMENT BY 1;

CREATE VIEW V_SALES_REPORT AS
	SELECT Sales_ID, Sales_Date, Product_ID, Product_Name, 
			Quantity_Sold, Product_Unit_Price, Sales_Price_Per_Unit, 
			(Sales_Price_Per_Unit - Product_Unit_Price) Profit_Amount
	FROM TBL_STOCK NATURAL JOIN TBL_SALES
	ORDER BY Profit_Amount DESC, Sales_ID ASC;
	
	create table TBL_ORDERS(
        Product_ID Varchar2(6),
	Product_Name Varchar2(20),
	Quantity Number(11),
        Item_Sold Varchar2(5),
        User_name Varchar2(40),
        CONSTRAINT FK21 FOREIGN KEY(Product_ID) REFERENCES TBL_STOCK(Product_ID),
        CONSTRAINT CH22 CHECK(Quantity >= 0)
);

CREATE VIEW V_RECEIPT1 AS
	SELECT Sales_ID, Sales_Date, Product_ID, Product_Name, 
			Quantity_Sold, User_Name, Sales_Price_Per_Unit,Item_Sold	
FROM TBL_SALES NATURAL JOIN TBL_ORDERS
ORDER BY Sales_Date DESC;

alter table TBL_SALES add USER_NAME varchar2(40);
alter table TBL_SALES add foreign key (USER_NAME) REFERENCES TBL_ORDERS(USER_NAME);
select * from TBL_ORDERS;
alter table TBL_ORDERS add PRIMARY KEY(USER_NAME);