1)create table client_master45
2 (client_no varchar(6) primary key check(client_no like 'C%'),
3 name varchar(20) not null,
4 address1 varchar(30),
5 address2 varchar(30),
6 city varchar(15),
7 state varchar(15),
8 pincode number(6),
9 bal_due number(10,2));
Table created.
SQL> insert into client_master45 (client_no,name,city,pincode,state,bal_due)
values('C00001','Ivan bayross','bombay',400054,'Maharashtra',15000);
1 row created.
SQL> insert into client_master24 (client_no,name,city,pincode,state,bal_due)
values('C00002','Vandana Saitwal','Madras',780001,'Tamil Nadu',0);
1 row created.
SQL> insert into client_master24 (client_no,name,city,pincode,state,bal_due)
values('C00004','basu Navindgi','bombay',400056,'Maharashtra',0);
1 row created.
SQL> insert into client_master24 (client_no,name,city,pincode,bal_due)
values('C00005','Ravi Sreedharan','Delhi',100001,2400);
1 row created.
SQL> insert into client_master24 (client_no,name,city,pincode,state,bal_due)
values('C00006','Rukmini','bombay',400050,'Maharashtra',0);
1 row created.
2) create table product_master45
2 (product_no varchar(6) primary key check(product_no like 'P%'),
3 discription varchar(50) not null,
4 profit_percent number(5,2) not null,
5 unit_measure varchar(10) not null,
6 qty_on_hand number(8) not null,
7 reorder_lvl number(8) not null,
8 sell_price number(8,2)not null check(sell_price not like 0),
9 cost_price number(8,2) not null check(cost_price not like 0));
Table created.
SQL>
SQL> insert all
2 into product_master24 values('P00001','1.44
Floppies',5,'Piece',100,24,525,500)
3 into product_master24
values('P03453','Monitors',6,'Piece',10,3,12400,11280)
4 into product_master24 values('P06734','Mouse',5,'Piece',24,5,1050,1000)
5 into product_master24 values('P07865','1.22
Floppies',5,'Piece',100,24,525,500)
6 into product_master24
values('P07868','Keyboards',2,'Piece',10,3,3150,3050)
7 into product_master24 values('P07885','CD
Drive',2.5,'Piece',10,3,5250,5100)
8 into product_master24 values('P07965','540 Hdd',4,'Piece',10,3,8400,8000)
9 into product_master24 values('P07975','1.44
Drive',5,'Piece',10,3,1050,1000)
10 into product_master24 values('P08865','1.22 Drive',5,'Piece',2,3,1050,1000)
11 select * from dual;
9 rows created.
create table salesman_master45
(salesman_no varchar(6) primary key check(salesman_no like 'S%'),
salesman_name varchar(20) not null,
address1 varchar(30),
address2 varchar(30),
city varchar(20),
pincode varchar(6),
state varchar(20),
sal_amt number(8,2) not null check(sal_amt>0),
tgt_to_get number(6,2) not null check(tgt_to_get>0),
ytd_sales number(6,2) not null,
remarks varchar(60));
insert all
into salesman_master45
values('S00001','Kiran','a/14','Worli','bombay','400002','MaH',3000,100,50,'Good')
into salesman_master24
values('S00002','Manish','65','Nariman','bombay','400001','MaH',3000,240,100,'Good'
)
into salesman_master24 values('S00003','Ravi','P-
7','bandra','bombay','400032','MaH',3000,240,100,'Good')
into salesman_master24
values('S00004','ashish','a/5','Juhu','bombay','400044','MaH',3000,240,150,'Good')
select * from dual;
create table sales_order45
(s_order_no varchar(6) primary key check(s_order_no like'O%'),
s_order_date varchar(15),
client_no varchar(6) references client_master24(client_no),
dely_addr varchar(25),
salesman_no varchar(6) references salesman_master24(salesman_no),
dely_type char(1) default 'F' check(dely_type in('P','F')),
billed_yn char(1),
dely_date varchar(15) ,
order_status varchar(10) check(order_status in('In
Process','Fulfilled','backOrder','Canceled')));
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O19001','12-jan-96','C00001','F','N','S00001','24-jan-96','In
Process');
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O19002','25-
create table sales_order_details24
(s_order_no varchar(8),
product_no varchar(8),
qty_ordered number(8),
qty_disp number(8),
product_rate number(10,2),
primary key(s_order_no,product_no),
foreign key(s_order_no) references sales_order24(s_order_no),
foreign key(product_no) references product_master24(product_no));
create table challan_header24
(challan_no varchar(6) primary key, check(challan_no like'CH%'),
s_order_no varchar(6) references sales_order24(s_order_no),
challan_date varchar(15) not null,
billed_yn char(1) default 'N' check(billed_yn in('Y','N')));
create table challan_details24
(challan_no varchar(6),
product_no varchar(6),
qty_disp number(8));
product_rate number(10,2),
primary key(s_order_no,product_no),
foreign key(s_order_no) references sales_order24(s_order_no),
foreign key(product_no) references product_master24(product_no));
create table challan_header24
create table sales_order_details24
(s_order_no varchar(8),
product_no varchar(8),
qty_ordered number(8),
qty_disp number(8),
product_rate number(10,2),
primary key(s_order_no,product_no),
foreign key(s_order_no) references sales_order24(s_order_no),
foreign key(product_no) references product_master24(product_no));
create table challan_header24
(challan_no varchar(6) primary key, check(challan_no like'CH%'),
s_order_no varchar(6) references sales_order24(s_order_no),
challan_date varchar(15) not null,
billed_yn char(1) default 'N' check(billed_yn in('Y','N')));
create table challan_details24
(challan_no varchar(6),
product_no varchar(6),
qty_disp number(8));
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O19001','12-jan-96','C00001','F','N','S00001','24-jan-96','In
Process');
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O19002','25-jan-96','C00002','P','N','S00002','27-jan-
96','Canceled');
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O46865','18-feb-96','C00003','F','Y','S00003','24-feb-
96','Fulfilled');
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O19003','3-apr-96','C00001','F','Y','S00001','07-apr-
96','Fulfilled');
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O46866','24-may-96','C00004','P','N','S00002','22-may-
96','Canceled');
insert into sales_order24
(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_
status) values('O10008','24-may-96','C00005','F','N','S00004','26-may-96','In
Process');
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O19001','P00001',4,4,525);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O19001','P07965',2,1,8400);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O19001','P07885',2,1,5250);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O19002','P00001',10,0,525);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O46865','P07868',3,3,3150);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O46865','P07885',3,1,5250);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O46865','P00001',10,10,525);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O46865','P03453',4,4,1050);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O19003','P03453',2,2,1050);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O19003','P06734',1,1,12400);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O46865','P07965',1,0,8400);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O46865','P07975',1,0,1050);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O10008','P00001',10,5,525);
insert into sales_order_details24
(s_order_no,product_no,qty_ordered,qty_disp,product_rate)
values('O10008','P07975',5,3,1050);
insert all
into challan_header24 values('CH9001','O19001','12-dec-95','Y')
into challan_header24 values('CH6865','O46865','12-nov-95','Y')
into challan_header24 values('CH3965','O10008','12-oct-95','Y')
select * from dual;
insert all
into challan_details24 values('ch9001','p00001',4)
into challan_details24 values('ch9001','p07965',1)
into challan_details24 values('ch9001','p07885',1)
into challan_details24 values('ch6865','p07868',3)
into challan_details24 values('ch6865','p03453',4)
into challan_details24 values('ch6865','p0001',10)
into challan_details24 values('ch3865','p0001',5)
into challan_details24 values('ch3865','p07975',2)
select *from dual;
