A5

CREATE TABLE client_master (Client_no varchar2(5),Name varchar2(20) NOT NULL UNIQUE,Address1 varchar2(30),State varchar2(30),City varchar2(15), PRIMARY KEY(Client_no), CHECK(Client_no LIKE 'C%'),CHECK(City IN ('Delhi','Mumbai','Chennai')));
DESC client_master;
CREATE TABLE product_master (Product_no varchar2(10),Description varchar2(20) NOT NULL UNIQUE,Qty_on_hand NUMBER(10),Sell_price NUMBER(8,2) NOT NULL,Cost_Price NUMBER(8,2) NOT NULL, CHECK(Product_no LIKE 'P%'),CHECK(Qty_on_hand>10),PRIMARY KEY(Product_no));
DESC product_master;
CREATE TABLE sales_order(S_Order_no varchar2(10),S_order_date DATE,Client_no varchar2(5),Salesman_no varchar2(10),Product_no varchar2(10),CHECK(S_order_no LIKE 'O%'),CHECK(Salesman_no LIKE 'S%'),FOREIGN KEY(Client_no) REFERENCES client_master(Client_no),PRIMARY KEY(S_order_no),FOREIGN KEY(Product_no) REFERENCES product_master(Product_no));
DESC sales_order;
INSERT INTO Client_Master VALUES('C01','Ivaan','Church Rd','Maharashtra','Mumbai');
INSERT INTO Client_Master VALUES('C02','Vandana','St.Mary Rd','Tamil Nadu','Chennai');
INSERT INTO Client_Master VALUES('C03','Pramada','Mall Rd','Maharashtra','Mumbai');
INSERT INTO Client_Master VALUES('C04','Basu','Church Rd','Maharashtra','Mumbai');
INSERT INTO Client_Master VALUES('C05','Ravi','Chnadni',NULL,'Delhi');
INSERT INTO Client_Master VALUES('C06','Rukmani','Mall Rd','Maharashtra','Mumbai');