sql

CREATE TABLE Client
(
	Cno VARCHAR2(6) PRIMARY KEY, CHECK( Cno LIKE 'C%'),
	Name VARCHAR2(20) NOT NULL,
	Address VARCHAR2(30),
	City VARCHAR2(15),
	PinCode NUMBER(8),
	State VARCHAR2(15),
	Baldue NUMBER(10,2)
);


CREATE TABLE Product
(
	Pno VARCHAR2(6) PRIMARY KEY, CHECK( Pno LIKE 'P%'),
	Description VARCHAR2(20) NOT NULL,
	ProfitPercent NUMBER(4,2) NOT NULL,
	UnitMeasure VARCHAR2(15) NOT NULL,
	qtyonHand NUMBER(8) NOT NULL,
	ReorderLvl VARCHAR2(15) NOT NULL,
	SellPrice NUMBER(8,2) NOT NULL,CHECK(SellPrice != 0),
	CostPrice NUMBER(8,2) NOT NULL,CHECK(CostPrice != 0)
);


CREATE TABLE Salesman
(
	Sno VARCHAR2(6) PRIMARY KEY, CHECK( Sno LIKE 'S%'),
	SName VARCHAR2(20) NOT NULL,
	City VARCHAR2(15) NOT NULL,
	State VARCHAR2(15),
	SelAmt NUMBER(6,2) NOT NULL, CHECK(SelAmt!=0),
	TgtToGet NUMBER(6,2) NOT NULL,CHECK(TgtToGet !=0),
	qtysale NUMBER(6,2),
	Remarks VARCHAR2(60)
);

CREATE TABLE Sales_Order
(
	OrderNo VARCHAR2(6) PRIMARY KEY CHECK( OrderNo LIKE 'O%'),
	OrderDate Date,
	Cno VARCHAR2(30),
	DelvAddr VARCHAR2(25),
	DelvType CHAR(1) DEFAULT 'P' CHECK (DelvType IN ('P','F')),
	Sno VARCHAR2(15) ,
	DelvDate DATE ,
	OrderStatus VARCHAR2(10) CHECK (OrderStatus IN ('In Process','Fulfilled','BackOrder','Cancelled')),
	FOREIGN KEY (Sno) REFERENCES Salesman(Sno),
	CHECK(DelvDate>=OrderDate)
);

CREATE TABLE Sales_Order_Details
(
	OrderNo VARCHAR2(6) ,
	Pno VARCHAR(7),
	QtyOrdered NUMBER(8),
	QtyDisp NUMBER(8),
	ProductRate NUMBER (10,2),
	PRIMARY KEY (OrderNo,Pno),
	FOREIGN KEY (OrderNo) REFERENCES Sales_Order(OrderNo),
	FOREIGN KEY (PNo) REFERENCES Product(PNo)
);

ALTER TABLE Sales_order
ADD CONSTRAINT client_constraint
FOREIGN KEY (Cno) REFERENCES Client(cno);

ALTER TABLE Client
ADD Telephone NUMBER(10);

ALTER TABLE Client
MODIFY name varchar2(10);

ALTER TABLE client
MODIFY pincode number(8) not null;

select constraint_name,search_condition,constraint_type from user_constraints where constraint_name='SYS_C0037604';

INSERT INTO client(cno,name,address,city,pincode,state,baldue)
values ('&cno','&name','&address','&city',&pincode,'&state',&baldue);



INSERT INTO product(pno,description,profitpercent,unitmeasure,qtyonHand,sellprice,costprice)
values ('&pno','&description',&profitpercent,'&unitmeasure',&qtyonHand,&sellprice,&costprice);


INSERT INTO salesman(sno,sname,city,state,selamt,tgttoget,qtysale,remarks)
values ('&sno','&sname','&city','&state',&selamt,&tgttoget,&qtysale,'&remarks');


INSERT INTO sales_order(orderno,orderdate,cno,delvaddr,delvtype,sno,delvdate,orderstatus)
values ('&orderno','&orderdate','&cno','&delVaddr','&delvtype','&sno','&delVdate','&orderstatus');

INSERT INTO sales_order_details(orderno,pno,qtyordered,qtydisp,productrate)
values ('&orderno','&pno',&qtyordered,&qtydisp,&productrate);