create table CLIENT_MASTER (
CLIENTNO varchar(10) ,
NAME varchar(20),
CITY varchar(15),
PINCODE int(8),
STATE varchar(15),
BALDUE decimal(10,2),
primary key(CLIENTNO)
);
insert into CLIENT_MASTER (CLIENTNO , NAME , CITY , PINCODE , STATE , BALDUE )
values('C00001' , 'Ivan Bayrons' , 'Mumbai', 400054 ,'Maharastra',15000);
insert into CLIENT_MASTER (CLIENTNO , NAME , CITY , PINCODE , STATE , BALDUE )
values('C00002' , 'Mamta Muzumdar' , 'Madras',780001,'Tamil Nadu',0);
insert into CLIENT_MASTER (CLIENTNO , NAME , CITY , PINCODE , STATE , BALDUE )
values('C00003' , 'Chhaya Bankar' , 'Mumbai',400057,'Maharastra',5000);
insert into CLIENT_MASTER (CLIENTNO , NAME , CITY , PINCODE , STATE , BALDUE )
values('C00004' , 'Ashwini Joshi' , 'Banglore',560001,'karnataka',0);
insert into CLIENT_MASTER (CLIENTNO , NAME , CITY , PINCODE , STATE , BALDUE )
values('C00005' , 'Hannel Colaco' , 'Mumbai',400060,'Maharashtra',2000);
insert into CLIENT_MASTER (CLIENTNO , NAME , CITY , PINCODE , STATE , BALDUE )
values('C00006' , 'Deepak Sharma' , 'Mangalore',560050,'Karnataka',0);
create table PRODUCT_MASTER
(
PRODUCTNO varchar(6),
DESCRIPTION varchar(15) not null,
PROFITPERCENT int(4) not null ,
UNITMEASURE varchar(10) not null,
QTYONHAND int(8) not null,
REORDERLVL int(8) not null,
SELLPRICE int(8) not null,
COSTPRICE int(8) not null,
primary key(PRODUCTNO),
check(SELLPRICE!=0),
check(COSTPRICE!=0),
check(PRODUCTNO like 'P%')
);
create table SALESMAN_MASTER
(
SALESMANNO varchar(6),
SALESMANNAME varchar(20) not null,
ADDRESS1 varchar(30) not null ,
ADDRESS2 varchar(30),
CITY varchar(20),
PINCODE int(8),
STATE varchar(20),
SALAMT decimal(8,2) not null,
TGTTOGET decimal(6,2) not null,
YTDSALES decimal(6,2) not null,
REMARKS varchar(60),
primary key(SALESMANNO),
check(SALAMT!=0),
check(TGTTOGET!=0),
check(SALESMANNO like 'S%')
);
create table SALES_ORDER(
ORDERNO varchar(6),
CLIENTNO varchar(6),
ORDERDATE date not null,
DELYADDR varchar(25),
SALESMANNO varchar(6),
DELYTYPE char(1),
BILLYN char(1),
DELYDATE date,
ORDERSTATUS varchar(10),
primary key(ORDERNO),
check(ORDERNO like 'O%'),
check(DELYDATE >= ORDERDATE),
check(ORDERSTATUS ='In Process' or ORDERSTATUS ='Fulfilled' or ORDERSTATUS
='BackOrder' or ORDERSTATUS ='Cancelled'),
foreign key(CLIENTNO) references CLIENT_MASTER(CLIENTNO),
foreign key(SALESMANNO) references SALES_MASTER(SALESMANNO)
);
create table SALES_ORDER_DETAILS (
ORDERNO varchar(6),
PRODUCTNO varchar(6),
QTYORDERED int(8),
QTYDISP int(8),
PRODUCTRATE decimal(10,2),
foreign key(ORDERNO) references CLIENT_MASTER(CLIENTNO),
foreign key(PRODUCTNO) references PRODUCT_MASTER(PRODUCTNO)
);
Insert Into SALESMAN_MASTER(SalesmanNo , SalesmanName , Address1 , Address2 ,
City , Pincode , State ,Salamt ,Tgttoget ,Ytdsales ,Remarks )
Values('S00001','Aman','A/14','Worli','Mumbai',400002,'Maharashtra',3000,100,50,'Good');
Insert Into SALESMAN_MASTER(SalesmanNo , SalesmanName , Address1 , Address2 ,
City , Pincode , State ,Salamt ,Tgttoget ,Ytdsales ,Remarks )
Values('S00002','Omkar','65','Nariman','Mumbai',400001,'Maharashtra',3000,200,100,'Good'
);
Insert Into SALESMAN_MASTER(SalesmanNo , SalesmanName , Address1 , Address2 ,
City , Pincode , State ,Salamt ,Tgttoget ,Ytdsales ,Remarks )
Values('S00003','Raj','P-7','Bandra','Mumbai',400032,'Maharashtra',3000,200,100,'Good');
Insert Into SALESMAN_MASTER(SalesmanNo , SalesmanName , Address1 , Address2 ,
City , Pincode , State ,Salamt ,Tgttoget ,Ytdsales ,Remarks )
Values('S00004','Ashish','A/5','Juhu','Mumbai',400044,'Maharashtra',3500,200,150,'Good');
insert into SALES_ORDER(ORDERNO , CLIENTNO ,ORDERDATE ,DELYADDR ,
SALESMANNO ,DELYTYPE ,BILLYN ,DELYDATE , ORDERSTATUS ) values('O19001' ,
'C00001' , '2004/06/12', '' , 'S00001' , 'F' , 'N' , '2004/07/20', 'In Process');
insert into SALES_ORDER(ORDERNO , CLIENTNO ,ORDERDATE ,DELYADDR ,
SALESMANNO ,DELYTYPE ,BILLYN ,DELYDATE , ORDERSTATUS ) values('O19002' ,
'C00002' , '2004/06/25', '' , 'S00002' , 'P' , 'N' , '2004/06/27', 'Cancelled');
insert into SALES_ORDER(ORDERNO , CLIENTNO ,ORDERDATE ,DELYADDR ,
SALESMANNO ,DELYTYPE ,BILLYN ,DELYDATE , ORDERSTATUS ) values('O46865' ,
'C00003' , '2004/02/18', '' , 'S00003' , 'F' , 'Y' , '2004/02/20', 'Fulfilled');
insert into SALES_ORDER(ORDERNO , CLIENTNO ,ORDERDATE ,DELYADDR ,
SALESMANNO ,DELYTYPE ,BILLYN ,DELYDATE , ORDERSTATUS ) values('O19003' ,
'C00001' , '2004/04/03', '' , 'S00001' , 'F' , 'Y' , '2004/04/07', 'Fulfilled');
insert into SALES_ORDER(ORDERNO , CLIENTNO ,ORDERDATE ,DELYADDR ,
SALESMANNO ,DELYTYPE ,BILLYN ,DELYDATE , ORDERSTATUS ) values('O46866' ,
'C00004' , '2004/05/20', '' , 'S00002' , 'P' , 'N' , '2004/05/22', 'Cancelled');
insert into SALES_ORDER(ORDERNO , CLIENTNO ,ORDERDATE ,DELYADDR ,
SALESMANNO ,DELYTYPE ,BILLYN ,DELYDATE , ORDERSTATUS ) values('O19008' ,
'C00005' , '2004/05/24', '' , 'S00004' , 'F' , 'N' , '2004/07/26', 'In Process');
Rename karne ke liye : alter table sales_master rename to salesman_master;
insert into product_master
(PRODUCTNO,DESCRIPTION,PROFITPERCENT,UNITMEASURE,QTYONHAND,REORD
ERLVL,SELLPRICE,COSTPRICE)
VALUES
("P0001","T-Shirts",5,"piece",200,50,350,250),
("P0345","Shirts",6,"piece",150,50,500,350),
("P06734","Cotton Jeans",5,"piece",100,20,600,450),
("P07865","Jeans",5,"piece",100,20,750,500),
("P07868","Trousers",2,"piece",150,50,850,550),
("P07885","Pull Overs",2.5,"piece",80,30,700,450),
("P07965","Denim Shirts",4,"piece",100,40,350,250),
("P07975","Lycra Tops",5,"piece",70,30,300,175),
("P08865"," Skirts",5,"piece",75,30,450,300)
;
