schema1.sql

derby database source code.good for you.

-- SQL Test Suite, V6.0, Schema Definition, schema1.std
-- 59-byte ID
-- TEd Version #
-- date_time print
-- ***************************************************************
-- ****** THIS FILE SHOULD BE RUN UNDER AUTHORIZATION ID HU ******
-- ***************************************************************

-- This file defines the base tables used in most of the tests.

-- This is a standard schema definition.

  CREATE SCHEMA
--O  AUTHORIZATION HU;
  HU;
  set schema hu;

  CREATE TABLE BASE_TESTREPORT
   (TESTNO   CHAR(4),
    RESULT   CHAR(4),
    TESTTYPE  CHAR(3));

  CREATE TABLE HU.ECCO (C1 CHAR(2));

  CREATE TABLE STAFF
   (EMPNUM   CHAR(3) NOT NULL UNIQUE,
    EMPNAME  CHAR(20),
    GRADE    DECIMAL(4),
    CITY     CHAR(15));


  CREATE TABLE PROJ
   (PNUM     CHAR(3) NOT NULL UNIQUE,
    PNAME    CHAR(20),
    PTYPE    CHAR(6),
    BUDGET   DECIMAL(9),
    CITY     CHAR(15)) ;


  CREATE TABLE WORKS
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5),
--O    UNIQUE(EMPNUM,PNUM))
    constraint worksc1 UNIQUE(EMPNUM,PNUM)
    );



  CREATE TABLE FIPS1
   (FIPS_TEST CHAR(20));



  CREATE TABLE STAFF1 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15));

  CREATE TABLE PROJ1 (PNUM    CHAR(3) NOT NULL UNIQUE,
                PNAME  CHAR(20),
                PTYPE  CHAR(6),
                BUDGET DECIMAL(9),
                CITY   CHAR(15));


  CREATE TABLE WORKS1(EMPNUM    CHAR(3) NOT NULL,
                PNUM    CHAR(3) NOT NULL,
                HOURS   DECIMAL(5),
--O                UNIQUE(EMPNUM, PNUM))
                constraint works1c1 UNIQUE(EMPNUM, PNUM)
                );


  CREATE TABLE STAFF3 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15));

  CREATE TABLE STAFF4 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15));


  -- we use the keyword BIGINT, so I have changed
  -- the name of this table.
  CREATE TABLE LONGINTTAB (LONG_INT DECIMAL(15));


  CREATE TABLE TEMP_S
     (EMPNUM  CHAR(3),
      GRADE DECIMAL(4),
      CITY CHAR(15));



  CREATE TABLE TMP (T1 CHAR (10), T2 DECIMAL(2), T3 CHAR (10));



  CREATE TABLE AA (CHARTEST     CHAR(20));

  CREATE TABLE BB (CHARTEST     CHAR);

  CREATE TABLE CC (CHARTEST     CHARACTER(20));

  CREATE TABLE DD (CHARTEST     CHARACTER);

  CREATE TABLE EE (INTTEST     INTEGER);

  CREATE TABLE FF (INTTEST     INT);

  CREATE TABLE GG (REALTEST     REAL);

  CREATE TABLE HH (SMALLTEST  SMALLINT);

  CREATE TABLE II (DOUBLETEST  DOUBLE PRECISION);

  CREATE TABLE JJ (FLOATTEST  FLOAT);

  CREATE TABLE KK (FLOATTEST  FLOAT(32));

  CREATE TABLE LL (NUMTEST  NUMERIC(13,6));

  CREATE TABLE MM (NUMTEST  NUMERIC);
 
  CREATE TABLE MM2 (NUMTEST NUMERIC(10));

  CREATE TABLE NN (NUMTEST  NUMERIC(9));

  CREATE TABLE OO (NUMTEST  NUMERIC(9));

  CREATE TABLE PP (NUMTEST  DECIMAL(13,6));

  CREATE TABLE QQ (NUMTEST  DECIMAL);

  CREATE TABLE RR (NUMTEST  DECIMAL(8));

  CREATE TABLE SS (NUMTEST  DEC(13,6));

  CREATE TABLE P1 (NUMTEST  NUMERIC(1));

  CREATE TABLE P7 (NUMTEST  NUMERIC(7));

  CREATE TABLE P12 (NUMTEST  NUMERIC(12));

  CREATE TABLE P15 (NUMTEST  NUMERIC(15));



  CREATE TABLE VTABLE
         (COL1   INTEGER,
          COL2   INTEGER,
          COL3   INTEGER,
          COL4   INTEGER,
          COL5   DECIMAL(7,2));



  CREATE TABLE UPUNIQ (NUMKEY  DECIMAL(3) NOT NULL UNIQUE,
                           COL2    CHAR(2));




  CREATE TABLE TEXT80  (TEXXT CHAR(80));
  CREATE TABLE TEXT132  (TEXXT CHAR(132));
  CREATE TABLE TEXT240  (TEXXT CHAR(240));
  CREATE TABLE TEXT256  (TEXXT VARCHAR(256));
  CREATE TABLE TEXT512  (TEXXT VARCHAR(512));
  CREATE TABLE TEXT1024  (TEXXT VARCHAR(1024));

-- The following tables are used to test the limitations (12-14-88)


  CREATE TABLE T240(STR240 CHAR(240));

  CREATE TABLE DEC15(COL1 DECIMAL(15,7));

  CREATE TABLE FLO15(COL1 FLOAT(15));

  CREATE TABLE INT10(COL1 INTEGER, COL2 SMALLINT);

  CREATE TABLE T100(C1 CHAR(2),C2 CHAR(2),C3 CHAR(2),C4 CHAR(2),
                C5 CHAR(2),C6 CHAR(2),C7 CHAR(2),C8 CHAR(2),
                C9 CHAR(2),C10 CHAR(2),C11 CHAR(2),C12 CHAR(2),
                C13 CHAR(2),C14 CHAR(2),C15 CHAR(2),C16 CHAR(2),
                C17 CHAR(2),C18 CHAR(2),C19 CHAR(2),C20 CHAR(2),
                C21 CHAR(2),C22 CHAR(2),C23 CHAR(2),C24 CHAR(2),
                C25 CHAR(2),C26 CHAR(2),C27 CHAR(2),C28 CHAR(2),
                C29 CHAR(2),C30 CHAR(2),C31 CHAR(2),C32 CHAR(2),
                C33 CHAR(2),C34 CHAR(2),C35 CHAR(2),C36 CHAR(2),
                C37 CHAR(2),C38 CHAR(2),C39 CHAR(2),C40 CHAR(2),
                C41 CHAR(2),C42 CHAR(2),C43 CHAR(2),C44 CHAR(2),
                C45 CHAR(2),C46 CHAR(2),C47 CHAR(2),C48 CHAR(2),
                C49 CHAR(2),C50 CHAR(2),C51 CHAR(2),C52 CHAR(2),
                C53 CHAR(2),C54 CHAR(2),C55 CHAR(2),C56 CHAR(2),
                C57 CHAR(2),C58 CHAR(2),C59 CHAR(2),C60 CHAR(2),
                C61 CHAR(2),C62 CHAR(2),C63 CHAR(2),C64 CHAR(2),
                C65 CHAR(2),C66 CHAR(2),C67 CHAR(2),C68 CHAR(2),
                C69 CHAR(2),C70 CHAR(2),C71 CHAR(2),C72 CHAR(2),
                C73 CHAR(2),C74 CHAR(2),C75 CHAR(2),C76 CHAR(2),
                C77 CHAR(2),C78 CHAR(2),C79 CHAR(2),C80 CHAR(2),
                C81 CHAR(2),C82 CHAR(2),C83 CHAR(2),C84 CHAR(2),
                C85 CHAR(2),C86 CHAR(2),C87 CHAR(2),C88 CHAR(2),
                C89 CHAR(2),C90 CHAR(2),C91 CHAR(2),C92 CHAR(2),
                C93 CHAR(2),C94 CHAR(2),C95 CHAR(2),C96 CHAR(2),
                C97 CHAR(2),C98 CHAR(2),C99 CHAR(2),C100 CHAR(2));

  CREATE TABLE T2000(STR110 CHAR(110),STR120 CHAR(120),
                   STR130 CHAR(130),STR140 CHAR(140),
                   STR150 CHAR(150),STR160 CHAR(160),
                   STR170 CHAR(170),STR180 CHAR(180),
                   STR190 CHAR(190),STR200 CHAR(200),
                   STR210 CHAR(210),STR216 CHAR(216));

  CREATE TABLE T8(COL1 CHAR(2) NOT NULL,COL2 CHAR(4) NOT NULL,
                COL3 CHAR(6) NOT NULL,COL4 CHAR(8) NOT NULL,
                COL5 CHAR(10) NOT NULL,COL6 CHAR(12) NOT NULL,
                COL7 CHAR(14),COL8 CHAR(16),
--O                UNIQUE(COL1,COL2,COL3,COL4,COL5,COL6));
                constraint t8c1 UNIQUE(COL1,COL2,COL3,COL4,COL5,COL6));

  CREATE TABLE T118(STR118 CHAR(118) NOT NULL UNIQUE);

  CREATE TABLE T4(STR110 CHAR(110) NOT NULL,
                NUM6   NUMERIC(6) NOT NULL,
                COL3   CHAR(10),COL4 CHAR(20),
--O                UNIQUE(STR110,NUM6))
                constraint t4c1 UNIQUE(STR110,NUM6)
                );

  CREATE TABLE T12(COL1 CHAR(1), COL2 CHAR(2),
                 COL3 CHAR(4), COL4 CHAR(6),
                 COL5 CHAR(8), COL6 CHAR(10),
                 COL7 CHAR(20), COL8 CHAR(30),
                 COL9 CHAR(40), COL10 CHAR(50),
                 COL11 INTEGER, COL12 INTEGER);


   
  CREATE TABLE NEXTKEY (KEYNUM INTEGER, AUTHOR CHAR(1),
                         DOLLARS INTEGER);


  CREATE TABLE SV (NUMTEST NUMERIC(8,3));

  CREATE TABLE JJ_20 (FLOATTEST  FLOAT(20));

  CREATE TABLE PP_15 (NUMTEST  DECIMAL(15,15));
 
  CREATE TABLE PP_7  (NUMTEST  DECIMAL(15,7));

  CREATE TABLE P15_15 (NUMTEST  NUMERIC(15,15));

  CREATE TABLE P15_7 (NUMTEST  NUMERIC(15,7));

  CREATE TABLE TEMP_OBSERV
               (YEAR_OBSERV  NUMERIC(4),
                CITY         CHAR(10),
                MAX_TEMP     NUMERIC(5,2),
                MIN_TEMP     NUMERIC(5,2));

  CREATE TABLE TOKENS
    (PROG_NO INT, TOKEN_NO INT);

  CREATE TABLE WHICH_SCHEMA1 (C1 CHAR (50));

-- ************* create view statements follow *************

   CREATE VIEW TESTREPORT AS
    SELECT TESTNO, RESULT, TESTTYPE
    FROM BASE_TESTREPORT;

   CREATE VIEW CELSIUS_OBSERV (CITY, YEAR_OBSERV, MIN_C, MAX_C)
      AS SELECT CITY, YEAR_OBSERV, (MIN_TEMP - 32) * 5 / 9,
                (MAX_TEMP - 32) * 5 / 9
         FROM TEMP_OBSERV;

   CREATE VIEW MULTI_YEAR_OBSERV (CITY, HIGH, LOW)
      AS SELECT CITY, AVG(MAX_TEMP), AVG(MIN_TEMP)
            FROM TEMP_OBSERV
            GROUP BY CITY;

   CREATE VIEW EXTREME_TEMPS (YEAR_OBSERV, HIGH, LOW)
      AS SELECT YEAR_OBSERV, MAX(MAX_TEMP), MIN(MIN_TEMP)
            FROM TEMP_OBSERV
            GROUP BY YEAR_OBSERV;

       CREATE VIEW SET_TEST (EMP1, EMP_AVG, EMP_MAX) AS
           SELECT STAFF.EMPNUM, AVG(HOURS), MAX(HOURS)
           FROM STAFF, WORKS
           GROUP BY STAFF.EMPNUM;
          
       CREATE VIEW DUP_COL (EMP1, PNO, HOURS, HOURS_2) AS
           SELECT EMPNUM, PNUM, HOURS, HOURS * 2
           FROM WORKS;

  CREATE VIEW STAFFV1
           AS SELECT * FROM STAFF
              WHERE  GRADE >= 12;

  CREATE VIEW STAFFV2
           AS SELECT * FROM STAFF
              WHERE  GRADE >= 12
--0           WITH CHECK OPTION
    ;


  
  CREATE VIEW STAFFV2_VIEW
           AS SELECT * 
              FROM   STAFFV2
              WHERE  CITY = 'Vienna';


  CREATE VIEW DOMAIN_VIEW
           AS SELECT *
              FROM   WORKS
              WHERE  EMPNUM = 'E1' AND HOURS = 80
                  OR EMPNUM = 'E2' AND HOURS = 40
                  OR EMPNUM = 'E4' AND HOURS = 20
--0              WITH CHECK OPTION
			  ;

  CREATE VIEW STAFF2
           AS SELECT * 
              FROM   STAFF
--0           WITH CHECK OPTION
	;



  CREATE VIEW STAFF_WORKS_DESIGN (NAME,COST,PROJECT)
    AS SELECT EMPNAME,HOURS*2*GRADE,PNAME
     FROM   PROJ,STAFF,WORKS
     WHERE  STAFF.EMPNUM=WORKS.EMPNUM
        AND WORKS.PNUM=PROJ.PNUM
        AND PTYPE='Design';


  CREATE VIEW SUBSP (EMPNUM,PNUM,HOURS)
     AS SELECT EMPNUM,PNUM,HOURS
        FROM   WORKS
        WHERE  EMPNUM='E3'
--0     WITH CHECK OPTION
	 ;

  CREATE VIEW TEMP_SS(EMPNUM,GRADE,CITY)
     AS SELECT EMPNUM,GRADE,CITY
        FROM   STAFF
        WHERE  GRADE > 12
--0     WITH CHECK OPTION
	 ;

  CREATE VIEW V_WORKS1                
           AS SELECT * FROM WORKS       
              WHERE HOURS > 15          
--0           WITH CHECK OPTION
		   ;

  CREATE VIEW V_WORKS2                
           AS SELECT * FROM V_WORKS1    
              WHERE EMPNUM = 'E1'       
                 OR EMPNUM = 'E6';

  CREATE VIEW V_WORKS3                
           AS SELECT * FROM V_WORKS2    
              WHERE PNUM = 'P2'         
                 OR PNUM = 'P7'         
--0           WITH CHECK OPTION       
		   ;

  CREATE VIEW UPDATE_VIEW1   
            AS SELECT ALL CITY
                     FROM PROJ;

  CREATE VIEW UPDATE_VIEW2 
            AS SELECT HOURS, EMPNUM, PNUM
                     FROM WORKS
                     WHERE HOURS IN (10, 20, 40);

  CREATE VIEW UPDATE_VIEW3  
            AS SELECT *
                     FROM WORKS
                     WHERE PNUM BETWEEN 'P2' AND 'P4'
                     AND EMPNUM NOT BETWEEN 'E2' AND 'E3';
 
  CREATE VIEW UPDATE_VIEW4  
            AS SELECT PNUM, EMPNUM 
                     FROM WORKS
                     WHERE PNUM LIKE '_2%';

  CREATE VIEW UPDATE_VIEW5 
            AS SELECT *
                     FROM STAFF
                     WHERE EMPNAME IS NOT NULL AND CITY IS NULL;

  CREATE VIEW UPDATE_VIEW6 
            AS SELECT EMPNAME, CITY, GRADE
                     FROM STAFF
                     WHERE EMPNAME >= 'Betty' AND EMPNUM < 'E35'
                       OR CITY <= 'Deale' AND GRADE > 12
                       OR GRADE = 13 AND CITY <> 'Akron';

  CREATE VIEW UPDATE_VIEW7 
            AS SELECT EMPNAME, CITY, GRADE
                     FROM STAFFV2
                     WHERE EMPNAME >= 'Betty' AND EMPNUM < 'E35'
                       OR CITY <= 'Deale' AND GRADE > 12
                       OR GRADE = 13 AND CITY <> 'Akron';

  CREATE VIEW UPDATE_VIEW8 
            AS SELECT MYTABLE.EMPNUM, MYTABLE.EMPNAME
                     FROM STAFF MYTABLE
                     WHERE MYTABLE.GRADE = 12;

  CREATE VIEW UPDATE_VIEW9 
            AS SELECT EMPNAME, CITY, GRADE
                     FROM STAFF
                     WHERE NOT EMPNAME >= 'Betty' AND EMPNUM <= 'E35'
                       OR NOT (CITY <= 'Deale') AND GRADE > 9
                       AND NOT (GRADE = 13 AND CITY <> 'Akron')
                       OR NOT CITY IN ('Vienna','New York','Deale');

   CREATE VIEW VSTAFF3 AS SELECT * FROM STAFF3;

disconnect;
exit;