test/tools/csql/exp.test135.ksh

   1 echo CREATE TABLE t1(f1 int, f2 CHAR(10), f3 FLOAT, f4 TIMESTAMP);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 Statement Executed: Rows Affected = 1
   9 echo SELECT * FROM t1;
  10 ---------------------------------------------------------
  11         t1.f1   t1.f2   t1.f3   t1.f4
  12 ---------------------------------------------------------
  13         1       NULL    7.500000        2009/7/29 1:1:1.0
  14         NULL    NULL    NULL    2009/7/29 1:1:1.0
  15         10      PAPU    NULL    2009/7/29 1:1:1.0
  16         20      NULL    7.500000        2009/7/29 1:1:1.0
  17         NULL    PAPU    7.500000        2009/7/29 1:1:1.0
  18         30      PAPU    7.500000        2009/7/29 1:1:1.0
  19
  20 echo SELECT * FROM t1 WHERE f1 IS NULL;
  21 ---------------------------------------------------------
  22         t1.f1   t1.f2   t1.f3   t1.f4
  23 ---------------------------------------------------------
  24         NULL    NULL    NULL    2009/7/29 1:1:1.0
  25         NULL    PAPU    7.500000        2009/7/29 1:1:1.0
  26
  27 echo UPDATE t1 SET f2 = 'MUNA' WHERE f1 IS NULL;
  28 Statement Executed: Rows Affected = 2
  29 echo UPDATE t1 SET f2 = 'RAHUL' WHERE f1 IS NOT NULL;
  30 Statement Executed: Rows Affected = 4
  31 echo SELECT * FROM t1;
  32 ---------------------------------------------------------
  33         t1.f1   t1.f2   t1.f3   t1.f4
  34 ---------------------------------------------------------
  35         1       RAHUL   7.500000        2009/7/29 1:1:1.0
  36         NULL    MUNA    NULL    2009/7/29 1:1:1.0
  37         10      RAHUL   NULL    2009/7/29 1:1:1.0
  38         20      RAHUL   7.500000        2009/7/29 1:1:1.0
  39         NULL    MUNA    7.500000        2009/7/29 1:1:1.0
  40         30      RAHUL   7.500000        2009/7/29 1:1:1.0
  41
  42 echo SELECT * FROM t1 WHERE f2 IS NULL;
  43 ---------------------------------------------------------
  44         t1.f1   t1.f2   t1.f3   t1.f4
  45 ---------------------------------------------------------
  46
  47 echo UPDATE t1 SET f1 = 1000 WHERE f2 IS NULL;
  48 Statement Executed: Rows Affected = 0
  49 echo UPDATE t1 SET f1 = 2000 ,f2 = 'MONA' WHERE f2 IS NOT NULL;
  50 Statement Executed: Rows Affected = 6
  51 echo UPDATE t1 SET f1 = 3000 ,f2 = 'TIMA' WHERE f2 IS NULL;
  52 Statement Executed: Rows Affected = 0
  53 echo SELECT * FROM t1;
  54 ---------------------------------------------------------
  55         t1.f1   t1.f2   t1.f3   t1.f4
  56 ---------------------------------------------------------
  57         2000    MONA    7.500000        2009/7/29 1:1:1.0
  58         2000    MONA    NULL    2009/7/29 1:1:1.0
  59         2000    MONA    NULL    2009/7/29 1:1:1.0
  60         2000    MONA    7.500000        2009/7/29 1:1:1.0
  61         2000    MONA    7.500000        2009/7/29 1:1:1.0
  62         2000    MONA    7.500000        2009/7/29 1:1:1.0
  63
  64 echo SELECT * FROM t1 WHERE f3 IS NULL;
  65 ---------------------------------------------------------
  66         t1.f1   t1.f2   t1.f3   t1.f4
  67 ---------------------------------------------------------
  68         2000    MONA    NULL    2009/7/29 1:1:1.0
  69         2000    MONA    NULL    2009/7/29 1:1:1.0
  70
  71 echo UPDATE t1 SET f3 = 5.7 WHERE f3 IS NULL;
  72 Statement Executed: Rows Affected = 2
  73 echo UPDATE t1 SET f3 = 6.7 WHERE f3 IS NOT NULL;
  74 Statement Executed: Rows Affected = 6
  75 echo SELECT * FROM t1;
  76 ---------------------------------------------------------
  77         t1.f1   t1.f2   t1.f3   t1.f4
  78 ---------------------------------------------------------
  79         2000    MONA    6.700000        2009/7/29 1:1:1.0
  80         2000    MONA    6.700000        2009/7/29 1:1:1.0
  81         2000    MONA    6.700000        2009/7/29 1:1:1.0
  82         2000    MONA    6.700000        2009/7/29 1:1:1.0
  83         2000    MONA    6.700000        2009/7/29 1:1:1.0
  84         2000    MONA    6.700000        2009/7/29 1:1:1.0
  85
  86 echo DELETE FROM t1 WHERE f1 IS NULL;
  87 Statement Executed: Rows Affected = 0
  88 echo DELETE FROM t1 WHERE f2 IS NULL;
  89 Statement Executed: Rows Affected = 0
  90 echo DELETE FROM t1 WHERE f3 IS NULL;
  91 Statement Executed: Rows Affected = 0
  92 echo SELECT * FROM t1;
  93 ---------------------------------------------------------
  94         t1.f1   t1.f2   t1.f3   t1.f4
  95 ---------------------------------------------------------
  96         2000    MONA    6.700000        2009/7/29 1:1:1.0
  97         2000    MONA    6.700000        2009/7/29 1:1:1.0
  98         2000    MONA    6.700000        2009/7/29 1:1:1.0
  99         2000    MONA    6.700000        2009/7/29 1:1:1.0
 100         2000    MONA    6.700000        2009/7/29 1:1:1.0
 101         2000    MONA    6.700000        2009/7/29 1:1:1.0
 102
 103 echo DELETE FROM t1 WHERE f1 IS NOT NULL;
 104 Statement Executed: Rows Affected = 6
 105 echo DELETE FROM t1 WHERE f2 IS NOT NULL;
 106 Statement Executed: Rows Affected = 0
 107 echo DELETE FROM t1 WHERE f3 IS NOT NULL;
 108 Statement Executed: Rows Affected = 0
 109 echo SELECT * FROM t1;
 110 ---------------------------------------------------------
 111         t1.f1   t1.f2   t1.f3   t1.f4
 112 ---------------------------------------------------------
 113
 114 Statement Executed