test/sql/Aggregate/exp.testnw027.ksh

   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 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
 173
 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
 185
 186 echo drop table sales_order_details;
 187 Statement Executed
 188 echo drop table sales_order;
 189 Statement Executed
 190 echo drop table salesman_master;
 191 Statement Executed
 192 echo drop table product_master;
 193 Statement Executed
 194 echo drop table client_master;
 195 Statement Executed