ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML061

-- SQL Test Suite, V6.0, Interactive SQL, dml061.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION HU
   set schema HU;
0 rows inserted/updated/deleted
ij> --0   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
HU                                                                                                                              
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print

-- TEST:0269 BETWEEN value expressions in wrong order!

            SELECT COUNT(*)
            FROM WORKS
            WHERE HOURS BETWEEN 80 AND 40;
1          
-----------
0          
ij> -- PASS:0269 If count = 0   ?

-- setup   
         INSERT INTO WORKS
            VALUES('E6','P6',-60);
1 row inserted/updated/deleted
ij> SELECT COUNT(*)
            FROM WORKS
            WHERE HOURS BETWEEN -40 AND -80;
1          
-----------
0          
ij> -- PASS:0269 If count = 0?

            SELECT COUNT(*)
            FROM WORKS
            WHERE HOURS BETWEEN -80 AND -40;
1          
-----------
1          
ij> -- PASS:0269 If count = 1?

-- restore
    ROLLBACK WORK;
ij> -- END TEST >>> 0269 <<< END TEST

-- ****************************************************************


-- TEST:0270 BETWEEN approximate and exact numeric values!

            SELECT COUNT(*)
            FROM WORKS
            WHERE HOURS BETWEEN 11.999 AND 12 OR
                  HOURS BETWEEN 19.999 AND 2.001E1;
1          
-----------
6          
ij> -- PASS:0270 If count = 6?

-- END TEST >>> 0270 <<< END TEST

-- ****************************************************************


-- TEST:0271 COUNT(*) with Cartesian product subset !

            SELECT COUNT(*)
            FROM WORKS,STAFF
            WHERE WORKS.EMPNUM = 'E1';
1          
-----------
30         
ij> -- PASS:0271 If count = 30?

-- END TEST >>> 0271 <<< END TEST

-- ****************************************************************


-- TEST:0272 Statement rollback for integrity!
        UPDATE WORKS
        SET EMPNUM = 'E7'
        WHERE EMPNUM = 'E1' OR EMPNUM = 'E4';
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'WORKSC1' defined on 'WORKS'.
ij> -- PASS:0272 If ERROR, unique constraint, 0 rows updated?

        INSERT INTO WORKS 
        SELECT 'E3',PNUM,17 FROM PROJ;
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'WORKSC1' defined on 'WORKS'.
ij> -- PASS:0272 If ERROR, unique constraint, 0 rows inserted?

--0        UPDATE V_WORKS1
--0        SET HOURS = HOURS - 9;
-- PASS:0272 If ERROR, view check constraint, 0 rows updated?

        SELECT COUNT(*)
        FROM WORKS
        WHERE EMPNUM = 'E7' OR HOURS = 31 OR HOURS = 17;
1          
-----------
0          
ij> -- PASS:0272 If count = 0?

-- restore
       ROLLBACK WORK;
ij> -- END TEST >>> 0272 <<< END TEST

-- ****************************************************************


-- TEST:0273 SUM, MAX, MIN = NULL for empty arguments  !

            UPDATE WORKS
            SET HOURS = NULL;
12 rows inserted/updated/deleted
ij> -- PASS:0273 If 12 rows updated?

            SELECT SUM(HOURS),MAX(HOURS),MIN(HOURS),MIN(EMPNUM)
                FROM WORKS;
1          |2     |3     |4   
------------------------------
NULL       |NULL  |NULL  |E1  
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0273 If 1 row is selected?
-- PASS:0273 If SUM(HOURS), MAX(HOURS), and MIN(HOURS) are NULL?

-- restore
    ROLLBACK WORK;
ij> -- END TEST >>> 0273 <<< END TEST

-- ****************************************************************


-- TEST:0277 Computation with NULL value specification!

          UPDATE WORKS
          SET HOURS = NULL  WHERE EMPNUM = 'E1';
6 rows inserted/updated/deleted
ij> -- PASS:0277 If 6 rows are updated?
 
          UPDATE WORKS
          SET HOURS = HOURS - (3 + -17);
12 rows inserted/updated/deleted
ij> -- PASS:0277 If 12 rows are updated?
 
          UPDATE WORKS
          SET HOURS = 3 / -17 * HOURS;
12 rows inserted/updated/deleted
ij> -- PASS:0277 If 12 rows are updated?
  
          UPDATE WORKS
          SET HOURS = HOURS + 5;
12 rows inserted/updated/deleted
ij> -- PASS:0277 If 12 rows are updated?
  
          SELECT COUNT(*)
          FROM WORKS
          WHERE HOURS IS NULL;
1          
-----------
6          
ij> -- PASS:0277 If count = 6?

-- restore 
    ROLLBACK WORK;
ij> -- END TEST >>> 0277 <<< END TEST

-- ****************************************************************


-- TEST:0278 IN value list with USER, literal, variable spec.!

          UPDATE STAFF
          SET EMPNAME = 'HU'
          WHERE EMPNAME = 'Ed';
1 row inserted/updated/deleted
ij> -- PASS:0278 If 1 row is updated?

          SELECT COUNT(*)
          FROM STAFF
          WHERE EMPNAME IN (CAST(USER AS VARCHAR(128)),'Betty','Carmen');
1          
-----------
3          
ij> -- PASS:0278 If count = 3?

-- restore
    ROLLBACK WORK;
ij> -- END TEST >>> 0278 <<< END TEST

-- *************************************************////END-OF-MODULE
;
ij>