ij> AUTOCOMMIT OFF; ij> -- MODULE DML079 -- SQL Test Suite, V6.0, Interactive SQL, dml079.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:0451 UNIQUEness is case sensitive! UPDATE STAFF SET EMPNUM = 'e2' WHERE EMPNUM = 'E4'; 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row updated? INSERT INTO STAFF(EMPNUM) VALUES ('E1'); 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 'STAFF_UNIQUE' defined on 'STAFF'. ij> -- PASS:0451 If ERROR, unique constraint, 0 rows inserted? INSERT INTO STAFF(EMPNUM) VALUES ('e1'); 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row inserted? UPDATE STAFF SET EMPNUM = 'E1' WHERE EMPNUM = 'e1'; 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 'STAFF_UNIQUE' defined on 'STAFF'. ij> -- PASS:0451 If ERROR, unique constraint, 0 rows updated? SELECT * FROM STAFF; EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E1 |Alice |12 |Deale E2 |Betty |10 |Vienna E3 |Carmen |13 |Vienna e2 |Don |12 |Deale E5 |Ed |13 |Akron e1 |NULL |NULL |NULL ij> -- PASS:0451 If 6 rows are selected? -- PASS:0451 If EMPNUMs are 'e1','e2','E1','E2','E3','E5'? INSERT INTO WORKS (EMPNUM,PNUM) VALUES ('e1','p2'); 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row inserted? INSERT INTO WORKS (EMPNUM,PNUM) VALUES ('E1','p2'); 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row inserted? INSERT INTO WORKS (EMPNUM,PNUM) VALUES ('E1','P2'); 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:0451 If ERROR, unique constraint, 0 rows inserted? INSERT INTO WORKS (EMPNUM,PNUM) VALUES ('e1', 'P2'); 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row inserted? UPDATE WORKS SET EMPNUM = 'E1' WHERE PNUM = 'P5' AND 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:0451 If ERROR, unique constraint, 0 rows updated? UPDATE WORKS SET EMPNUM = 'e1' WHERE PNUM = 'P5' AND EMPNUM = 'E4'; 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row updated? UPDATE WORKS SET PNUM = 'P4' WHERE PNUM = 'P2' AND 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:0451 If ERROR, unique constraint, 0 rows updated? UPDATE WORKS SET PNUM = 'p4' WHERE PNUM = 'P2' AND EMPNUM = 'E4'; 1 row inserted/updated/deleted ij> -- PASS:0451 If 1 row updated? SELECT * FROM WORKS ORDER BY EMPNUM, PNUM; EM&|PN&|HOURS -------------- E1 |P1 |40 E1 |P2 |20 E1 |P3 |80 E1 |P4 |20 E1 |P5 |12 E1 |P6 |12 E1 |p2 |NULL E2 |P1 |40 E2 |P2 |80 E3 |P2 |20 E4 |P4 |40 E4 |p4 |20 e1 |P2 |NULL e1 |P5 |80 e1 |p2 |NULL ij> -- PASS:0451 If 15 rows are selected? -- PASS:0451 If EMPNUM/PNUM values include ? -- PASS:0451 e1/p2, E1/p2, e1/P2, e1/P5, E4/p4 ? -- PASS:0451 If no EMPNUM/PNUM values are duplicates ? ROLLBACK WORK; ij> -- END TEST >>> 0451 <<< END TEST -- ********************************************* -- TEST:0452 Order of precedence, left-to-right in UNION [ALL]! SELECT EMPNAME FROM STAFF UNION SELECT EMPNAME FROM STAFF UNION ALL SELECT EMPNAME FROM STAFF; EMPNAME -------------------- Alice Betty Carmen Don Ed Alice Betty Carmen Don Ed ij> -- PASS:0452 If 10 rows selected? SELECT EMPNAME FROM STAFF UNION ALL SELECT EMPNAME FROM STAFF UNION SELECT EMPNAME FROM STAFF; EMPNAME -------------------- Alice Betty Carmen Don Ed ij> -- PASS:0452 If 5 rows selected? -- END TEST >>> 0452 <<< END TEST -- ********************************************* -- TEST:0453 NULL with empty subquery of ALL, SOME, ANY! UPDATE PROJ SET CITY = NULL WHERE PNAME = 'IRM'; 1 row inserted/updated/deleted ij> --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY IS NULL; CITY --------------- NULL ij> -- PASS:0453 If count = 1? --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY = ALL (SELECT CITY FROM STAFF WHERE EMPNUM = 'E8'); CITY --------------- Deale Vienna Tampa Deale NULL Deale ij> -- PASS:0453 If count = 6? --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY <> ALL (SELECT CITY FROM STAFF WHERE EMPNUM = 'E8'); CITY --------------- Deale Vienna Tampa Deale NULL Deale ij> -- PASS:0453 If count = 6? --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY = ANY (SELECT CITY FROM STAFF WHERE EMPNUM = 'E8'); CITY --------------- ij> -- PASS:0453 If count = 0? --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY <> ANY (SELECT CITY FROM STAFF WHERE EMPNUM = 'E8'); CITY --------------- ij> -- PASS:0453 If count = 0? --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY = SOME (SELECT CITY FROM STAFF WHERE EMPNUM = 'E8'); CITY --------------- ij> -- PASS:0453 If count = 0? --O SELECT COUNT(*) SELECT CITY FROM PROJ WHERE CITY <> SOME (SELECT CITY FROM STAFF WHERE EMPNUM = 'E8'); CITY --------------- ij> -- PASS:0453 If count = 0? ROLLBACK WORK; ij> -- END TEST >>> 0453 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>