dml112.out

derby database source code.good for you.

ij> -- PASS:0625 If 1 row is inserted?
   INSERT INTO CHANGG VALUES ('QUEEG', 33);
1 row inserted/updated/deleted
ij> -- PASS:0625 If 1 row is inserted?
   INSERT INTO CHANGG VALUES ('BESSIE', 106);
1 row inserted/updated/deleted
ij> -- PASS:0625 If 1 row is inserted?
   SELECT COUNT(*) 
     FROM CHANGG WHERE DIVORCES IS NULL;
ERROR 42X04: Column 'DIVORCES' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'DIVORCES' is not a column in the target table.
ij> -- PASS:0625 If ERROR, column does not exist?
   COMMIT WORK;
ij> ALTER TABLE CHANGG ADD NUMBRR CHAR(11);
0 rows inserted/updated/deleted
ij> -- PASS:0625 If column is added?
   COMMIT WORK;
ij> SELECT MAX(AGE) FROM CHANGGVIEW;
1          
-----------
106        
ij> -- PASS:0625 If value is 106?
   SELECT MAX(NUMBRR) FROM CHANGGVIEW;
ERROR 42X04: Column 'NUMBRR' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'NUMBRR' is not a column in the target table.
ij> -- PASS:0625 If ERROR, column does not exist ?
   COMMIT WORK;
ij> --O   DROP VIEW CHANGGVIEW CASCADE;
   DROP VIEW CHANGGVIEW ;
0 rows inserted/updated/deleted
ij> -- PASS:0625 If view is dropped?
   COMMIT WORK;
ij> --O   ALTER TABLE CHANGG
--O     ADD COLUMN DIVORCES INT DEFAULT 0;
-- PASS:0625 If column is added?
--O   COMMIT WORK;
--O   SELECT COUNT(*) 
--O     FROM CHANGG WHERE NUMBRR IS NOT NULL
--O     OR DIVORCES <> 0;
-- PASS:0625 If count = 0?
--O   UPDATE CHANGG
--O     SET NUMBRR = '837-47-1847', DIVORCES = 3
--O     WHERE NAAM = 'RUDOLPH';
-- PASS:0625 If 1 row is updated?
--O   UPDATE CHANGG
--O     SET NUMBRR = '738-47-1847', DIVORCES = NULL
--O     WHERE NAAM = 'QUEEG';
-- PASS:0625 If 1 row is updated?
   DELETE FROM CHANGG
     WHERE NUMBRR IS NULL;
4 rows inserted/updated/deleted
ij> -- PASS:0625 If 2 rows are deleted?
--O   INSERT INTO CHANGG (NAAM, AGE, NUMBRR)
--O     VALUES ('GOOBER', 16, '000-10-0001');
-- PASS:0625 If 1 row is inserted?
--O   INSERT INTO CHANGG
--O     VALUES ('OLIVIA', 20, '111-11-1111', 0);
-- PASS:0625 If 1 row is inserted?
--O   SELECT AGE, NUMBRR, DIVORCES
--O     FROM CHANGG
--O     WHERE NAAM = 'RUDOLPH';
-- PASS:0625 If 1 row selected with values 54, 837-47-1847, 3 ?
--O   SELECT AGE, NUMBRR, DIVORCES
--O     FROM CHANGG
--O     WHERE NAAM = 'QUEEG';
-- PASS:0625 If 1 row selected with values 33, 738-47-1847, NULL ?
--O   SELECT AGE, NUMBRR, DIVORCES
--O     FROM CHANGG
--O     WHERE NAAM = 'GOOBER';
-- PASS:0625 If 1 row selected with values 16, 000-10-0001, 0 ?
--O   SELECT AGE, NUMBRR, DIVORCES
--O     FROM CHANGG
--O     WHERE NAAM = 'OLIVIA';
-- PASS:0625 If 1 row selected with values 20, 111-11-1111, 0 ?
   SELECT COUNT(*) FROM CHANGG;
1          
-----------
0          
ij> -- PASS:0625 If count = 4?
   COMMIT WORK;
ij> --O   ALTER TABLE CHANGG DROP AGE CASCADE;
-- PASS:0625 If column is dropped?
--O   COMMIT WORK;
--O   ALTER TABLE CHANGG DROP COLUMN DIVORCES RESTRICT;
-- PASS:0625 If column is dropped?
--O   COMMIT WORK;
--O   SELECT COUNT(*) 
--O     FROM CHANGG WHERE AGE > 30;
-- PASS:0625 If ERROR, column does not exist?
--O   SELECT COUNT(*) 
--O     FROM CHANGG WHERE DIVORCES IS NULL;
-- PASS:0625 If ERROR, column does not exist?
--O   SELECT NAAM 
--O     FROM CHANGG
--O     WHERE NUMBRR LIKE '%000%';
-- PASS:0625 If 1 row selected with value GOOBER ?
--O   COMMIT WORK;
--O   CREATE TABLE REFERENCE_CHANGG (
--O    NAAM CHAR (14) NOT NULL PRIMARY KEY
--O    REFERENCES CHANGG);
-- PASS:0625 If table is created?
--O   COMMIT WORK;
--O   INSERT INTO REFERENCE_CHANGG VALUES ('NO SUCH NAAM');
-- PASS:0625 If RI ERROR, parent missing, 0 rows inserted?
--O   COMMIT WORK;
--O   ALTER TABLE CHANGG DROP NAAM RESTRICT;
-- PASS:0625 If ERROR, referential constraint exists?
--O   COMMIT WORK;
--O   ALTER TABLE CHANGG DROP NAAM CASCADE;
-- PASS:0625 If column is dropped?
--O   COMMIT WORK;
--O   INSERT INTO REFERENCE_CHANGG VALUES ('NO SUCH NAAM');
-- PASS:0625 If 1 row is inserted?
--O   COMMIT WORK;
--O   ALTER TABLE CHANGG DROP NUMBRR RESTRICT;
-- PASS:0625 If ERROR, last column may not be dropped?
--O   COMMIT WORK;
--O   DROP TABLE CHANGG CASCADE;
   DROP TABLE CHANGG ;
0 rows inserted/updated/deleted
ij> -- PASS:0625 If table is dropped?
   COMMIT WORK;
ij> --O   DROP TABLE REFERENCE_CHANGG CASCADE;
-- PASS:0625 If table is dropped?
--O   COMMIT WORK;
-- END TEST >>> 0625 <<< END TEST
-- *********************************************
-- TEST:0631 Datetimes in a <default clause>!
--O   CREATE TABLE OBITUARIES (
--O    NAAM CHAR (14) NOT NULL PRIMARY KEY,
--O    BORN DATE DEFAULT DATE( '1880-01-01'),
--O    DIED DATE DEFAULT CURRENT_DATE,
--O    ENTERED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--O    TESTING1 DATE,
--O    TESTING2 TIMESTAMP);
-- PASS:0631 If table is created?
--O   COMMIT WORK;
--O   CREATE TABLE BIRTHS (
--O   NAAM CHAR (14) NOT NULL PRIMARY KEY,
--O    CHECKIN TIME (0)
--O        DEFAULT TIME( '00:00:00'),
--O    LABOR INTERVAL HOUR
--O        DEFAULT INTERVAL '4' HOUR,
--O    CHECKOUT TIME
--O        DEFAULT CURRENT_TIME,
--O    TESTING TIME);
-- PASS:0631 If table is created?
--O   COMMIT WORK;
--O   INSERT INTO OBITUARIES (NAAM, TESTING1, TESTING2)
--O     VALUES ('KEITH', CURRENT_DATE, CURRENT_TIMESTAMP);
-- PASS:0631 If 1 row is inserted?
--O   INSERT INTO BIRTHS (NAAM, TESTING)
--O     VALUES ('BJORN', CURRENT_TIME);
-- PASS:0631 If 1 row is inserted?
--O   SELECT HOUR( CHECKIN) +
--O                MINUTE( CHECKIN) +
--O                SECOND( CHECKIN)
--O   FROM BIRTHS;
-- PASS:0631 If 1 row selected with value 0?
--O   SELECT HOUR( LABOR) FROM BIRTHS;
-- PASS:0631 If 1 row selected with value 4?
--O   SELECT COUNT (*) FROM BIRTHS
--O     WHERE TESTING <> CHECKOUT OR CHECKOUT IS NULL;
-- PASS:0631 If count = 0?
--O   SELECT COUNT (*) FROM OBITUARIES
--O     WHERE BORN <> DATE( '1880-01-01')
--O     OR BORN IS NULL
--O     OR DIED <> TESTING1
--O     OR DIED IS NULL
--O     OR ENTERED <> TESTING2
--O     OR ENTERED IS NULL;
-- PASS:0631 If count = 0?
--O   COMMIT WORK;
--O   DROP TABLE BIRTHS CASCADE;
-- PASS:0631 If table is dropped?
--O   COMMIT WORK;
--O   DROP TABLE OBITUARIES CASCADE;
-- PASS:0631 If table is dropped?
--O   COMMIT WORK;
-- END TEST >>> 0631 <<< END TEST
-- *********************************************
-- TEST:0633 TRIM function!
   CREATE TABLE WEIRDPAD (
    NAAM CHAR (14),
    SPONSOR CHAR (14),
    PADCHAR CHAR (1));
0 rows inserted/updated/deleted
ij> -- PASS:0633 If table is created?
   COMMIT WORK;
ij> INSERT INTO WEIRDPAD (NAAM, SPONSOR) VALUES
     ('KATEBBBBBBBBBB', '000000000KEITH');
1 row inserted/updated/deleted
ij> -- PASS:0633 If 1 row is inserted?
   INSERT INTO WEIRDPAD (NAAM, SPONSOR) VALUES
     ('    KEITH     ', 'XXXXKATEXXXXXX');
1 row inserted/updated/deleted
ij> -- PASS:0633 If 1 row is inserted?
   SELECT LTRIM (RTRIM (SPONSOR,'X'),'X')
     FROM WEIRDPAD
     WHERE LTRIM (RTRIM (NAAM)) = 'KEITH';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 32.
ij> -- PASS:0633 If 1 row selected with value KATE ? 
   SELECT LTRIM (SPONSOR, 'X') 
     FROM WEIRDPAD
     WHERE RTRIM (NAAM) = '    KEITH';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 25.
ij> -- PASS:0633 If 1 row selected with value KATEXXXXXX ?
   SELECT LTRIM (SPONSOR, 'X') 
     FROM WEIRDPAD
     WHERE RTRIM (SPONSOR, 'X') = 'XXXXKATE';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 25.
ij> -- PASS:0633 If 1 row selected with value KATEXXXXXX ?
   SELECT LTRIM (B.NAAM)  FROM WEIRDPAD A,
     WEIRDPAD B WHERE RTRIM (LTRIM (A.NAAM, 'B'),'B')
                    = RTRIM (LTRIM (B.SPONSOR, 'X'),'X');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 43.
ij> -- PASS:0633 If 1 row selected with value KEITH ?
   SELECT COUNT(*) FROM WEIRDPAD A,
     WEIRDPAD B WHERE LTRIM (A.SPONSOR, '0')
                    = RTRIM (LTRIM (B.NAAM, ' '), ' ');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 39.
ij> -- PASS:0633 If count = 1?
   SELECT RTRIM (NAAM, 'BB')
     FROM WEIRDPAD WHERE NAAM LIKE 'KATE%';
ERROR 42X01: Syntax error: Encountered "," at line 3, column 22.
ij> -- PASS:0633 If ERROR, length of trim character must be 1 ?
   INSERT INTO WEIRDPAD (NAAM, SPONSOR)
     SELECT DISTINCT LTRIM (HU.STAFF.CITY, 'D'), 
                     RTRIM (PTYPE, 'n')
     FROM HU.STAFF, HU.PROJ 
     WHERE EMPNAME = 'Alice';
ERROR 42X01: Syntax error: Encountered "," at line 4, column 42.
ij> -- PASS:0633 If 3 rows are inserted?
   SELECT COUNT(*) FROM WEIRDPAD;
1          
-----------
2          
ij> -- PASS:0633 If count = 5?
   UPDATE WEIRDPAD
     SET SPONSOR = LTRIM (RTRIM (SPONSOR, 'X'), 'X'),
             NAAM = RTRIM (NAAM, 'B');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 41.
ij> -- PASS:0633 If 5 rows are updated?
   SELECT COUNT(*) FROM WEIRDPAD
     WHERE NAAM = 'KATE' OR SPONSOR = 'KATE';
1          
-----------
0          
ij> -- PASS:0633 If count = 2?
   DELETE FROM WEIRDPAD WHERE
     LTRIM('Kest', 'K') = LTRIM(SPONSOR, 'T');
ERROR 42X01: Syntax error: Encountered "," at line 4, column 18.
ij> -- PASS:0633 If 1 row is deleted?
   SELECT COUNT(*) FROM WEIRDPAD;
1          
-----------
2          
ij> -- PASS:0633 If count = 4?
   UPDATE WEIRDPAD
      SET PADCHAR = '0'
     WHERE SPONSOR = '000000000KEITH'
        OR NAAM    = 'eale';
1 row inserted/updated/deleted
ij> -- PASS:0633 If 3 rows are updated?
   UPDATE WEIRDPAD
      SET SPONSOR = NULL
     WHERE SPONSOR = 'Desig';
0 rows inserted/updated/deleted
ij> -- PASS:0633 If 1 row is updated?
   SELECT COUNT(*) FROM WEIRDPAD
     WHERE RTRIM (SPONSOR, PADCHAR) IS NULL;
ERROR 42X01: Syntax error: Encountered "," at line 4, column 26.
ij> -- PASS:0633 If count = 2?
   SELECT COUNT(*) FROM WEIRDPAD
     WHERE LTRIM (SPONSOR, PADCHAR) = 'KEITH';
ERROR 42X01: Syntax error: Encountered "," at line 4, column 26.
ij> -- PASS:0633 If count = 1?
   COMMIT WORK;
ij> --0   DROP TABLE WEIRDPAD CASCADE;
   DROP TABLE WEIRDPAD;
0 rows inserted/updated/deleted
ij> -- PASS:0633 If table is dropped?
   COMMIT WORK;
ij> -- END TEST >>> 0633 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>