schema8.out

来自「derby database source code.good for you.」· OUT 代码 · 共 538 行 · 第 1/2 页

ij> -- SQL Test Suite, V6.0, Schema Definition, schema8.smi
-- 59-byte ID
-- TEd Version #
-- date_time print
-- ***************************************************************
-- ****** THIS FILE SHOULD BE RUN UNDER AUTHORIZATION ID SUN *****
-- ***************************************************************
-- This file defines the base tables used in most of the CDR tests.
-- This non-standard schema definition is provided so that
-- implementations which require semicolons to terminate statements,
-- but which are otherwise conforming, can still execute the
-- remaining tests.
  CREATE SCHEMA
--O      AUTHORIZATION SUN;
      SUN;
0 rows inserted/updated/deleted
ij> set schema SUN;
0 rows inserted/updated/deleted
ij> CREATE TABLE SUN.ECCO (C1 CHAR(2));
0 rows inserted/updated/deleted
ij> --O  CREATE TABLE ECCO (C1 CHAR(2));
  CREATE TABLE STAFF
   (EMPNUM   CHAR(3) NOT NULL,
    EMPNAME  CHAR(20),
    GRADE    DECIMAL(4),
    CITY     CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE PROJ
   (PNUM     CHAR(3) NOT NULL,
    PNAME    CHAR(20),
    PTYPE    CHAR(6),
    BUDGET   DECIMAL(9),
    CITY     CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE WORKS
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF3
   (EMPNUM   CHAR(3) NOT NULL,
    EMPNAME  CHAR(20),
    GRADE    DECIMAL(4),
    CITY     CHAR(15),
    UNIQUE (EMPNUM));
0 rows inserted/updated/deleted
ij> CREATE TABLE PROJ3
   (PNUM     CHAR(3) NOT NULL,
    PNAME    CHAR(20),
    PTYPE    CHAR(6),
    BUDGET   DECIMAL(9),
    CITY     CHAR(15),
    UNIQUE (PNUM));
0 rows inserted/updated/deleted
ij> CREATE TABLE WORKS3
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5),
    FOREIGN KEY (EMPNUM) REFERENCES STAFF3(EMPNUM),
    FOREIGN KEY (PNUM) REFERENCES PROJ3(PNUM));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF4 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20) DEFAULT NULL,
--O                GRADE DECIMAL(4) DEFAULT 0,
--O                CITY   CHAR(15) DEFAULT '               ');
                GRADE DECIMAL(4) ,
                CITY   CHAR(15) );
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF14 (EMPNUM    CHAR(3) NOT NULL,
--O                EMPNAME  CHAR(20) DEFAULT USER,
                EMPNAME  CHAR(20) ,
        -- EMPNAME CHAR precision may be changed to implementation-defined
        --              precision for value of USER
                GRADE DECIMAL(4),
                CITY   CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF5 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (GRADE > 0 AND GRADE < 20));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF6 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4) CHECK (GRADE > 0 AND GRADE < 20),
                CITY   CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF7 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (GRADE BETWEEN 1 AND 20));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF8 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (EMPNAME IS NOT NULL));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF9 (EMPNUM    CHAR(3) NOT NULL PRIMARY KEY,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                CHECK (EMPNAME NOT LIKE 'T%'));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF10 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (GRADE NOT IN (5,22)));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF11 (EMPNUM    CHAR(3) NOT NULL PRIMARY KEY,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                CHECK (GRADE NOT IN (5,22) 
                            AND EMPNAME NOT LIKE 'T%'));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF12 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (NOT GRADE IN (5,22) 
                            AND NOT EMPNAME LIKE 'T%'));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF13 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (NOT EMPNAME IS NULL));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF15 (EMPNUM    CHAR(3),
                EMPNAME  CHAR(20) NOT NULL,
                GRADE DECIMAL(4),
                CITY   CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF16 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20) DEFAULT NULL,
                GRADE DECIMAL(4) NOT NULL CHECK (GRADE IN (100,150,200)),
                CITY   CHAR(15), PRIMARY KEY (GRADE,EMPNUM));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ1_P
           (S1   CHAR(3) NOT NULL,
            S2   CHAR(3) NOT NULL,
            S3   DECIMAL(4) NOT NULL,
            S4   CHAR(3) NOT NULL,
            S5   DECIMAL(4) NOT NULL,
            S6   CHAR(3) NOT NULL,
            R1   CHAR(3),
            R2   CHAR(3),
            R3   DECIMAL(4),
            UNIQUE   (S1,S2,S3,S4,S5,S6));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ1_F
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(3),
            F3   DECIMAL(4),
            F4   CHAR(3),
            F5   DECIMAL(4),
            F6   CHAR(3),
            R1   CHAR(3),
            R2   DECIMAL(5),
            R3   DECIMAL(4),
           FOREIGN KEY   (F1,F2,F3,F4,F5,F6)
           REFERENCES SIZ1_P(S1,S2,S3,S4,S5,S6));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_P
           (P1   CHAR(3) NOT NULL,
            P2   CHAR(3) NOT NULL,
            P3   DECIMAL(4) NOT NULL,
            P4   CHAR(3) NOT NULL,
            P5   DECIMAL(4) NOT NULL,
            P6   CHAR(3) NOT NULL,
            P7   CHAR(3) NOT NULL,
            P8   DECIMAL(4) NOT NULL,
            P9   DECIMAL(4) NOT NULL,
            P10   DECIMAL(4) NOT NULL,
            P11   CHAR(4),
            UNIQUE (P1),
            UNIQUE (P2),
            UNIQUE (P3),
            UNIQUE (P4),
            UNIQUE (P5),
            UNIQUE (P6),
            UNIQUE (P7),
            UNIQUE (P8),
            UNIQUE (P9),
            UNIQUE (P10));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F1
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P1));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F2
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P2));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F3
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P3));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F4
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P4));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F5
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P5));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F6
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P6));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F7
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P7));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F8
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P8));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F9
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P9));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ2_F10
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P10));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ3_P1
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ3_P2
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
0 rows inserted/updated/deleted
ij> CREATE TABLE SIZ3_P3
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
           UNIQUE (F1));