dml104.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML104  
-- SQL Test Suite, V6.0, Interactive SQL, dml104.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
   ROLLBACK WORK;
ij> -- date_time print
-- TEST:0591 NATURAL JOIN (feature 4) (static)!
   CREATE TABLE GROUPS1
  (EMPNUM INT, GRP INT);
0 rows inserted/updated/deleted
ij> -- PASS:0591 If table is created?
   CREATE TABLE NAMES1
  (EMPNUM INT, NAME CHAR(5));
0 rows inserted/updated/deleted
ij> -- PASS:0591 If table is created?
  SELECT * FROM NAMES1 JOIN GROUPS1 ON (NAMES1.EMPNUM=GROUPS1.EMPNUM);
EMPNUM     |NAME |EMPNUM     |GRP        
-----------------------------------------
ij> CREATE VIEW NAMGRP1 (EMPNUM, NAME, NEMPNUM, GRP) AS
--O  SELECT * FROM NAMES1 NATURAL JOIN GROUPS1;
  SELECT * FROM NAMES1 JOIN GROUPS1 ON (NAMES1.EMPNUM=GROUPS1.EMPNUM);
0 rows inserted/updated/deleted
ij> -- PASS:0591 If view is created?
   COMMIT WORK;
ij> INSERT INTO GROUPS1 VALUES (0, 10);
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO GROUPS1 VALUES (1, 20);
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO GROUPS1 VALUES (2, 30);
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO GROUPS1 VALUES (3, 40);
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO NAMES1 VALUES (5, 'HARRY');
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO NAMES1 VALUES (1, 'MARY');
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO NAMES1 VALUES (7, 'LARRY');
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO NAMES1 VALUES (0, 'KERI');
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   INSERT INTO NAMES1 VALUES (9, 'BARRY');
1 row inserted/updated/deleted
ij> -- PASS:0591 If 1 row is inserted?
   SELECT EMPNUM
  FROM NAMGRP1
  WHERE NAME = 'KERI'
  AND GRP = 10;
EMPNUM     
-----------
0          
ij> -- PASS:0591 If 1 row is selected and EMPNUM = 0?
   SELECT EMPNUM
  FROM NAMGRP1
  WHERE NAME = 'MARY'
  AND GRP = 20;
EMPNUM     
-----------
1          
ij> -- PASS:0591 If 1 row is selected and EMPNUM = 1?
   SELECT COUNT(*)
  FROM NAMGRP1
  WHERE (NAME <> 'MARY'
  AND NAME <> 'KERI')
  OR GRP <> 20
  AND GRP <> 10
  OR EMPNUM <> 0
  AND EMPNUM <> 1
  OR NAME IS NULL
  OR GRP IS NULL
  OR EMPNUM IS NULL;
1          
-----------
0          
ij> -- PASS:0591 If count = 0?
   ROLLBACK WORK;
ij> --O   DROP TABLE NAMES1 CASCADE;
   DROP VIEW NAMGRP1 ;
0 rows inserted/updated/deleted
ij> DROP TABLE NAMES1 ;
0 rows inserted/updated/deleted
ij> -- PASS:0591 If table and view are dropped?
--O   DROP TABLE GROUPS1 CASCADE;
   DROP TABLE GROUPS1 ;
0 rows inserted/updated/deleted
ij> -- PASS:0591 If table is dropped?
   COMMIT WORK;
ij> -- END TEST >>> 0591 <<< END TEST
-- *********************************************
-- TEST:0592 INNER JOIN (feature 4) (static)!
   CREATE TABLE GROUPS2
  (EMPNUM SMALLINT, GRP INT);
0 rows inserted/updated/deleted
ij> -- PASS:0592 If table is created?
   CREATE TABLE NAMES2
  (EMPNUM INT, NAME CHAR(5));
0 rows inserted/updated/deleted
ij> -- PASS:0592 If table is created?
   CREATE VIEW NAMGRP2 (EMPNUM, NAME, NEMPNUM, GRP) AS
  SELECT * FROM NAMES2 INNER JOIN GROUPS2 ON (NAMES2.EMPNUM=GROUPS2.EMPNUM);
0 rows inserted/updated/deleted
ij> -- PASS:0592 If view is created?
   CREATE VIEW NMGRP2 (EMPNUM, NAME, NEMPNUM, GRP) AS
  SELECT * FROM NAMES2 JOIN GROUPS2 
    ON (NAMES2.EMPNUM=GROUPS2.EMPNUM) WHERE NAMES2.EMPNUM > 0;
0 rows inserted/updated/deleted
ij> -- PASS:0592 If view is created?
   COMMIT WORK;
ij> INSERT INTO GROUPS2 VALUES (0, 10);
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO GROUPS2 VALUES (1, 20);
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO GROUPS2 VALUES (2, 30);
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO GROUPS2 VALUES (3, 40);
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO NAMES2 VALUES (5, 'HARRY');
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO NAMES2 VALUES (1, 'MARY');
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO NAMES2 VALUES (7, 'LARRY');
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO NAMES2 VALUES (0, 'KERI');
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   INSERT INTO NAMES2 VALUES (9, 'BARRY');
1 row inserted/updated/deleted
ij> -- PASS:0592 If 1 row is inserted?
   SELECT EMPNUM
  FROM NAMGRP2
  WHERE NAME = 'KERI'
  AND GRP = 10;
EMPNUM     
-----------
0          
ij> -- PASS:0592 If 1 row is selected and EMPNUM = 0?
   SELECT EMPNUM
  FROM NAMGRP2
  WHERE NAME = 'MARY'
  AND GRP = 20;
EMPNUM     
-----------
1          
ij> -- PASS:0592 If 1 row is selected and EMPNUM = 1?
   SELECT COUNT(*)
  FROM NAMGRP2
  WHERE NAME <> 'MARY'
  AND NAME <> 'KERI'
  OR GRP <> 20
  AND GRP <> 10
  OR EMPNUM <> 0
  AND EMPNUM <> 1
  OR NAME IS NULL
  OR GRP IS NULL
  OR EMPNUM IS NULL;
1          
-----------
0          
ij> -- PASS:0592 If count = 0?
   SELECT EMPNUM
  FROM NMGRP2
  WHERE NAME = 'MARY'
  AND GRP = 20;
EMPNUM     
-----------
1          
ij> -- PASS:0592 If 1 row is selected and EMPNUM = 1?
   SELECT COUNT(*)
  FROM NMGRP2
  WHERE NAME <> 'MARY'
  OR GRP <> 20
  OR EMPNUM <> 1
  OR NAME IS NULL
  OR GRP IS NULL
  OR EMPNUM IS NULL;
1          
-----------
0          
ij> -- PASS:0592 If count = 0?
   ROLLBACK WORK;
ij> --O   DROP TABLE NAMES2 CASCADE;
   DROP VIEW NMGRP2 ;
0 rows inserted/updated/deleted
ij> DROP VIEW NAMGRP2 ;
0 rows inserted/updated/deleted
ij> DROP TABLE NAMES2 ;
0 rows inserted/updated/deleted
ij> -- PASS:0592 If table and 2 views are dropped?
--O   DROP TABLE GROUPS2 CASCADE;
   DROP TABLE GROUPS2 ;
0 rows inserted/updated/deleted
ij> -- PASS:0592 If table is dropped?
   COMMIT WORK;
ij> -- END TEST >>> 0592 <<< END TEST
-- *********************************************
-- TEST:0593 LEFT OUTER JOIN (feature 4) (static)!
   CREATE TABLE GROUPS3
  (EMPNUM INT, GRP INT);
0 rows inserted/updated/deleted
ij> -- PASS:0593 If table is created?
   CREATE TABLE NAMES3
  (EMPNUM INT, NAME CHAR(5));
0 rows inserted/updated/deleted
ij> -- PASS:0593 If table is created?
   CREATE VIEW NAMGRP3 (EMPNUM, NAME, NEMPNUM, GRP) AS
  SELECT * FROM NAMES3 LEFT OUTER JOIN GROUPS3 ON (NAMES3.EMPNUM=GROUPS3.EMPNUM);
0 rows inserted/updated/deleted
ij> -- PASS:0593 If view is created?
   CREATE VIEW NMGRP3 (NAME, GRP) AS
  SELECT NAME, GRP FROM NAMES3 LEFT OUTER JOIN GROUPS3
  ON NAMES3.EMPNUM < GROUPS3.EMPNUM
  WHERE NAME <> 'KERI';
0 rows inserted/updated/deleted
ij> -- PASS:0593 If view is created?
   COMMIT WORK;
ij> INSERT INTO GROUPS3 VALUES (0, 10);
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO GROUPS3 VALUES (1, 20);
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO GROUPS3 VALUES (2, 30);
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO GROUPS3 VALUES (3, 40);
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO NAMES3 VALUES (5, 'HARRY');
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO NAMES3 VALUES (1, 'MARY');
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO NAMES3 VALUES (7, 'LARRY');
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO NAMES3 VALUES (0, 'KERI');
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   INSERT INTO NAMES3 VALUES (9, 'BARRY');
1 row inserted/updated/deleted
ij> -- PASS:0593 If 1 row is inserted?
   SELECT COUNT(*)
  FROM NAMGRP3
  WHERE EMPNUM = 0
  AND NAME = 'KERI'
  AND GRP = 10;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP3
  WHERE EMPNUM = 1
  AND NAME = 'MARY'
  AND GRP = 20;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP3
  WHERE EMPNUM = 5
  AND NAME = 'HARRY'
  AND GRP IS NULL;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP3
  WHERE EMPNUM = 7
  AND NAME = 'LARRY'
  AND GRP IS NULL;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP3
  WHERE EMPNUM = 9
  AND NAME = 'BARRY'
  AND GRP IS NULL;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP3;
1          
-----------
5          
ij> -- PASS:0593 If count = 5?
   SELECT COUNT(*)
  FROM NMGRP3
  WHERE NAME = 'HARRY'
  AND GRP IS NULL;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NMGRP3
  WHERE NAME = 'MARY'
  AND GRP = 30;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NMGRP3
  WHERE NAME = 'MARY'
  AND GRP = 40;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NMGRP3
  WHERE NAME = 'BARRY'
  AND GRP IS NULL;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NMGRP3
  WHERE NAME = 'LARRY'
  AND GRP IS NULL;
1          
-----------
1          
ij> -- PASS:0593 If count = 1?
   SELECT COUNT(*)
  FROM NMGRP3;
1          
-----------
5          
ij> -- PASS:0593 If count = 5?
   ROLLBACK WORK;
ij> --O   DROP TABLE NAMES3 CASCADE;
   DROP VIEW NMGRP3 ;
0 rows inserted/updated/deleted
ij> DROP VIEW NAMGRP3 ;
0 rows inserted/updated/deleted
ij> DROP TABLE NAMES3 ;
0 rows inserted/updated/deleted
ij> -- PASS:0593 If table and 2 views are dropped?
--O   DROP TABLE GROUPS3 CASCADE;
   DROP TABLE GROUPS3 ;
0 rows inserted/updated/deleted
ij> -- PASS:0593 If table is dropped?
   COMMIT WORK;
ij> -- END TEST >>> 0593 <<< END TEST
-- *********************************************
-- TEST:0594 RIGHT OUTER JOIN (feature 4) (static)!
   CREATE TABLE GROUPS4
  (EMPNUM INT, GRP INT);
0 rows inserted/updated/deleted
ij> -- PASS:0594 If table is created?
   CREATE TABLE NAMES4
  (EMPNUM DECIMAL (4, 2), NAME CHAR(5));
0 rows inserted/updated/deleted
ij> -- PASS:0594 If table is created?
   CREATE VIEW NAMGRP4 (NEMPNUM, NAME, EMPNUM, GRP) AS
  SELECT * FROM NAMES4 RIGHT OUTER JOIN GROUPS4 ON (NAMES4.EMPNUM=GROUPS4.EMPNUM);
0 rows inserted/updated/deleted
ij> -- PASS:0594 If view is created?
   COMMIT WORK;
ij> INSERT INTO GROUPS4 VALUES (0, 10);
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO GROUPS4 VALUES (1, 20);
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO GROUPS4 VALUES (2, 30);
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO GROUPS4 VALUES (3, 40);
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO NAMES4 VALUES (5.0, 'HARRY');
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO NAMES4 VALUES (1.0, 'MARY');
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO NAMES4 VALUES (7.0, 'LARRY');
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO NAMES4 VALUES (0.0, 'KERI');
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   INSERT INTO NAMES4 VALUES (9.0, 'BARRY');
1 row inserted/updated/deleted
ij> -- PASS:0594 If 1 row is inserted?
   SELECT COUNT(*)
  FROM NAMGRP4
  WHERE EMPNUM = 0
  AND NAME = 'KERI'
  AND GRP = 10;
1          
-----------
1          
ij> -- PASS:0594 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP4
  WHERE EMPNUM = 1
  AND NAME = 'MARY'
  AND GRP = 20;
1          
-----------
1          
ij> -- PASS:0594 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP4
  WHERE EMPNUM = 2
  AND NAME IS NULL
  AND GRP = 30;
1          
-----------
1          
ij> -- PASS:0594 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP4
  WHERE EMPNUM = 3
  AND NAME IS NULL
  AND GRP = 40;
1          
-----------
1          
ij> -- PASS:0594 If count = 1?
   SELECT COUNT(*)
  FROM NAMGRP4;
1          
-----------
4          
ij> -- PASS:0594 If count = 4?
   ROLLBACK WORK;
ij> --O   DROP TABLE NAMES4 CASCADE;
   DROP VIEW NAMGRP4 ;
0 rows inserted/updated/deleted
ij> DROP TABLE NAMES4 ;
0 rows inserted/updated/deleted
ij> -- PASS:0594 If table and view are dropped?
--O   DROP TABLE GROUPS4 CASCADE;
   DROP TABLE GROUPS4 ;
0 rows inserted/updated/deleted
ij> -- PASS:0594 If table is dropped?
   COMMIT WORK;
ij> -- END TEST >>> 0594 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>