dml114.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML114  
-- SQL Test Suite, V6.0, Interactive SQL, dml114.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --0   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:0635 Feature 13, grouped operations (static)!
  CREATE VIEW WORKWEEK AS
  SELECT EMPNUM, HOURS FROM HU.WORKS
  GROUP BY HOURS, EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0635 If table is created?
   COMMIT WORK;
ij> SELECT EMPNUM, SUM (HOURS)
  FROM WORKWEEK
  WHERE HOURS > 20
  GROUP BY EMPNUM
  HAVING EMPNUM = 'E1';
EM&|2          
---------------
E1 |120        
ij> -- PASS:0635 If 1 row selected and EMPNUM = 'E1' and SUM(HOURS) = 120?
   SELECT COUNT(*)
  FROM WORKWEEK WHERE HOURS > 40;
1          
-----------
3          
ij> -- PASS:0635 If count = 3?
   SELECT EMPNAME
  FROM HU.STAFF, WORKWEEK
  WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM
  AND HOURS = 12;
EMPNAME             
--------------------
Alice               
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
   SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM), AVG(HOURS)
  FROM WORKWEEK;
1          |2   |3   |4            
-----------------------------------
10         |E4  |E1  |43.2000      
ij> -- PASS:0635 If 1 row selected and count = 10 and MAX(EMPNUM) = 'E4'?
-- PASS:0635 AND MIN(EMPNUM) = 'E1' and AVG(HOURS) = 43 (approximately)?
   SELECT EMPNAME
  FROM HU.STAFF WHERE EMPNUM =
  (SELECT EMPNUM FROM WORKWEEK
    WHERE HOURS = 12);
EMPNAME             
--------------------
Alice               
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
   SELECT EMPNAME
  FROM HU.STAFF WHERE EMPNUM =
  (SELECT EMPNUM FROM HU.WORKS
    GROUP BY EMPNUM, HOURS
    HAVING HOURS = 12);
EMPNAME             
--------------------
Alice               
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
-- NOTE:0635 Cursor subtest deleted.
   COMMIT WORK;
ij> --0   DROP VIEW WORKWEEK CASCADE;
   DROP VIEW WORKWEEK ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0635 <<< END TEST
-- *********************************************
-- TEST:0637 Feature 14, Qualified * in select list (static)!
   CREATE VIEW QUALSTAR AS
  SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS
  WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
   COMMIT WORK;
ij> CREATE VIEW CORRQUALSTAR AS
  SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS
  WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
   COMMIT WORK;
ij> CREATE VIEW SUBQ2 AS
  SELECT DISTINCT * FROM QUALSTAR;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
   COMMIT WORK;
ij> CREATE VIEW CORRSUBQ2 AS
  SELECT DISTINCT * FROM CORRQUALSTAR;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
   COMMIT WORK;
ij> SELECT COUNT(*) FROM QUALSTAR;
1          
-----------
12         
ij> -- PASS:0637 If count = 12?
   SELECT COUNT(*) FROM SUBQ2;
1          
-----------
10         
ij> -- PASS:0637 If count = 10?
   SELECT EMPNUM, GRADE, CITY, HOURS
  FROM QUALSTAR WHERE EMPNAME = 'Carmen';
EM&|GRADE|CITY           |HOURS 
--------------------------------
E3 |13   |Vienna         |20    
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and GRADE = 13?
-- PASS:0637 AND CITY = 'Vienna' and HOURS = 20?
-- NOTE:0637 Cursor subtest deleted.
   SELECT HU.STAFF.*, HOURS
  FROM HU.STAFF, HU.WORKS
  WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM
  AND EMPNAME = 'Carmen';
EM&|EMPNAME             |GRADE|CITY           |HOURS 
-----------------------------------------------------
E3 |Carmen              |13   |Vienna         |20    
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and EMPNAME = 'Carmen'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
   SELECT COUNT(*) FROM CORRQUALSTAR;
1          
-----------
12         
ij> -- PASS:0637 If count = 12?
   SELECT COUNT(*) FROM CORRSUBQ2;
1          
-----------
10         
ij> -- PASS:0637 If count = 10?
   SELECT EMPNUM, GRADE, CITY, HOURS
  FROM CORRQUALSTAR WHERE EMPNAME = 'Carmen';
EM&|GRADE|CITY           |HOURS 
--------------------------------
E3 |13   |Vienna         |20    
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
   COMMIT WORK;
ij> --0   DROP VIEW QUALSTAR CASCADE;
   DROP VIEW SUBQ2 ;
0 rows inserted/updated/deleted
ij> DROP VIEW QUALSTAR ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> --0   DROP VIEW CORRQUALSTAR CASCADE;
   DROP VIEW CORRSUBQ2 ;
0 rows inserted/updated/deleted
ij> DROP VIEW CORRQUALSTAR ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0637 <<< END TEST
-- *********************************************
-- TEST:0639 Feature 15, Lowercase Identifiers (static)!
  create view Staff (Empnum, empname, Grade, City) as
  select empnum, EMPNAME, Grade, cItY from Hu.Staff;
0 rows inserted/updated/deleted
ij> -- PASS:0639 If view is created?
  commit work;
ij> SELECT EMPNUM as WhatsHisNumber, GRADE, CITY
  FROM Flater.staff FLaterStaff_Flater
  WHERE EMPNAME = 'Carmen'
--0 SQL92 does not scope renames into the query, just outside it -- ALC
--0  AND FLATERstaff_fLATER.whatshisnumber = 'E3';
  AND FLATERstaff_fLATER.empnum = 'E3';
WH&|GRADE|CITY           
-------------------------
E3 |13   |Vienna         
ij> -- PASS:0639 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0639 AND GRADE = 13 and CITY = 'Vienna'?
-- NOTE:0639 Cursor subtest deleted.
   COMMIT WORK;
ij> --0   DROP VIEW STAFF CASCADE;
   DROP VIEW STAFF ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0639 <<< END TEST
-- *********************************************
-- TEST:0641 Feature 16, PRIMARY KEY enhancement (static)!
   CREATE TABLE FEAT16 (
  EMPNUM INT NOT NULL PRIMARY KEY,
  PNUM   INT  NOT NULL UNIQUE);
0 rows inserted/updated/deleted
ij> -- PASS:0641 If view is created?
   COMMIT WORK;
ij> CREATE TABLE BARNO (
  P1 INT NOT NULL, P2 CHAR NOT NULL, X1 INT NOT NULL, X2 CHAR NOT NULL,
  UNIQUE (X2, X1),
  PRIMARY KEY (P1, P2));
0 rows inserted/updated/deleted
ij> -- PASS:0641 If view is created?
   COMMIT WORK;
ij> INSERT INTO FEAT16 VALUES (1, 10);
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO FEAT16 VALUES (2, 20);
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO FEAT16 VALUES (1, 30);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'FEAT16'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
   INSERT INTO FEAT16 VALUES (3, 20);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'FEAT16'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
   INSERT INTO FEAT16 VALUES (3, NULL);
ERROR 23502: Column 'PNUM'  cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO FEAT16 VALUES (4, NULL);
ERROR 23502: Column 'PNUM'  cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO FEAT16 VALUES (5, NULL);
ERROR 23502: Column 'PNUM'  cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO BARNO VALUES (1, 'A', 10, 'a');
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO BARNO VALUES (2, 'A', 20, 'a');
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO BARNO VALUES (1, 'A', 30, 'a');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'BARNO'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
   INSERT INTO BARNO VALUES (3, 'A', 20, 'a');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'BARNO'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
   INSERT INTO BARNO VALUES (3, NULL, 30, 'a');
ERROR 23502: Column 'P2'  cannot accept a NULL value.
ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted?
-- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted?
   INSERT INTO BARNO VALUES (3, NULL, 30, 'b');
ERROR 23502: Column 'P2'  cannot accept a NULL value.
ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted?
-- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted?
   INSERT INTO BARNO VALUES (3, 'A', 30, NULL);
ERROR 23502: Column 'X2'  cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO BARNO VALUES (3, 'B', 30, NULL);
ERROR 23502: Column 'X2'  cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
   INSERT INTO BARNO VALUES (4, 'B', NULL, NULL);
ERROR 23502: Column 'X1'  cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
   COMMIT WORK;
ij> --0   DROP TABLE FEAT16 CASCADE;
   DROP TABLE FEAT16 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> --0   DROP TABLE BARNO CASCADE;
   DROP TABLE BARNO ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0641 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>