ij> AUTOCOMMIT OFF; ij> --O-- SQL Test Suite, V6.0, Schema Definition, cts5sch2.sql --O-- 59-byte ID --O-- TEd Version # --O-- date_time print --O-- *************************************************************** --O-- ****** THIS FILE SHOULD BE RUN UNDER SCHEMA ID CTS1 ****** --O-- *************************************************************** --O --O-- The following command is supported only at INTERMEDIATE level --O CREATE SCHEMA CTS1; --O --O-- The following command should be used if ENTRY level rather than --O-- intermediate is supported. --O-- CREATE SCHEMA AUTHORIZATION CTS1; CREATE SCHEMA CTS1; 0 rows inserted/updated/deleted ij> set schema CTS1; 0 rows inserted/updated/deleted ij> --O --O-- ************* create character set statements ***** --O --O CREATE CHARACTER SET CS GET SQL_TEXT; --O --O-- ************* create table statements ************* --O --O CREATE TABLE ECCO (C1 CHAR(2)); 0 rows inserted/updated/deleted ij> CREATE TABLE FIPS1 (FIPS_TEST CHAR(20)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFF (EMPNUM CHAR(3) NOT NULL UNIQUE, EMPNAME CHAR(20), GRADE DECIMAL(4), 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), UNIQUE(EMPNUM,PNUM)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFF1 (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFF4 (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); 0 rows inserted/updated/deleted ij> CREATE TABLE VTABLE (COL1 INTEGER, COL2 INTEGER, COL3 INTEGER, COL4 INTEGER, COL5 DECIMAL(7,2)); 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 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 WORKS3a (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5), FOREIGN KEY (PNUM) REFERENCES PROJ3(PNUM)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFFa ( HOURS INTEGER, SALARY DECIMAL(6), EMPNUM CHAR(3), PNUM DECIMAL(4), EMPNAME CHAR(20)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFFb ( SALARY DECIMAL(6), EMPNAME CHAR(20), HOURS INTEGER, PNUM CHAR(3), CITY CHAR(15), SEX CHAR); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFFc ( EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15), MGR CHAR(3), UNIQUE (EMPNUM)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFFd ( EMPNUM CHAR(3) NOT NULL, GRADE DECIMAL(4), MGR CHAR(3)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFF_CTS ( PNUM CHAR(3), CITY CHAR(15), GRADE DECIMAL(4), EMPNAME CHAR(20)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFFz ( EMPNUM CHAR(3) REFERENCES STAFF3(EMPNUM), SALARY DECIMAL(6) CHECK (SALARY > 0)); 0 rows inserted/updated/deleted ij> CREATE TABLE PROJ_DURATION ( MONTHS INTEGER, TIME_LEFT INTEGER, EMP_HOURS INTEGER, CHECK (MONTHS > 0)); 0 rows inserted/updated/deleted ij> CREATE TABLE STAFF_CTS2 (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); 0 rows inserted/updated/deleted ij> CREATE TABLE EMPLOYEES2 ( name CHAR(10), empno INTEGER); 0 rows inserted/updated/deleted ij> CREATE TABLE A ( p INTEGER, q INTEGER ); 0 rows inserted/updated/deleted ij> CREATE TABLE TT (TTA INTEGER, TTB INTEGER, TTC INTEGER); 0 rows inserted/updated/deleted ij> CREATE TABLE TU (TUD CHAR(2), TUE INTEGER); 0 rows inserted/updated/deleted ij> --O CREATE TABLE TT2 --O (TTA INTEGER, --O TTB INTERVAL YEAR TO MONTH, --O TTC DECIMAL(6,0)); --O CREATE TABLE TV (A INTEGER, B CHAR); 0 rows inserted/updated/deleted ij> CREATE TABLE TW (D CHAR, E INTEGER); 0 rows inserted/updated/deleted ij> CREATE TABLE TX (TX1 INTEGER, TX2 CHARACTER(5), TX3 CHARACTER VARYING (10)); 0 rows inserted/updated/deleted ij> CREATE TABLE COMP_BUDG (P_REF CHAR(3) NOT NULL, BUDGET DECIMAL(20), HOURS INTEGER, SALARY DECIMAL(6), FOREIGN KEY (P_REF) REFERENCES CTS2.PROJ_MAN(P_REF)); ERROR 42Y07: Schema 'CTS2' does not exist ij> CREATE TABLE PROJ_STATUS ( MGR CHAR(15) REFERENCES CTS2.PROJ_MAN(MGR), P_REF CHAR(3), ONTIME CHAR, BUDGET DECIMAL(20), COST DECIMAL(20)); ERROR 42Y07: Schema 'CTS2' does not exist ij> CREATE TABLE DATA_TYPE ( NUM NUMERIC, --O DEC DECIMAL, DECI DECIMAL, ING INTEGER, SMA SMALLINT, FLO FLOAT, REA REAL, DOU DOUBLE PRECISION); 0 rows inserted/updated/deleted ij> --O CREATE TABLE TTIME_BASE --O (PK INTEGER, --O TT TIME, --O TS TIMESTAMP, --O TT2 TIME WITH TIME ZONE, --O TS2 TIMESTAMP WITH TIME ZONE, --O PRIMARY KEY (PK)); CREATE TABLE CL_DATA_TYPE (CL_CHAR CHAR(10), CL_NUM NUMERIC, CL_DEC DECIMAL, CL_REAL REAL); 0 rows inserted/updated/deleted ij> CREATE TABLE CL_EMPLOYEE (EMPNUM NUMERIC(5) NOT NULL PRIMARY KEY, DEPTNO CHAR(3), LOC CHAR(15), EMPNAME CHAR(20), SALARY DECIMAL(6), GRADE DECIMAL(4), HOURS DECIMAL(5)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST6740A (TNUM NUMERIC(4), TCHARA CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST6740B (TNUM NUMERIC(4), TCHARB CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST6740C (TNUMERIC NUMERIC(4), TCHAR CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST6840A (NUM_A NUMERIC(4), CH_A CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST6840B (NUM_B NUMERIC(4), CH_B CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST6840C (NUM_C1 NUMERIC(4), CH_C1 CHAR(10), NUM_C2 NUMERIC(4), CH_C2 CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TEST12849B (col_num3 NUMERIC(3) NOT NULL, PRIMARY KEY (col_num3)); 0 rows inserted/updated/deleted ij> --RESOLVE: we don't support CASCADE --O CREATE TABLE TEST12849A --O (col_num1 NUMERIC(5) PRIMARY KEY, --O col_str1 VARCHAR(15) NOT NULL, --O col_str2 VARCHAR(10), --O col_num2 NUMERIC(5) CONSTRAINT constr_1 REFERENCES TEST12849A, --O col_str3 VARCHAR(25), --O col_num3 NUMERIC(7,2), --O col_num4 NUMERIC(3) NOT NULL --O CONSTRAINT constr_3 REFERENCES TEST12849B --O ON DELETE CASCADE, --O CONSTRAINT constr_2 UNIQUE (col_str1, col_str2)); CREATE TABLE T4 (STR110 CHAR(110) NOT NULL, NUM6 NUMERIC(6) NOT NULL, COL3 CHAR(10), COL4 CHAR(20), UNIQUE(STR110,NUM6)); 0 rows inserted/updated/deleted ij> CREATE TABLE EMPTY740 (COL_1 CHAR(10), COL_2 VARCHAR(5), COL_3 NUMERIC(5), COL_4 DECIMAL(6), COL_5 TIME); 0 rows inserted/updated/deleted ij> CREATE TABLE TABX760 ( DEPTNO NUMERIC(5) UNIQUE NOT NULL, EMPNAME CHAR(20) UNIQUE NOT NULL, SALARY DECIMAL(7)); 0 rows inserted/updated/deleted ij> --OCREATE TABLE TABCS --O ( COLUN NUMERIC(5) UNIQUE, --O COLSTR1 CHAR(10) CHARACTER SET CS, --O COLSTR2 VARCHAR(10) CHARACTER SET CS); CREATE TABLE CL_STANDARD ( COL_NUM1 NUMERIC(4), COL_CH1 CHAR(10), COL_NUM2 NUMERIC(4), COL_CH2 CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TABLE728a ( C1 CHAR(10), C2 CHAR(10)); 0 rows inserted/updated/deleted ij> CREATE TABLE TABLE728b ( COL_1 CHAR(10), COL_2 CHAR(10)); 0 rows inserted/updated/deleted ij> --O CREATE TABLE TAB734 --O ( CSTR1 NCHAR(10), --O CSTR2 NCHAR VARYING(12)); --O-- LATIN1 is not required by SQL-92 DWF 1996-02-21 --O-- CREATE TABLE TABLATIN1 --O-- ( COL1 CHARACTER(10) CHARACTER SET LATIN1, --O-- COL2 CHAR(12) CHARACTER SET LATIN1, --O-- COL3 VARCHAR(15) CHARACTER SET LATIN1, --O-- COL4 NUMERIC(5)); CREATE TABLE ET (col1 CHAR(3), col2 CHAR(20), col3 DECIMAL(4), col4 CHAR(15), col5 INTEGER, col6 INTEGER); 0 rows inserted/updated/deleted ij> CREATE TABLE TTSTORE (numx INTEGER, colthu INTEGER, coltmu INTEGER, TT TIME); 0 rows inserted/updated/deleted ij> --O CREATE TABLE TTSTORE2 --O (num INTEGER, --O colthu INTEGER, --O coltmu INTEGER, --O TT2 TIME WITH TIME ZONE); CREATE TABLE CONCATBUF (ZZ CHAR(240)); 0 rows inserted/updated/deleted ij> CREATE VIEW TESTREPORT AS SELECT TESTNO, RESULT, TESTTYPE FROM HU.TESTREPORT; 0 rows inserted/updated/deleted ij> --OCREATE VIEW TTIME (PK, TT, TS) AS --O SELECT PK, TT, TS --O FROM TTIME_BASE; --O --OCREATE VIEW TTIME2 (PK, TT2, TS2) AS --O SELECT PK, TT2, TS2 --O FROM TTIME_BASE; --O --OCREATE VIEW TTIME3 (PK, TT, TT2, TS2) AS --O SELECT PK, TT, TT2, TS2 --O FROM TTIME_BASE; --O --O-- ************* create domain statements *********** --O --O CREATE DOMAIN esal AS INTEGER --O CHECK (VALUE > 500); --O --O CREATE DOMAIN atom CHARACTER --O CHECK ('a' <= VALUE) --O CHECK ('m' >= VALUE); --O --O CREATE DOMAIN smint INTEGER --O CHECK (1<= VALUE) --O CHECK (100 >= VALUE); --O --O-- ************* grant statements follow ************* --O --O GRANT ALL PRIVILEGES ON CONCATBUF TO PUBLIC; --O --O GRANT SELECT ON CTS1.ECCO TO PUBLIC; --O --O GRANT INSERT ON TESTREPORT TO PUBLIC WITH GRANT OPTION; --O --O GRANT SELECT ON CTS1.DATA_TYPE TO CTS4; --O --O-- ************* End of Schema ************* commit; ij>