1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
|
ij> -- 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;
0 rows inserted/updated/deleted
ij> set schema hu;
0 rows inserted/updated/deleted
ij> CREATE TABLE BASE_TESTREPORT
(TESTNO CHAR(4),
RESULT CHAR(4),
TESTTYPE CHAR(3));
0 rows inserted/updated/deleted
ij> CREATE TABLE HU.ECCO (C1 CHAR(2));
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF
(EMPNUM CHAR(3) NOT NULL CONSTRAINT STAFF_UNIQUE UNIQUE,
EMPNAME CHAR(20),
GRADE DECIMAL(4),
CITY CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE PROJ
(PNUM CHAR(3) NOT NULL UNIQUE,
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),
--O UNIQUE(EMPNUM,PNUM))
constraint worksc1 UNIQUE(EMPNUM,PNUM)
);
0 rows inserted/updated/deleted
ij> CREATE TABLE FIPS1
(FIPS_TEST CHAR(20));
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 PROJ1 (PNUM CHAR(3) NOT NULL UNIQUE,
PNAME CHAR(20),
PTYPE CHAR(6),
BUDGET DECIMAL(9),
CITY CHAR(15));
0 rows inserted/updated/deleted
ij> 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)
);
0 rows inserted/updated/deleted
ij> CREATE TABLE STAFF3 (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> -- we use the keyword BIGINT, so I have changed
-- the name of this table.
CREATE TABLE LONGINTTAB (LONG_INT DECIMAL(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEMP_S
(EMPNUM CHAR(3),
GRADE DECIMAL(4),
CITY CHAR(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE TMP (T1 CHAR (10), T2 DECIMAL(2), T3 CHAR (10));
0 rows inserted/updated/deleted
ij> CREATE TABLE AA (CHARTEST CHAR(20));
0 rows inserted/updated/deleted
ij> CREATE TABLE BB (CHARTEST CHAR);
0 rows inserted/updated/deleted
ij> CREATE TABLE CC (CHARTEST CHARACTER(20));
0 rows inserted/updated/deleted
ij> CREATE TABLE DD (CHARTEST CHARACTER);
0 rows inserted/updated/deleted
ij> CREATE TABLE EE (INTTEST INTEGER);
0 rows inserted/updated/deleted
ij> CREATE TABLE FF (INTTEST INT);
0 rows inserted/updated/deleted
ij> CREATE TABLE GG (REALTEST REAL);
0 rows inserted/updated/deleted
ij> CREATE TABLE HH (SMALLTEST SMALLINT);
0 rows inserted/updated/deleted
ij> CREATE TABLE II (DOUBLETEST DOUBLE PRECISION);
0 rows inserted/updated/deleted
ij> CREATE TABLE JJ (FLOATTEST FLOAT);
0 rows inserted/updated/deleted
ij> CREATE TABLE KK (FLOATTEST FLOAT(32));
0 rows inserted/updated/deleted
ij> CREATE TABLE LL (NUMTEST NUMERIC(13,6));
0 rows inserted/updated/deleted
ij> CREATE TABLE MM (NUMTEST NUMERIC);
0 rows inserted/updated/deleted
ij> CREATE TABLE MM2 (NUMTEST NUMERIC(10));
0 rows inserted/updated/deleted
ij> CREATE TABLE NN (NUMTEST NUMERIC(9));
0 rows inserted/updated/deleted
ij> CREATE TABLE OO (NUMTEST NUMERIC(9));
0 rows inserted/updated/deleted
ij> CREATE TABLE PP (NUMTEST DECIMAL(13,6));
0 rows inserted/updated/deleted
ij> CREATE TABLE QQ (NUMTEST DECIMAL);
0 rows inserted/updated/deleted
ij> CREATE TABLE RR (NUMTEST DECIMAL(8));
0 rows inserted/updated/deleted
ij> CREATE TABLE SS (NUMTEST DEC(13,6));
0 rows inserted/updated/deleted
ij> CREATE TABLE P1 (NUMTEST NUMERIC(1));
0 rows inserted/updated/deleted
ij> CREATE TABLE P7 (NUMTEST NUMERIC(7));
0 rows inserted/updated/deleted
ij> CREATE TABLE P12 (NUMTEST NUMERIC(12));
0 rows inserted/updated/deleted
ij> CREATE TABLE P15 (NUMTEST NUMERIC(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 UPUNIQ (NUMKEY DECIMAL(3) NOT NULL UNIQUE,
COL2 CHAR(2));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEXT80 (TEXXT CHAR(80));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEXT132 (TEXXT CHAR(132));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEXT240 (TEXXT CHAR(240));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEXT256 (TEXXT VARCHAR(256));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEXT512 (TEXXT VARCHAR(512));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEXT1024 (TEXXT VARCHAR(1024));
0 rows inserted/updated/deleted
ij> -- The following tables are used to test the limitations (12-14-88)
CREATE TABLE T240(STR240 CHAR(240));
0 rows inserted/updated/deleted
ij> CREATE TABLE DEC15(COL1 DECIMAL(15,7));
0 rows inserted/updated/deleted
ij> CREATE TABLE FLO15(COL1 FLOAT(15));
0 rows inserted/updated/deleted
ij> CREATE TABLE INT10(COL1 INTEGER, COL2 SMALLINT);
0 rows inserted/updated/deleted
ij> 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));
0 rows inserted/updated/deleted
ij> 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));
0 rows inserted/updated/deleted
ij> 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));
0 rows inserted/updated/deleted
ij> CREATE TABLE T118(STR118 CHAR(118) NOT NULL UNIQUE);
0 rows inserted/updated/deleted
ij> 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)
);
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
ij> CREATE TABLE NEXTKEY (KEYNUM INTEGER, AUTHOR CHAR(1),
DOLLARS INTEGER);
0 rows inserted/updated/deleted
ij> CREATE TABLE SV (NUMTEST NUMERIC(8,3));
0 rows inserted/updated/deleted
ij> CREATE TABLE JJ_20 (FLOATTEST FLOAT(20));
0 rows inserted/updated/deleted
ij> CREATE TABLE PP_15 (NUMTEST DECIMAL(15,15));
0 rows inserted/updated/deleted
ij> CREATE TABLE PP_7 (NUMTEST DECIMAL(15,7));
0 rows inserted/updated/deleted
ij> CREATE TABLE P15_15 (NUMTEST NUMERIC(15,15));
0 rows inserted/updated/deleted
ij> CREATE TABLE P15_7 (NUMTEST NUMERIC(15,7));
0 rows inserted/updated/deleted
ij> CREATE TABLE TEMP_OBSERV
(YEAR_OBSERV NUMERIC(4),
CITY CHAR(10),
MAX_TEMP NUMERIC(5,2),
MIN_TEMP NUMERIC(5,2));
0 rows inserted/updated/deleted
ij> CREATE TABLE TOKENS
(PROG_NO INT, TOKEN_NO INT);
0 rows inserted/updated/deleted
ij> CREATE TABLE WHICH_SCHEMA1 (C1 CHAR (50));
0 rows inserted/updated/deleted
ij> -- ************* create view statements follow *************
CREATE VIEW TESTREPORT AS
SELECT TESTNO, RESULT, TESTTYPE
FROM BASE_TESTREPORT;
0 rows inserted/updated/deleted
ij> 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;
0 rows inserted/updated/deleted
ij> CREATE VIEW MULTI_YEAR_OBSERV (CITY, HIGH, LOW)
AS SELECT CITY, AVG(MAX_TEMP), AVG(MIN_TEMP)
FROM TEMP_OBSERV
GROUP BY CITY;
0 rows inserted/updated/deleted
ij> 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;
0 rows inserted/updated/deleted
ij> CREATE VIEW SET_TEST (EMP1, EMP_AVG, EMP_MAX) AS
SELECT STAFF.EMPNUM, AVG(HOURS), MAX(HOURS)
FROM STAFF, WORKS
GROUP BY STAFF.EMPNUM;
0 rows inserted/updated/deleted
ij> CREATE VIEW DUP_COL (EMP1, PNO, HOURS, HOURS_2) AS
SELECT EMPNUM, PNUM, HOURS, HOURS * 2
FROM WORKS;
0 rows inserted/updated/deleted
ij> CREATE VIEW STAFFV1
AS SELECT * FROM STAFF
WHERE GRADE >= 12;
0 rows inserted/updated/deleted
ij> CREATE VIEW STAFFV2
AS SELECT * FROM STAFF
WHERE GRADE >= 12
--0 WITH CHECK OPTION
;
0 rows inserted/updated/deleted
ij> CREATE VIEW STAFFV2_VIEW
AS SELECT *
FROM STAFFV2
WHERE CITY = 'Vienna';
0 rows inserted/updated/deleted
ij> 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
;
0 rows inserted/updated/deleted
ij> CREATE VIEW STAFF2
AS SELECT *
FROM STAFF
--0 WITH CHECK OPTION
;
0 rows inserted/updated/deleted
ij> 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';
0 rows inserted/updated/deleted
ij> CREATE VIEW SUBSP (EMPNUM,PNUM,HOURS)
AS SELECT EMPNUM,PNUM,HOURS
FROM WORKS
WHERE EMPNUM='E3'
--0 WITH CHECK OPTION
;
0 rows inserted/updated/deleted
ij> CREATE VIEW TEMP_SS(EMPNUM,GRADE,CITY)
AS SELECT EMPNUM,GRADE,CITY
FROM STAFF
WHERE GRADE > 12
--0 WITH CHECK OPTION
;
0 rows inserted/updated/deleted
ij> CREATE VIEW V_WORKS1
AS SELECT * FROM WORKS
WHERE HOURS > 15
--0 WITH CHECK OPTION
;
0 rows inserted/updated/deleted
ij> CREATE VIEW V_WORKS2
AS SELECT * FROM V_WORKS1
WHERE EMPNUM = 'E1'
OR EMPNUM = 'E6';
0 rows inserted/updated/deleted
ij> CREATE VIEW V_WORKS3
AS SELECT * FROM V_WORKS2
WHERE PNUM = 'P2'
OR PNUM = 'P7'
--0 WITH CHECK OPTION
;
0 rows inserted/updated/deleted
ij> CREATE VIEW UPDATE_VIEW1
AS SELECT ALL CITY
FROM PROJ;
0 rows inserted/updated/deleted
ij> CREATE VIEW UPDATE_VIEW2
AS SELECT HOURS, EMPNUM, PNUM
FROM WORKS
WHERE HOURS IN (10, 20, 40);
0 rows inserted/updated/deleted
ij> CREATE VIEW UPDATE_VIEW3
AS SELECT *
FROM WORKS
WHERE PNUM BETWEEN 'P2' AND 'P4'
AND EMPNUM NOT BETWEEN 'E2' AND 'E3';
0 rows inserted/updated/deleted
ij> CREATE VIEW UPDATE_VIEW4
AS SELECT PNUM, EMPNUM
FROM WORKS
WHERE PNUM LIKE '_2%';
0 rows inserted/updated/deleted
ij> CREATE VIEW UPDATE_VIEW5
AS SELECT *
FROM STAFF
WHERE EMPNAME IS NOT NULL AND CITY IS NULL;
0 rows inserted/updated/deleted
ij> 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';
0 rows inserted/updated/deleted
ij> 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';
0 rows inserted/updated/deleted
ij> CREATE VIEW UPDATE_VIEW8
AS SELECT MYTABLE.EMPNUM, MYTABLE.EMPNAME
FROM STAFF MYTABLE
WHERE MYTABLE.GRADE = 12;
0 rows inserted/updated/deleted
ij> 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');
0 rows inserted/updated/deleted
ij> CREATE VIEW VSTAFF3 AS SELECT * FROM STAFF3;
0 rows inserted/updated/deleted
ij> disconnect;
ij>
|
