dml134.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML134  
-- SQL Test Suite, V6.0, Interactive SQL, dml134.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O   ROLLBACK WORK;
-- date_time print
-- NO_TEST:0688 INFO_SCHEM:  Dynamic changes are visible!
-- Testing dynamic SQL
-- *********************************************
-- TEST:0689 Many Trans SQL features #1:  inventory system!
   CREATE TABLE COST_CODES (
  COSTCODE INT NOT NULL UNIQUE,
  COSTTEXT VARCHAR (50) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
   COMMIT;
ij> CREATE TABLE CONDITION_CODES (
  CONDCODE INT NOT NULL UNIQUE,
  CONDTEXT VARCHAR (50) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
   COMMIT;
ij> CREATE TABLE ITEM_CODES (
  ITEMCODE INT NOT NULL PRIMARY KEY,
  ITEMTEXT VARCHAR (50) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
   COMMIT;
ij> CREATE TABLE INVENTORY (
--O  COSTCODE INT REFERENCES COST_CODES (COSTCODE),
--O  CONDCODE INT REFERENCES CONDITION_CODES (CONDCODE),
--O  ITEMCODE INT REFERENCES ITEM_CODES);
  COSTCODE INT ,
  CONDCODE INT ,
  ITEMCODE INT );
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is created?
--O   COMMIT;
--O   CREATE VIEW COMPLETES AS
--O  SELECT ITEMTEXT, CONDTEXT, COSTTEXT
--O    FROM INVENTORY NATURAL JOIN COST_CODES
--O                   NATURAL JOIN CONDITION_CODES
--O                   NATURAL JOIN ITEM_CODES;
-- PASS:0689 If view is created?
--O   COMMIT;
--O   CREATE VIEW INCOMPLETES AS
--O  SELECT ITEMTEXT, CONDTEXT, COSTTEXT
--O    FROM INVENTORY, COST_CODES, CONDITION_CODES, ITEM_CODES
--O      WHERE INVENTORY.ITEMCODE = ITEM_CODES.ITEMCODE
--O        AND ((INVENTORY.CONDCODE = CONDITION_CODES.CONDCODE
--O              AND INVENTORY.COSTCODE IS NULL
--O              AND COST_CODES.COSTCODE IS NULL)
--O          OR (INVENTORY.COSTCODE = COST_CODES.COSTCODE
--O              AND INVENTORY.CONDCODE IS NULL
--O              AND CONDITION_CODES.CONDCODE IS NULL));
-- PASS:0689 If view is created?
--O   COMMIT;
--O   CREATE VIEW VERBOSE_INV AS
--O  SELECT * FROM COMPLETES UNION SELECT * FROM INCOMPLETES;
-- PASS:0689 If view is created?
--O   COMMIT;
   INSERT INTO COST_CODES VALUES (
   NULL,
   RTRIM ('No cost code assigned                             '));
ERROR 23502: Column 'COSTCODE'  cannot accept a NULL value.
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO COST_CODES VALUES (
   0,
   RTRIM ('Expensive                                         '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO COST_CODES VALUES (
   1,
   RTRIM ('Absurdly expensive                                '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO COST_CODES VALUES (
   2,
   RTRIM ('Outrageously expensive                            '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO COST_CODES VALUES (
   3,
   RTRIM ('Robbery; a complete and total rip-off             '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO CONDITION_CODES VALUES (
   NULL,
   RTRIM ('Unknown                                           '));
ERROR 23502: Column 'CONDCODE'  cannot accept a NULL value.
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO CONDITION_CODES VALUES (
   1,
   RTRIM ('Slightly used                                     '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO CONDITION_CODES VALUES (
   2,
   RTRIM ('Returned as defective                             '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO CONDITION_CODES VALUES (
   3,
   RTRIM ('Visibly damaged (no returns)                      '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO ITEM_CODES VALUES (
   1,
   RTRIM ('Lousy excuse for a tape deck                      '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO ITEM_CODES VALUES (
   3,
   RTRIM ('World''s worst VCR                                 '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO ITEM_CODES VALUES (
   4,
   RTRIM ('Irreparable intermittent CD player                '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO ITEM_CODES VALUES (
   7,
   RTRIM ('Self-destruct VGA monitor w/ critical need detect '));
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO INVENTORY VALUES (3, NULL, 4);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO INVENTORY VALUES (1, 2, 3);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO INVENTORY VALUES (2, 3, 7);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO INVENTORY VALUES (0, 3, 1);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
   INSERT INTO INVENTORY VALUES (3, 1, 7);
1 row inserted/updated/deleted
ij> -- PASS:0689 If 1 row is inserted?
--O   SELECT COUNT(*) FROM VERBOSE_INV;
-- PASS:0689 If count = 5?
--O   SELECT COUNT(*) FROM INCOMPLETES;
-- PASS:0689 If count = 1?
--O   SELECT COUNT(*) FROM COMPLETES;
-- PASS:0689 If count = 4?
--O   SELECT COUNT(*) FROM VERBOSE_INV
--O  WHERE ITEMTEXT = 'Irreparable intermittent CD player'
--O  AND CONDTEXT = 'Unknown'
--O  AND COSTTEXT = 'Robbery; a complete and total rip-off';
-- PASS:0689 If count = 1?
--O   SELECT COUNT(*) FROM VERBOSE_INV
--O  WHERE ITEMTEXT = 'Lousy excuse for a tape deck'
--O  AND CONDTEXT = 'Visibly damaged (no returns)'
--O  AND COSTTEXT = 'Expensive';
-- PASS:0689 If count = 1?
--O   SELECT COUNT(*) FROM VERBOSE_INV
--O  WHERE ITEMTEXT =
--O  'Self-destruct VGA monitor w/ critical need detect'
--O  AND CONDTEXT = 'Slightly used'
--O  AND COSTTEXT = 'Robbery; a complete and total rip-off';
-- PASS:0689 If count = 1?
--O   SELECT COUNT(*) FROM VERBOSE_INV
--O  WHERE ITEMTEXT =
--O  'Self-destruct VGA monitor w/ critical need detect'
--O  AND CONDTEXT = 'Visibly damaged (no returns)'
--O  AND COSTTEXT = 'Outrageously expensive';
-- PASS:0689 If count = 1?
--O   SELECT COUNT(*) FROM VERBOSE_INV
--O  WHERE ITEMTEXT = 'World''s worst VCR'
--O  AND CONDTEXT = 'Returned as defective'
--O  AND COSTTEXT = 'Absurdly expensive';
-- PASS:0689 If count = 1?
   COMMIT;
ij> --O   DROP TABLE INVENTORY CASCADE;
   DROP TABLE INVENTORY ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table and 3 views are dropped?
   COMMIT;
ij> --O   DROP TABLE COST_CODES CASCADE;
   DROP TABLE COST_CODES ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is dropped?
   COMMIT;
ij> --O   DROP TABLE CONDITION_CODES CASCADE;
   DROP TABLE CONDITION_CODES ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is dropped?
   COMMIT;
ij> --O   DROP TABLE ITEM_CODES CASCADE;
   DROP TABLE ITEM_CODES ;
0 rows inserted/updated/deleted
ij> -- PASS:0689 If table is dropped?
   COMMIT;
ij> -- END TEST >>> 0689 <<< END TEST
-- *********************************************
-- TEST:0690 Many Trans SQL features #2:  talk show schedule!
--O   CREATE TABLE PORGRAM (
--O  SEGNO    INT PRIMARY KEY,
--O  STARTS   TIME NOT NULL,
--O  LASTS    INTERVAL MINUTE TO SECOND NOT NULL,
--O  SEGMENT  VARCHAR (50));
-- PASS:0690 If table is created?
--O   COMMIT;
--O   CREATE VIEW GAPS AS
--O  SELECT * FROM PORGRAM AS OUTERR WHERE NOT EXISTS
--O    (SELECT * FROM PORGRAM AS INNERR WHERE OUTERR.STARTS
--O    + OUTERR.LASTS = INNERR.STARTS);