dml026.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE DML026
-- SQL Test Suite, V6.0, Interactive SQL, dml026.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
   set schema HU;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
HU                                                                                                                              
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0118 Monadic arithmetic operator +!
      SELECT +MAX(DISTINCT HOURS)
              FROM WORKS;
1     
------
80    
ij> -- PASS:0118 If +MAX(DISTINCT HOURS) = 80?
-- END TEST >>> 0118 <<< END TEST
-- *********************************************************
-- TEST:0119 Monadic arithmetic operator -!
      SELECT -MAX(DISTINCT HOURS)
              FROM WORKS;
1     
------
-80   
ij> -- PASS:0119 If -MAX(DISTINCT HOURS) = -80?
-- END TEST >>> 0119 <<< END TEST
-- *********************************************************
-- TEST:0120 Value expression with NULL primary IS NULL!
-- setup
     INSERT INTO WORKS1
            SELECT * 
                 FROM WORKS;
12 rows inserted/updated/deleted
ij> -- PASS:0120 If 12 rows are inserted ?
-- setup
     INSERT INTO WORKS1
            VALUES('E9','P1',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0120 If 1 row is inserted?
      SELECT EMPNUM
           FROM WORKS1
           WHERE HOURS IS NULL;
EM&
---
E9 
ij> -- PASS:0120 If EMPNUM = 'E9'?
-- NOTE:0120 we insert into WORKS from WORKS1
-- setup
     INSERT INTO WORKS
            SELECT EMPNUM,'P9',20+HOURS
                 FROM WORKS1
                 WHERE EMPNUM='E9';
1 row inserted/updated/deleted
ij> -- PASS:0120 If 1 row is inserted?
--O      SELECT COUNT(*)
      SELECT *
           FROM WORKS
           WHERE EMPNUM='E9';
EM&|PN&|HOURS 
--------------
E9 |P9 |NULL  
ij> -- PASS:0120 If count = 1      ?
--O      SELECT COUNT(*)
      SELECT *
              FROM WORKS
              WHERE HOURS IS NULL;
EM&|PN&|HOURS 
--------------
E9 |P9 |NULL  
ij> -- PASS:0120 If count = 1 ?
-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0120 <<< END TEST
-- **********************************************************
-- TEST:0121 Dyadic operators +, -, *, /!
--O      SELECT COUNT(*)
      SELECT *
           FROM VTABLE;
COL1       |COL2       |COL3       |COL4       |COL5      
----------------------------------------------------------
10         |20         |30         |40         |10.50     
0          |1          |2          |3          |4.25      
100        |200        |300        |400        |500.01    
1000       |-2000      |3000       |NULL       |4000.00   
ij> -- PASS:0121 If count = 4 ?
      SELECT +COL1+COL2 - COL3*COL4/COL1
              FROM VTABLE
              WHERE COL1=10;
1          
-----------
-90        
ij> -- PASS:0121 If answer is -90?
-- END TEST >>> 0121 <<< END TEST
-- *********************************************************
-- TEST:0122 Divisor shall not be zero!
      SELECT COL2/COL1+COL3
              FROM VTABLE
              WHERE COL4=3;
1          
-----------
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0122 If ERROR Number not Divisible by Zero?
-- END TEST >>> 0122 <<< END TEST
-- **********************************************************
-- TEST:0123 Evaluation order of expression!
      SELECT (-COL2+COL1)*COL3 - COL3/COL1
              FROM VTABLE
              WHERE COL4 IS NULL;
1          
-----------
8999997    
ij> -- PASS:0123 If Answer is 8999997 (plus or minus 0.5)?
-- END TEST >>> 0123 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>