dml134.sql

derby database source code.good for you.

AUTOCOMMIT OFF;

-- MODULE  DML134  

-- SQL Test Suite, V6.0, Interactive SQL, dml134.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION FLATER
   set schema FLATER;

--O   SELECT USER FROM HU.ECCO;
  VALUES USER;
-- 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);
-- PASS:0689 If table is created?

   COMMIT;

   CREATE TABLE CONDITION_CODES (
  CONDCODE INT NOT NULL UNIQUE,
  CONDTEXT VARCHAR (50) NOT NULL);
-- PASS:0689 If table is created?

   COMMIT;

   CREATE TABLE ITEM_CODES (
  ITEMCODE INT NOT NULL PRIMARY KEY,
  ITEMTEXT VARCHAR (50) NOT NULL);
-- PASS:0689 If table is created?

   COMMIT;

   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 );
-- 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                             '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO COST_CODES VALUES (
   0,
   RTRIM ('Expensive                                         '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO COST_CODES VALUES (
   1,
   RTRIM ('Absurdly expensive                                '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO COST_CODES VALUES (
   2,
   RTRIM ('Outrageously expensive                            '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO COST_CODES VALUES (
   3,
   RTRIM ('Robbery; a complete and total rip-off             '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO CONDITION_CODES VALUES (
   NULL,
   RTRIM ('Unknown                                           '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO CONDITION_CODES VALUES (
   1,
   RTRIM ('Slightly used                                     '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO CONDITION_CODES VALUES (
   2,
   RTRIM ('Returned as defective                             '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO CONDITION_CODES VALUES (
   3,
   RTRIM ('Visibly damaged (no returns)                      '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO ITEM_CODES VALUES (
   1,
   RTRIM ('Lousy excuse for a tape deck                      '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO ITEM_CODES VALUES (
   3,
   RTRIM ('World''s worst VCR                                 '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO ITEM_CODES VALUES (
   4,
   RTRIM ('Irreparable intermittent CD player                '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO ITEM_CODES VALUES (
   7,
   RTRIM ('Self-destruct VGA monitor w/ critical need detect '));
-- PASS:0689 If 1 row is inserted?

   INSERT INTO INVENTORY VALUES (3, NULL, 4);
-- PASS:0689 If 1 row is inserted?

   INSERT INTO INVENTORY VALUES (1, 2, 3);
-- PASS:0689 If 1 row is inserted?

   INSERT INTO INVENTORY VALUES (2, 3, 7);
-- PASS:0689 If 1 row is inserted?

   INSERT INTO INVENTORY VALUES (0, 3, 1);
-- PASS:0689 If 1 row is inserted?

   INSERT INTO INVENTORY VALUES (3, 1, 7);
-- 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;

--O   DROP TABLE INVENTORY CASCADE;
   DROP TABLE INVENTORY ;
-- PASS:0689 If table and 3 views are dropped?

   COMMIT;

--O   DROP TABLE COST_CODES CASCADE;
   DROP TABLE COST_CODES ;
-- PASS:0689 If table is dropped?

   COMMIT;

--O   DROP TABLE CONDITION_CODES CASCADE;
   DROP TABLE CONDITION_CODES ;
-- PASS:0689 If table is dropped?

   COMMIT;

--O   DROP TABLE ITEM_CODES CASCADE;
   DROP TABLE ITEM_CODES ;
-- PASS:0689 If table is dropped?

   COMMIT;

-- 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);