?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
core reorg
[csql.git] / test / sql / Aggregate / exp.testnw025.ksh
blob696bcbf4ecf6e3eb7e22e15e64680da34c8e87ec
1 Network CSql
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));
3 Statement Executed
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));
5 Statement Executed
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));
7 Statement Executed
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));
9 Statement Executed
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));
11 Statement Executed
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
100
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
114
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
123
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
134
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
153
154 Network CSql
155 echo select client_master.clientno,name,count(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name;
156 ---------------------------------------------------------
157 client_master.clientno name COUNT(qtydisp)
158 ---------------------------------------------------------
159 C00001 Ivan Bayross 5
160 C00002 mamta Muzumdar 1
161 C00003 Chhaya Bankar 4
162 C00004 Ashwini Joshi 2
163 C00005 Hansel Colaco 2
164
165 echo select client_master.clientno,name,count(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name having count(qtydisp)>3;
166 ---------------------------------------------------------
167 client_master.clientno name COUNT(qtydisp)
168 ---------------------------------------------------------
169 C00001 Ivan Bayross 5
170 C00003 Chhaya Bankar 4
171
172 echo select client_master.clientno,name,min(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name;
173 ---------------------------------------------------------
174 client_master.clientno name MIN(qtydisp)
175 ---------------------------------------------------------
176 C00001 Ivan Bayross 1
177 C00002 mamta Muzumdar 0
178 C00003 Chhaya Bankar 1
179 C00004 Ashwini Joshi 0
180 C00005 Hansel Colaco 3
181
182 echo select client_master.clientno,name,min(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name having min(qtydisp)<3;
183 ---------------------------------------------------------
184 client_master.clientno name MIN(qtydisp)
185 ---------------------------------------------------------
186 C00001 Ivan Bayross 1
187 C00002 mamta Muzumdar 0
188 C00003 Chhaya Bankar 1
189 C00004 Ashwini Joshi 0
190
191 echo select client_master.clientno,name,max(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name;
192 ---------------------------------------------------------
193 client_master.clientno name MAX(qtydisp)
194 ---------------------------------------------------------
195 C00001 Ivan Bayross 4
196 C00002 mamta Muzumdar 0
197 C00003 Chhaya Bankar 10
198 C00004 Ashwini Joshi 0
199 C00005 Hansel Colaco 5
200
201 echo select client_master.clientno,name,max(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name having max(qtydisp)>4;
202 ---------------------------------------------------------
203 client_master.clientno name MAX(qtydisp)
204 ---------------------------------------------------------
205 C00003 Chhaya Bankar 10
206 C00005 Hansel Colaco 5
207
208 echo select client_master.clientno,name,sum(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name;
209 ---------------------------------------------------------
210 client_master.clientno name SUM(qtydisp)
211 ---------------------------------------------------------
212 C00001 Ivan Bayross 9
213 C00002 mamta Muzumdar 0
214 C00003 Chhaya Bankar 18
215 C00004 Ashwini Joshi 0
216 C00005 Hansel Colaco 8
217
218 echo select client_master.clientno,name,sum(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name having sum(qtydisp)>3;
219 ---------------------------------------------------------
220 client_master.clientno name SUM(qtydisp)
221 ---------------------------------------------------------
222 C00001 Ivan Bayross 9
223 C00003 Chhaya Bankar 18
224 C00005 Hansel Colaco 8
225
226 echo select client_master.clientno,name,avg(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name;
227 ---------------------------------------------------------
228 client_master.clientno name AVG(qtydisp)
229 ---------------------------------------------------------
230 C00001 Ivan Bayross 1.800000
231 C00002 mamta Muzumdar 0.000000
232 C00003 Chhaya Bankar 4.500000
233 C00004 Ashwini Joshi 0.000000
234 C00005 Hansel Colaco 4.000000
235
236 echo select client_master.clientno,name,avg(qtydisp) from sales_order_details,sales_order,client_master where client_master.clientno=sales_order.clientno and sales_order.orderno=sales_order_details.orderno group by client_master.clientno,name having avg(qtydisp)>3;
237 ---------------------------------------------------------
238 client_master.clientno name AVG(qtydisp)
239 ---------------------------------------------------------
240 C00003 Chhaya Bankar 4.500000
241 C00005 Hansel Colaco 4.000000
242
243 echo drop table sales_order_details;
244 Statement Executed
245 echo drop table sales_order;
246 Statement Executed
247 echo drop table salesman_master;
248 Statement Executed
249 echo drop table product_master;
250 Statement Executed
251 echo drop table client_master;
252 Statement Executed
Main memory database SQL engine (cvs import)