ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- AUTOCOMMIT OFF; ij> -- MODULE CDR027 -- SQL Test Suite, V6.0, Interactive SQL, cdr027.sql -- 59-byte ID -- TEd Version # -- AUTHORIZATION SUN set schema SUN; 0 rows inserted/updated/deleted ij> --O SELECT USER FROM SUN.ECCO; VALUES USER; 1 -------------------------------------------------------------------------------------------------------------------------------- SUN ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment -- date_time print -- TEST:0446 Table CHECK constraint allows unknown (NULL)! DELETE FROM STAFF5; 1 row inserted/updated/deleted ij> INSERT INTO STAFF5 VALUES('E7','Mimi',NULL,'Miami'); 1 row inserted/updated/deleted ij> -- PASS:0446 If 1 row inserted? INSERT INTO STAFF5 VALUES('E8','Joe',NULL,'Boston'); 1 row inserted/updated/deleted ij> -- PASS:0446 If 1 row inserted? INSERT INTO STAFF5(EMPNUM) VALUES('E9'); 1 row inserted/updated/deleted ij> -- PASS:0446 If 1 row inserted? --O UPDATE STAFF UPDATE HU.STAFF SET GRADE = NULL WHERE EMPNUM = 'E1'; 1 row inserted/updated/deleted ij> -- PASS:0446 If 1 row updated? INSERT INTO STAFF5 SELECT * --O FROM STAFF; FROM HU.STAFF; 5 rows inserted/updated/deleted ij> -- PASS:0446 If 5 rows inserted? UPDATE STAFF5 SET GRADE = NULL WHERE EMPNUM = 'E2'; 1 row inserted/updated/deleted ij> -- PASS:0446 If 1 row updated? UPDATE STAFF5 SET GRADE = NULL WHERE EMPNUM = 'E4'; 1 row inserted/updated/deleted ij> -- PASS:0446 If 1 row updated? SELECT COUNT(*) FROM STAFF5; 1 ----------- 8 ij> -- PASS:0446 If count = 8? SELECT COUNT(*) FROM STAFF5 WHERE GRADE IS NULL; 1 ----------- 6 ij> -- PASS:0446 If count = 6? ROLLBACK WORK; ij> -- END TEST >>> 0446 <<< END TEST -- ********************************************* -- TEST:0447 NULLs with check constraint and check option! DELETE FROM STAFF6; 1 row inserted/updated/deleted ij> --O INSERT INTO STAFF6_WITH_GRADES --O VALUES('X1','Vicki',NULL,'Houston'); -- PASS:0447 If ERROR, view check constraint, 0 rows inserted? INSERT INTO STAFF6 VALUES('X2','Tina',NULL,'Orlando'); 1 row inserted/updated/deleted ij> -- PASS:0447 If 1 row inserted? --O SELECT COUNT(*) --O FROM STAFF6_WITH_GRADES; -- PASS:0447 If count = 0? SELECT COUNT(*) FROM STAFF6; 1 ----------- 1 ij> -- PASS:0447 If count = 1? SELECT EMPNAME FROM STAFF6 WHERE GRADE IS NULL; EMPNAME -------------------- Tina ij> -- PASS:0447 If EMPNAME is Tina? ROLLBACK WORK; ij> -- END TEST >>> 0447 <<< END TEST -- ********************************************* -- TEST:0448 PRIMARY KEY implies UNIQUE! DELETE FROM STAFF9; 1 row inserted/updated/deleted ij> INSERT INTO STAFF9(EMPNUM,EMPNAME) VALUES('D1','Muddley'); 1 row inserted/updated/deleted ij> -- PASS:0448 If 1 row inserted? INSERT INTO STAFF9(EMPNUM,EMPNAME) VALUES('D1','Muddley'); 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 'STAFF9_PK' defined on 'STAFF9'. ij> -- PASS:0448 If ERROR, unique constraint, 0 rows inserted? INSERT INTO STAFF9(EMPNUM,EMPNAME) VALUES('d1','Muddley'); 1 row inserted/updated/deleted ij> -- PASS:0448 If 1 row inserted? SELECT COUNT(*) FROM STAFF9; 1 ----------- 2 ij> -- PASS:0448 If count = 2? ROLLBACK WORK; ij> -- END TEST >>> 0448 <<< END TEST -- ********************************************* -- TEST:0449 Constraint definition is case sensitive! DELETE FROM STAFF9; 1 row inserted/updated/deleted ij> INSERT INTO STAFF9(EMPNUM,EMPNAME) VALUES('Z1','Tina'); ERROR 23513: The check constraint 'STAFF9_EMPNAME' was violated while performing an INSERT or UPDATE on table 'SUN.STAFF9'. ij> -- PASS:0449 If ERROR, check constraint, 0 rows inserted? INSERT INTO STAFF9(EMPNUM,EMPNAME) VALUES('Z2','tina'); 1 row inserted/updated/deleted ij> -- PASS:0449 If 1 row inserted? INSERT INTO STAFF9(EMPNUM,EMPNAME) VALUES('Z3','ANTHONY'); 1 row inserted/updated/deleted ij> -- PASS:0449 If 1 row inserted? SELECT COUNT(*) FROM STAFF9; 1 ----------- 2 ij> -- PASS:0449 If count = 2? ROLLBACK WORK; ij> -- END TEST >>> 0449 <<< END TEST -- ********************************************* -- TEST:0450 Referential integrity is case sensitive! INSERT INTO DEPT VALUES(11,'VOLLEYBALL','VICKI'); 1 row inserted/updated/deleted ij> -- PASS:0450 If 1 row inserted? INSERT INTO DEPT VALUES(10,'volleyball','vicki'); 1 row inserted/updated/deleted ij> -- PASS:0450 If 1 row inserted? -- NOTE:0450 insert lower case value of above. SELECT COUNT(*) FROM DEPT WHERE DNO = 10; 1 ----------- 1 ij> -- PASS:0450 If count = 1? --O INSERT INTO EMP --O VALUES(13,'MARY','Dancer',15,'VOLLEYBALL',010101); -- PASS:0450 If 1 row inserted? DELETE FROM DEPT WHERE DNO = 10; 1 row inserted/updated/deleted ij> -- PASS:0450 If 1 row deleted? --O UPDATE DEPT --O SET DNAME = 'EDUCATION' --O WHERE DNAME = 'Education'; -- PASS:0450 If RI ERROR, children exist, 0 rows updated? --O SELECT * FROM DEPT ORDER BY DNO; -- PASS:0450 If 5 rows selected? -- PASS:0450 If DNO values are 11, 12, 13, 14, 15? -- PASS:0450 If DNAME = 'Education' (not 'EDUCATION') for DNO = 14? --O INSERT INTO EMP --O VALUES(28,'BARBARA','Jogger',14,'EDUCATION',010101); -- PASS:0450 If RI ERROR, parent missing, 0 rows inserted? --O UPDATE EMP --O SET DNAME = 'PHYSICS' --O WHERE ENO = 25; -- PASS:0450 If RI ERROR, parent missing, 0 rows updated? --O SELECT ENO, ENAME, DNO, DNAME --O FROM EMP ORDER BY ENO; -- PASS:0450 If 8 rows selected? -- PASS:0450 If ENO values are 13, 21 through 27? -- PASS:0450 If DNAME = 'Physics' (not 'PHYSICS') for ENO = 25? ROLLBACK WORK; ij> -- END TEST >>> 0450 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>