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