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
|
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 CONSTRAINT FEAT16_PK PRIMARY KEY,
PNUM INT NOT NULL CONSTRAINT FEAT16_PNUM 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,
CONSTRAINT BARNO_UNIQUE UNIQUE (X2, X1),
CONSTRAINT BARNO_PK 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 'FEAT16_PK' 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 'FEAT16_PNUM' 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 'BARNO_PK' 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 'BARNO_UNIQUE' 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>
|
