1 echo Create table client_master
(clientno char
(6), name char
(20) NOT NULL
, address1 char
(30), address2 char
(30), city char
(15), pincode int
, state char
(15), baldue float
,PRIMARY KEY
(clientno
));
3 echo Create table product_master
(productno char
(6), description char
(15) NOT NULL
,profitpercent float NOT NULL
,unitmeasure char
(10) NOT NULL
,qtyonhand int NOT NULL
,reorderlvl int NOT NULL
,selprice float NOT NULL
,costprice float NOT NULL
, PRIMARY KEY
(productno
));
5 echo Create table salesman_master
(salesmanno char
(6),salesmanname char
(20) NOT NULL
,address1 char
(30) NOT NULL
,address2 char
(30),city char
(20),pincode char
(8),state char
(20),salamt float NOT NULL
,tgttoget float NOT NULL
,ytdsales float NOT NULL
,remarks char
(30),PRIMARY KEY
(salesmanno
));
7 echo Create table sales_order
(orderno char
(6),orderdate
date,clientno char
(6),delyaddr char
(25),delytype char
(1),billedyn char
(1),salesmanno char
(6),delydate
date,orderstatus char
(10),PRIMARY KEY
(orderno
),FOREIGN KEY
(clientno
) REFERENCES client_master
(clientno
),FOREIGN KEY
(salesmanno
) REFERENCES salesman_master
(salesmanno
));
9 echo Create table sales_order_details
(orderno char
(6),productno char
(6),qtyordered int
,qtydisp int
,productrate float
,FOREIGN KEY
(orderno
) REFERENCES sales_order
(orderno
),FOREIGN KEY
(productno
) REFERENCES product_master
(productno
));
11 echo insert into client_master
(clientno
,name
,city
,pincode
,state
,baldue
) values
('C00001','Ivan Bayross','Mumbai',400054,'Maharastra',15000);
12 Statement Executed
: Rows Affected
= 1
13 echo insert into client_master
(clientno
,name
,city
,pincode
,state
,baldue
) values
('C00002','mamta Muzumdar','Madras',780001,'Tamil Nadu',0);
14 Statement Executed
: Rows Affected
= 1
15 echo insert into client_master
(clientno
,name
,city
,pincode
,state
,baldue
) values
('C00003','Chhaya Bankar','Mumbai',400057,'Maharastra',5000);
16 Statement Executed
: Rows Affected
= 1
17 echo insert into client_master
(clientno
,name
,city
,pincode
,state
,baldue
) values
('C00004','Ashwini Joshi','Bangalore',560001,'MKarnataka',0);
18 Statement Executed
: Rows Affected
= 1
19 echo insert into client_master
(clientno
,name
,city
,pincode
,state
,baldue
) values
('C00005','Hansel Colaco','Mumbai',400060,'Maharastra',2000);
20 Statement Executed
: Rows Affected
= 1
21 echo insert into client_master
(clientno
,name
,city
,pincode
,state
,baldue
) values
('C00006','Deepak Sharma','Mangalore',560050,'Karnatakaa',0);
22 Statement Executed
: Rows Affected
= 1
23 echo insert into product_master values
('P00001','T-Shirts',5,'Piece',200,50,350,250);
24 Statement Executed
: Rows Affected
= 1
25 echo insert into product_master values
('P03453','Shirts',6,'Piece',150,50,500,350);
26 Statement Executed
: Rows Affected
= 1
27 echo insert into product_master values
('P06734','Cotton Jeans',5,'Piece',100,20,60,450);
28 Statement Executed
: Rows Affected
= 1
29 echo insert into product_master values
('P07865','Jeans',5,'Piece',100,20,750,500);
30 Statement Executed
: Rows Affected
= 1
31 echo insert into product_master values
('P07868','Trousers',2,'Piece',150,50,850,550);
32 Statement Executed
: Rows Affected
= 1
33 echo insert into product_master values
('P07885','Pull Overs',2.5,'Piece',80,30,700,450);
34 Statement Executed
: Rows Affected
= 1
35 echo insert into product_master values
('P07965','Denim Shirts',4,'Piece',100,40,350,250);
36 Statement Executed
: Rows Affected
= 1
37 echo insert into product_master values
('P07975','Lycra Tops',5,'Piece',70,30,300,175);
38 Statement Executed
: Rows Affected
= 1
39 echo insert into product_master values
('P08865','Skirts',5,'Piece',75,30,450,300);
40 Statement Executed
: Rows Affected
= 1
41 echo insert into salesman_master values
('S00001','Aman','A/14','Worli','Mumbai',400002,'Maharastra',3000,100,50,'Good');
42 Statement Executed
: Rows Affected
= 1
43 echo insert into salesman_master values
('S00002','Omkar','65','Nariman','Mumbai',400001,'maharastra',3000,200,100,'Good');
44 Statement Executed
: Rows Affected
= 1
45 echo insert into salesman_master values
('S00003','Raj','P-7','Banfra','Mumbai',400032,'Maharastra',3000,200,100,'Good');
46 Statement Executed
: Rows Affected
= 1
47 echo insert into salesman_master values
('S00004','Ashish','A/5','Juhu','Bombay',400044,'Maharastra',3500,200,150,'Good');
48 Statement Executed
: Rows Affected
= 1
49 echo insert into sales_order
(orderno
,orderdate
,clientno
,delytype
,billedyn
,salesmanno
,delydate
,orderstatus
) values
('O19001','2002-06-12','C00001','F','N','S00001','2002-07-20','In Process');
50 Statement Executed
: Rows Affected
= 1
51 echo insert into sales_order
(orderno
,orderdate
,clientno
,delytype
,billedyn
,salesmanno
,delydate
,orderstatus
) values
('O19002','2002-06-25','C00002','P','N','S00002','2002-06-27','Cancelled');
52 Statement Executed
: Rows Affected
= 1
53 echo insert into sales_order
(orderno
,orderdate
,clientno
,delytype
,billedyn
,salesmanno
,delydate
,orderstatus
) values
('O46865','2002-02-18','C00003','F','Y','S00003','2002-02-20','Fulfilled');
54 Statement Executed
: Rows Affected
= 1
55 echo insert into sales_order
(orderno
,orderdate
,clientno
,delytype
,billedyn
,salesmanno
,delydate
,orderstatus
) values
('O19003','2002-04-03','C00001','F','Y','S00001','2002-04-07','Fulfilled');
56 Statement Executed
: Rows Affected
= 1
57 echo insert into sales_order
(orderno
,orderdate
,clientno
,delytype
,billedyn
,salesmanno
,delydate
,orderstatus
) values
('O46866','2002-05-20','C00004','P','N','S00002','2002-05-22','Cancelled');
58 Statement Executed
: Rows Affected
= 1
59 echo insert into sales_order
(orderno
,orderdate
,clientno
,delytype
,billedyn
,salesmanno
,delydate
,orderstatus
) values
('O19008','2002-05-24','C00005','F','N','S00004','2002-07-26','In Process');
60 Statement Executed
: Rows Affected
= 1
61 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19001','P00001',4,4,525);
62 Statement Executed
: Rows Affected
= 1
63 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19001','P07965',2,1,8400);
64 Statement Executed
: Rows Affected
= 1
65 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19001','P07885',2,1,5250);
66 Statement Executed
: Rows Affected
= 1
67 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19002','P00001',10,0,525);
68 Statement Executed
: Rows Affected
= 1
69 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O46865','P07868',3,3,3150);
70 Statement Executed
: Rows Affected
= 1
71 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O46865','P07885',3,1,5250);
72 Statement Executed
: Rows Affected
= 1
73 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O46865','P00001',10,10,525);
74 Statement Executed
: Rows Affected
= 1
75 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O46865','P03453',4,4,1050);
76 Statement Executed
: Rows Affected
= 1
77 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19003','P03453',2,2,1050);
78 Statement Executed
: Rows Affected
= 1
79 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19003','P06734',1,1,12000);
80 Statement Executed
: Rows Affected
= 1
81 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O46866','P07965',1,0,8400);
82 Statement Executed
: Rows Affected
= 1
83 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O46866','P07975',1,0,1050);
84 Statement Executed
: Rows Affected
= 1
85 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19008','P00001',10,5,525);
86 Statement Executed
: Rows Affected
= 1
87 echo insert into sales_order_details
(orderno
,productno
,qtyordered
,qtydisp
,productrate
) values
('O19008','P07975',5,3,1050);
88 Statement Executed
: Rows Affected
= 1
89 echo select * from client_master
;
90 ---------------------------------------------------------
91 client_master.clientno client_master.name client_master.address1 client_master.address2 client_master.city client_master.pincode client_master.state client_master.baldue
92 ---------------------------------------------------------
93 C00001 Ivan Bayross NULL NULL Mumbai
400054 Maharastra
15000.000000
94 C00002 mamta Muzumdar NULL NULL Madras
780001 Tamil Nadu
0.000000
95 C00003 Chhaya Bankar NULL NULL Mumbai
400057 Maharastra
5000.000000
96 C00004 Ashwini Joshi NULL NULL Bangalore
560001 MKarnataka
0.000000
97 C00005 Hansel Colaco NULL NULL Mumbai
400060 Maharastra
2000.000000
98 C00006 Deepak Sharma NULL NULL Mangalore
560050 Karnatakaa
0.000000
100 echo select * from product_master
;
101 ---------------------------------------------------------
102 product_master.productno product_master.description product_master.profitpercent product_master.unitmeasure product_master.qtyonhand product_master.reorderlvl product_master.selprice product_master.costprice
103 ---------------------------------------------------------
104 P00001 T-Shirts
5.000000 Piece
200 50 350.000000 250.000000
105 P03453 Shirts
6.000000 Piece
150 50 500.000000 350.000000
106 P06734 Cotton Jeans
5.000000 Piece
100 20 60.000000 450.000000
107 P07865 Jeans
5.000000 Piece
100 20 750.000000 500.000000
108 P07868 Trousers
2.000000 Piece
150 50 850.000000 550.000000
109 P07885 Pull Overs
2.500000 Piece
80 30 700.000000 450.000000
110 P07965 Denim Shirts
4.000000 Piece
100 40 350.000000 250.000000
111 P07975 Lycra Tops
5.000000 Piece
70 30 300.000000 175.000000
112 P08865 Skirts
5.000000 Piece
75 30 450.000000 300.000000
114 echo select * from salesman_master
;
115 ---------------------------------------------------------
116 salesman_master.salesmanno salesman_master.salesmanname salesman_master.address1 salesman_master.address2 salesman_master.city salesman_master.pincode salesman_master.state salesman_master.salamt salesman_master.tgttoget salesman_master.ytdsales salesman_master.remarks
117 ---------------------------------------------------------
118 S00001 Aman A
/14 Worli Mumbai
400002 Maharastra
3000.000000 100.000000 50.000000 Good
119 S00002 Omkar
65 Nariman Mumbai
400001 maharastra
3000.000000 200.000000 100.000000 Good
120 S00003 Raj P-7 Banfra Mumbai
400032 Maharastra
3000.000000 200.000000 100.000000 Good
121 S00004 Ashish A
/5 Juhu Bombay
400044 Maharastra
3500.000000 200.000000 150.000000 Good
123 echo select * from sales_order
;
124 ---------------------------------------------------------
125 sales_order.orderno sales_order.orderdate sales_order.clientno sales_order.delyaddr sales_order.delytype sales_order.billedyn sales_order.salesmanno sales_order.delydate sales_order.orderstatus
126 ---------------------------------------------------------
127 O19001
2002/6/12 C00001 NULL F N S00001
2002/7/20 In Process
128 O19002
2002/6/25 C00002 NULL P N S00002
2002/6/27 Cancelled
129 O46865
2002/2/18 C00003 NULL F Y S00003
2002/2/20 Fulfilled
130 O19003
2002/4/3 C00001 NULL F Y S00001
2002/4/7 Fulfilled
131 O46866
2002/5/20 C00004 NULL P N S00002
2002/5/22 Cancelled
132 O19008
2002/5/24 C00005 NULL F N S00004
2002/7/26 In Process
134 echo select * from sales_order_details
;
135 ---------------------------------------------------------
136 sales_order_details.orderno sales_order_details.productno sales_order_details.qtyordered sales_order_details.qtydisp sales_order_details.productrate
137 ---------------------------------------------------------
138 O19001 P00001
4 4 525.000000
139 O19001 P07965
2 1 8400.000000
140 O19001 P07885
2 1 5250.000000
141 O19002 P00001
10 0 525.000000
142 O46865 P07868
3 3 3150.000000
143 O46865 P07885
3 1 5250.000000
144 O46865 P00001
10 10 525.000000
145 O46865 P03453
4 4 1050.000000
146 O19003 P03453
2 2 1050.000000
147 O19003 P06734
1 1 12000.000000
148 O46866 P07965
1 0 8400.000000
149 O46866 P07975
1 0 1050.000000
150 O19008 P00001
10 5 525.000000
151 O19008 P07975
5 3 1050.000000
153 echo select sales_order_details.productno
,description from sales_order_details
,sales_order
,product_master
,client_master where product_master.productno
=sales_order_details.productno and sales_order.orderno
=sales_order_details.orderno and client_master.clientno
=sales_order.clientno and client_master.name
='Ivan Bayross';
154 ---------------------------------------------------------
155 sales_order_details.productno description
156 ---------------------------------------------------------
163 echo drop table sales_order_details
;
165 echo drop table sales_order
;
167 echo drop table salesman_master
;
169 echo drop table product_master
;
171 echo drop table client_master
;