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