cdr027.out

derby database source code.good for you.

ij> 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 'xxxxGENERATED-IDxxxx' 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 'xxxxGENERATED-IDxxxx' 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>