dml112.sql

derby database source code.good for you.

-- LTRIM(string, trimSet)/RTRIM(string, trimSet) is not supported anymore. Mastering the
-- output with errors for now. We may implement our own LTRIM_TRIMSET()/RTRIM_TRIMSET()
-- functions for testing only in the future and replace usages of LTRIM/RTRIM here.

AUTOCOMMIT OFF;

-- MODULE  DML112  

-- SQL Test Suite, V6.0, Interactive SQL, dml112.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
   ROLLBACK WORK;

-- date_time print

-- TEST:0621 DATETIME NULLs!

   CREATE TABLE MERCH (
     ITEMKEY INT,
     ORDERED DATE,
     RDATE DATE,
     RTIME TIME,
     SOLD TIMESTAMP);
-- PASS:0621 If table is created?

   COMMIT WORK;

--O   CREATE TABLE TURNAROUND (
--O     ITEMKEY INT,
--O     MWAIT INTERVAL MONTH,
--O     DWAIT INTERVAL DAY TO HOUR);
-- PASS:0621 If table is created?

--O   COMMIT WORK;

--O   CREATE VIEW INVENTORY AS
--O     SELECT MERCH.ITEMKEY AS ITEMKEY, ORDERED,
--O     MWAIT, DWAIT FROM MERCH, TURNAROUND COR1 WHERE RDATE
--O     IS NOT NULL AND SOLD IS NULL AND
--O     MERCH.ITEMKEY = COR1.ITEMKEY
--O            UNION
--O     SELECT ITEMKEY, ORDERED,
--O     CAST (NULL AS INTERVAL MONTH),
--O     CAST (NULL AS INTERVAL DAY TO HOUR) FROM
--O     MERCH WHERE RDATE IS NOT NULL AND SOLD IS NULL
--O     AND MERCH.ITEMKEY NOT IN (SELECT ITEMKEY
--O     FROM TURNAROUND);
-- PASS:0621 If view is created?

--O   COMMIT WORK;

   INSERT INTO MERCH VALUES (0, DATE( '1993-11-23'), NULL, NULL, NULL);
-- PASS:0621 If 1 row is inserted?

   INSERT INTO MERCH VALUES (1, DATE( '1993-12-10'), DATE( '1994-01-03'),
          CAST (NULL AS TIME), NULL);
-- PASS:0621 If 1 row is inserted?

   INSERT INTO MERCH VALUES (2, DATE( '1993-12-11'), NULL,
--O          NULL, CAST ('TIMESTAMP ''1993-12-11 13:00:00''' AS TIMESTAMP));
          NULL, TIMESTAMP( '1993-12-11 13:00:00' ));
-- PASS:0621 If 1 row is inserted?

   INSERT INTO MERCH VALUES (4, DATE( '1993-01-26'), DATE( '1993-01-27'),
          NULL, NULL);
-- PASS:0621 If 1 row is inserted?

--O   INSERT INTO TURNAROUND VALUES (2, INTERVAL '1' MONTH, 
--O          INTERVAL '20:0' DAY TO HOUR);
-- PASS:0621 If 1 row is inserted?

--O   INSERT INTO TURNAROUND VALUES (5, INTERVAL '5' MONTH,
--O          CAST (NULL AS INTERVAL DAY TO HOUR));
-- PASS:0621 If 1 row is inserted?

--O   INSERT INTO TURNAROUND VALUES (6, INTERVAL '2' MONTH, NULL);
-- PASS:0621 If 1 row is inserted?

--O   SELECT COUNT(*) FROM
--O     MERCH A, MERCH B WHERE A.SOLD = B.SOLD;
-- PASS:0621 If count = 1?

--O   SELECT COUNT(*) FROM
--O     MERCH A, MERCH B WHERE A.RTIME = B.RTIME;
-- PASS:0621 If count = 0?

--O   SELECT COUNT(*) FROM
--O     MERCH WHERE RDATE IS NULL;
-- PASS:0621 If count = 2?

--O   SELECT COUNT(*) FROM
--O     TURNAROUND WHERE DWAIT IS NOT NULL;
-- PASS:0621 If count = 1?

--O   SELECT DAY( RDATE)
--O     FROM MERCH, TURNAROUND WHERE MERCH.ITEMKEY =
--O     TURNAROUND.ITEMKEY;
-- PASS:0621 If 1 row selected and value is NULL?

   SELECT ITEMKEY FROM MERCH WHERE SOLD IS NOT NULL;
-- PASS:0621 If 1 row selected and ITEMKEY is 2?

--O   SELECT HOUR( AVG (DWAIT))
--O      FROM MERCH, TURNAROUND WHERE
--O      MERCH.ITEMKEY = TURNAROUND.ITEMKEY OR
--O      TURNAROUND.ITEMKEY NOT IN
--O        (SELECT ITEMKEY FROM MERCH);
-- PASS:0621 If 1 row selected and value is 0?

--O   SELECT COUNT(*)
--O     FROM INVENTORY WHERE MWAIT IS NULL
--O     AND DWAIT IS NULL;
-- PASS:0621 If count = 2?

   COMMIT WORK;

--O   DROP TABLE MERCH CASCADE;
   DROP TABLE MERCH ;
-- PASS:0621 If table is dropped?

   COMMIT WORK;

--O   DROP TABLE TURNAROUND CASCADE;
-- PASS:0621 If table is dropped?

--O   COMMIT WORK;

-- END TEST >>> 0621 <<< END TEST

-- *********************************************

-- TEST:0623 OUTER JOINs with NULLs and empty tables!


   CREATE TABLE JNULL1 (C1 INT, C2 INT);
-- PASS:0623 If table is created?

   COMMIT WORK;

   CREATE TABLE JNULL2 (D1 INT, D2 INT);
-- PASS:0623 If table is created?

   COMMIT WORK;

   CREATE VIEW JNULL3 AS
     SELECT C1, D1, D2 FROM JNULL1 LEFT OUTER JOIN JNULL2
     ON C2 = D2;
-- PASS:0623 If view is created?

   COMMIT WORK;

   CREATE VIEW JNULL4 AS
     SELECT D1, D2 AS C2 FROM JNULL2;
-- PASS:0623 If view is created?

   COMMIT WORK;

   CREATE VIEW JNULL5 AS
     SELECT C1, D1, JNULL1.C2 FROM JNULL1 RIGHT OUTER JOIN JNULL4
     ON (JNULL1.C2 = JNULL4.C2);
-- PASS:0623 If view is created?

   COMMIT WORK;

   CREATE VIEW JNULL6 (C1, C2, D1, D2) AS
     SELECT * FROM JNULL1 LEFT OUTER JOIN JNULL4
     ON (JNULL1.C2 = JNULL4.C2);
-- PASS:0623 If view is created?

   COMMIT WORK;

   INSERT INTO JNULL1 VALUES (NULL, NULL);
-- PASS:0623 If 1 row is inserted?

   INSERT INTO JNULL1 VALUES (1, NULL);
-- PASS:0623 If 1 row is inserted?

   INSERT INTO JNULL1 VALUES (NULL, 1);
-- PASS:0623 If 1 row is inserted?

   INSERT INTO JNULL1 VALUES (1, 1);
-- PASS:0623 If 1 row is inserted?

   INSERT INTO JNULL1 VALUES (2, 2);
-- PASS:0623 If 1 row is inserted?

   SELECT COUNT(*) FROM JNULL3;
-- PASS:0623 If count = 5?

   SELECT COUNT(*) FROM JNULL3
     WHERE D2 IS NOT NULL OR D1 IS NOT NULL;
-- PASS:0623 If count = 0?

   SELECT COUNT(*) FROM JNULL5;
----     ON (C2);
----     SELECT D1, D2 AS C2 FROM JNULL2;
-- PASS:0623 If count = 0?

   SELECT COUNT(*) FROM JNULL6
     WHERE C2 IS NOT NULL;
-- PASS:0623 If count = 3?

   INSERT INTO JNULL2
     SELECT * FROM JNULL1;
-- PASS:0623 If 5 rows are inserted?

   UPDATE JNULL2
     SET D2 = 1 WHERE D2 = 2;
-- PASS:0623 If 1 row is updated?

   SELECT COUNT(*) FROM JNULL3;
-- PASS:0623 If count = 9?

   SELECT COUNT(*) 
     FROM JNULL3 WHERE C1 IS NULL;
-- PASS:0623 If count = 4?

   SELECT COUNT(*) 
     FROM JNULL3 WHERE D1 IS NULL;
-- PASS:0623 If count = 5?

   SELECT COUNT(*) 
     FROM JNULL3 WHERE D2 IS NULL;
-- PASS:0623 If count = 3?

   SELECT AVG(D1) * 10 
     FROM JNULL3;
-- PASS:0623 If value is 15 (approximately)?

   SELECT COUNT(*) 
     FROM JNULL6
      WHERE C2 = 1;
-- PASS:0623 If count = 6?

   SELECT COUNT(*) 
     FROM JNULL6
      WHERE C2 IS NULL;
-- PASS:0623 If count = 2?

   SELECT COUNT(*) 
     FROM JNULL6
      WHERE C2 = C1
        AND D1 IS NULL;
-- PASS:0623 If count = 2?

   COMMIT WORK;

--O   DROP TABLE JNULL1 CASCADE;
   DROP VIEW JNULL3 ;
   DROP VIEW JNULL5 ;
   DROP VIEW JNULL6 ;
   DROP VIEW JNULL4 ;
   DROP TABLE JNULL1 ;
-- PASS:0623 If table is dropped?

   COMMIT WORK;

--O   DROP TABLE JNULL2 CASCADE;
   DROP TABLE JNULL2 ;
-- PASS:0623 If table is dropped?

   COMMIT WORK;

-- END TEST >>> 0623 <<< END TEST

-- *********************************************

-- TEST:0625 ADD COLUMN and DROP COLUMN!

   CREATE TABLE CHANGG
     (NAAM CHAR (14) NOT NULL PRIMARY KEY, AGE INT);
-- PASS:0625 If table is created?

   COMMIT WORK;

   CREATE VIEW CHANGGVIEW AS
     SELECT * FROM CHANGG;
-- PASS:0625 If view is created?

   COMMIT WORK;

--O   ALTER TABLE CHANGG
--O     DROP NAAM RESTRICT;
-- PASS:0625 If ERROR, view references NAAM?

--O   COMMIT WORK;

   INSERT INTO CHANGG VALUES ('RALPH', 22);
-- PASS:0625 If 1 row is inserted?

   INSERT INTO CHANGG VALUES ('RUDOLPH', 54);
-- PASS:0625 If 1 row is inserted?

   INSERT INTO CHANGG VALUES ('QUEEG', 33);
-- PASS:0625 If 1 row is inserted?

   INSERT INTO CHANGG VALUES ('BESSIE', 106);
-- PASS:0625 If 1 row is inserted?

   SELECT COUNT(*) 
     FROM CHANGG WHERE DIVORCES IS NULL;
-- PASS:0625 If ERROR, column does not exist?

   COMMIT WORK;

   ALTER TABLE CHANGG ADD NUMBRR CHAR(11);
-- PASS:0625 If column is added?

   COMMIT WORK;

   SELECT MAX(AGE) FROM CHANGGVIEW;
-- PASS:0625 If value is 106?