| 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> |