dml112.sql

derby database source code.good for you.

   SELECT MAX(NUMBRR) FROM CHANGGVIEW;
-- PASS:0625 If ERROR, column does not exist ?

   COMMIT WORK;

--O   DROP VIEW CHANGGVIEW CASCADE;
   DROP VIEW CHANGGVIEW ;
-- PASS:0625 If view is dropped?

   COMMIT WORK;

--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;
-- 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;
-- PASS:0625 If count = 4?

   COMMIT WORK;

--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 ;
-- PASS:0625 If table is dropped?

   COMMIT WORK;

--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));
-- PASS:0633 If table is created?

   COMMIT WORK;

   INSERT INTO WEIRDPAD (NAAM, SPONSOR) VALUES
     ('KATEBBBBBBBBBB', '000000000KEITH');
-- PASS:0633 If 1 row is inserted?

   INSERT INTO WEIRDPAD (NAAM, SPONSOR) VALUES
     ('    KEITH     ', 'XXXXKATEXXXXXX');
-- PASS:0633 If 1 row is inserted?

   SELECT LTRIM (RTRIM (SPONSOR,'X'),'X')
     FROM WEIRDPAD
     WHERE LTRIM (RTRIM (NAAM)) = 'KEITH';
-- PASS:0633 If 1 row selected with value KATE ? 

   SELECT LTRIM (SPONSOR, 'X') 
     FROM WEIRDPAD
     WHERE RTRIM (NAAM) = '    KEITH';
-- PASS:0633 If 1 row selected with value KATEXXXXXX ?

   SELECT LTRIM (SPONSOR, 'X') 
     FROM WEIRDPAD
     WHERE RTRIM (SPONSOR, 'X') = 'XXXXKATE';
-- 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');
-- 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, ' '), ' ');
-- PASS:0633 If count = 1?

   SELECT RTRIM (NAAM, 'BB')
     FROM WEIRDPAD WHERE NAAM LIKE 'KATE%';
-- 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';
-- PASS:0633 If 3 rows are inserted?

   SELECT COUNT(*) FROM WEIRDPAD;
-- PASS:0633 If count = 5?

   UPDATE WEIRDPAD
     SET SPONSOR = LTRIM (RTRIM (SPONSOR, 'X'), 'X'),
             NAAM = RTRIM (NAAM, 'B');
-- PASS:0633 If 5 rows are updated?

   SELECT COUNT(*) FROM WEIRDPAD
     WHERE NAAM = 'KATE' OR SPONSOR = 'KATE';
-- PASS:0633 If count = 2?

   DELETE FROM WEIRDPAD WHERE
     LTRIM('Kest', 'K') = LTRIM(SPONSOR, 'T');
-- PASS:0633 If 1 row is deleted?

   SELECT COUNT(*) FROM WEIRDPAD;
-- PASS:0633 If count = 4?

   UPDATE WEIRDPAD
      SET PADCHAR = '0'
     WHERE SPONSOR = '000000000KEITH'
        OR NAAM    = 'eale';
-- PASS:0633 If 3 rows are updated?

   UPDATE WEIRDPAD
      SET SPONSOR = NULL
     WHERE SPONSOR = 'Desig';
-- PASS:0633 If 1 row is updated?

   SELECT COUNT(*) FROM WEIRDPAD
     WHERE RTRIM (SPONSOR, PADCHAR) IS NULL;
-- PASS:0633 If count = 2?

   SELECT COUNT(*) FROM WEIRDPAD
     WHERE LTRIM (SPONSOR, PADCHAR) = 'KEITH';
-- PASS:0633 If count = 1?

   COMMIT WORK;

--0   DROP TABLE WEIRDPAD CASCADE;
   DROP TABLE WEIRDPAD;
-- PASS:0633 If table is dropped?

   COMMIT WORK;

-- END TEST >>> 0633 <<< END TEST

-- *************************************************////END-OF-MODULE