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
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML014
-- SQL Test Suite, V6.0, Interactive SQL, dml014.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0045 BETWEEN predicate!
SELECT PNUM
FROM PROJ
WHERE BUDGET BETWEEN 40000 AND 60000;
PN&
---
P6
ij> -- PASS:0045 If PNUM = 'P6'?
SELECT PNUM
FROM PROJ
WHERE BUDGET >= 40000 AND BUDGET <= 60000;
PN&
---
P6
ij> -- PASS:0045 If PNUM = 'P6'?
-- END TEST >>> 0045 <<< END TEST
-- ***********************************************************
-- TEST:0046 NOT BETWEEN predicate !
SELECT CITY
FROM STAFF
WHERE GRADE NOT BETWEEN 12 AND 13;
CITY
---------------
Vienna
ij> -- PASS:0046 If CITY = 'Vienna'?
SELECT CITY
FROM STAFF
WHERE NOT(GRADE BETWEEN 12 AND 13);
CITY
---------------
Vienna
ij> -- PASS:0046 If CITY = 'Vienna'?
-- END TEST >>> 0046 <<< END TEST
-- *************************************************************
-- TEST:0047 IN predicate!
SELECT STAFF.EMPNAME
FROM STAFF
WHERE STAFF.EMPNUM IN
(SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.CITY='Tampa'));
EMPNAME
--------------------
Alice
ij> -- PASS:0047 If EMPNAME = 'Alice'?
SELECT STAFF.EMPNAME
FROM STAFF
WHERE STAFF.EMPNUM = ANY
(SELECT WORKS.EMPNUM
FROM WORKS
WHERE WORKS.PNUM IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.CITY='Tampa'));
EMPNAME
--------------------
Alice
ij> -- PASS:0047 If EMPNAME = 'Alice'?
-- END TEST >>> 0047 <<< END TEST
-- ***********************************************************
-- TEST:0048 NOT IN predicate!
SELECT WORKS.HOURS
FROM WORKS
WHERE WORKS.PNUM NOT IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.BUDGET BETWEEN 5000 AND 40000);
HOURS
------
12
ij> -- PASS:0048 If HOURS = 12?
SELECT WORKS.HOURS
FROM WORKS
WHERE NOT (WORKS.PNUM IN
(SELECT PROJ.PNUM
FROM PROJ
WHERE PROJ.BUDGET BETWEEN 5000 AND 40000));
HOURS
------
12
ij> -- PASS:0048 If HOURS = 12?
-- END TEST >>> 0048 <<< END TEST
-- ****************************************************************
-- TEST:0049 IN predicate value list!
SELECT HOURS
FROM WORKS
WHERE PNUM NOT IN
(SELECT PNUM
FROM WORKS
WHERE PNUM IN ('P1','P2','P4','P5','P6'));
HOURS
------
80
ij> -- PASS:0049 If HOURS = 80?
SELECT HOURS
FROM WORKS
WHERE NOT (PNUM IN
(SELECT PNUM
FROM WORKS
WHERE PNUM IN ('P1','P2','P4','P5','P6')));
HOURS
------
80
ij> -- PASS:0049 If HOURS = 80?
-- END TEST >>> 0049 <<< END TEST
-- **************************************************************
-- TEST:0050 LIKE predicate -- %!
SELECT EMPNAME
FROM STAFF
WHERE EMPNAME LIKE 'Al%';
EMPNAME
--------------------
Alice
ij> -- PASS:0050 If EMPNAME = 'Alice'?
-- END TEST >>> 0050 <<< END TEST
-- **************************************************************
-- TEST:0051 LIKE predicate -- underscore!
SELECT CITY
FROM STAFF
WHERE EMPNAME LIKE 'B__t%';
CITY
---------------
Vienna
ij> -- PASS:0051 If CITY = 'Vienna'?
-- END TEST >>> 0051 <<< END TEST
-- *************************************************************
-- TEST:0052 LIKE predicate -- ESCAPE character!
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,'Xi_an%');
1 row inserted/updated/deleted
ij> -- PASS:0052 If 1 row is inserted?
--O SELECT CITY
--O FROM STAFF
--O WHERE CITY LIKE 'XiS___S%%'
--O ESCAPE 'S';
-- PASS:0052 If CITY = 'Xi_an%' ?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0052 <<< END TEST
-- **************************************************************
-- TEST:0053 NOT LIKE predicate!
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,'Xi_an%');
1 row inserted/updated/deleted
ij> -- PASS:0053 If 1 row is inserted?
SELECT COUNT(*)
FROM STAFF
WHERE EMPNUM NOT LIKE '_36';
1
-----------
5
ij> -- PASS:0053 If count = 5?
SELECT COUNT(*)
FROM STAFF
WHERE NOT(EMPNUM LIKE '_36');
1
-----------
5
ij> -- PASS:0053 If count = 5?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0053 <<< END TEST
-- ***************************************************************
-- TEST:0054 IS NULL predicate!
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,NULL);
1 row inserted/updated/deleted
ij> -- PASS:0054 If 1 row is inserted?
SELECT EMPNAME
FROM STAFF
WHERE CITY IS NULL;
EMPNAME
--------------------
Huyan
ij> -- PASS:0054 If EMPNAME = 'Huyan'?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0054 <<< END TEST
-- ************************************************************
-- TEST:0055 NOT NULL predicate!
-- setup
INSERT INTO STAFF
VALUES('E36','Huyan',36,NULL);
1 row inserted/updated/deleted
ij> -- PASS:0055 If 1 row is inserted?
SELECT COUNT(*)
FROM STAFF;
1
-----------
6
ij> -- PASS:0055 If count = 6?
SELECT COUNT(*)
FROM STAFF
WHERE CITY IS NOT NULL;
1
-----------
5
ij> -- PASS:0055 If count = 5?
SELECT COUNT(*)
FROM STAFF
WHERE NOT (CITY IS NULL);
1
-----------
5
ij> -- PASS:0055 If count = 5?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0055 <<< END TEST
-- ***************************************************************
-- TEST:0056 NOT EXISTS predicate!
SELECT STAFF.EMPNAME
FROM STAFF
WHERE NOT EXISTS
(SELECT *
FROM PROJ
WHERE NOT EXISTS
(SELECT *
FROM WORKS
WHERE STAFF.EMPNUM = WORKS.EMPNUM
AND WORKS.PNUM=PROJ.PNUM));
EMPNAME
--------------------
Alice
ij> -- PASS:0056 If EMPNAME = 'Alice'?
-- END TEST >>> 0056 <<< END TEST
-- ************************************************************
-- TEST:0057 ALL quantifier !
SELECT CITY
FROM PROJ
WHERE BUDGET > ALL
(SELECT BUDGET
FROM PROJ
WHERE CITY='Vienna');
CITY
---------------
Deale
ij> -- PASS:0057 If CITY = 'Deale'?
-- END TEST >>> 0057 <<< END TEST
-- **************************************************************
-- TEST:0058 SOME quantifier!
SELECT EMPNAME
FROM STAFF
WHERE GRADE < SOME
(SELECT BUDGET/1000 - 39
FROM PROJ
WHERE CITY='Deale');
EMPNAME
--------------------
Betty
ij> -- PASS:0058 If EMPNAME = 'Betty'?
-- END TEST >>> 0058 <<< END TEST
-- *************************************************************
-- TEST:0059 ANY quantifier !
SELECT EMPNAME
FROM STAFF
WHERE GRADE < ANY
(SELECT BUDGET/1000 - 39
FROM PROJ
WHERE CITY = 'Deale');
EMPNAME
--------------------
Betty
ij> -- PASS:0059 If EMPNAME = 'Betty'?
-- END TEST >>> 0059 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
