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
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML147
-- SQL Test Suite, V6.0, Interactive SQL, dml147.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
--O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
-- date_time print
-- TEST:0840 Roll back schema manipulation !
CREATE TABLE NOT_THERE (C1 CHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0840 If table is created?
ROLLBACK WORK;
ij> INSERT INTO NOT_THERE VALUES ('1234567890');
ERROR 42X05: Table/View 'NOT_THERE' does not exist.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
ROLLBACK WORK;
ij> CREATE VIEW NOT_HERE AS
SELECT * FROM USIG;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If view is created?
ROLLBACK WORK;
ij> SELECT COUNT (*) FROM NOT_HERE;
ERROR 42X05: Table/View 'NOT_HERE' does not exist.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
ROLLBACK WORK;
ij> ALTER TABLE USIG
ADD COLUMN NUL INT;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If column is added?
ROLLBACK WORK;
ij> SELECT COUNT (*)
FROM USIG WHERE NUL IS NULL;
ERROR 42X04: Column 'NUL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NUL' is not a column in the target table.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
ROLLBACK WORK;
ij> --O DROP TABLE USIG CASCADE;
DROP TABLE USIG ;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If table is dropped?
ROLLBACK WORK;
ij> SELECT COUNT(*)
FROM U_SIG;
1
-----------
2
ij> -- PASS:0840 If count = 2?
ROLLBACK WORK;
ij> SELECT COUNT(*)
FROM USIG;
1
-----------
2
ij> -- PASS:0840 If count = 2?
ROLLBACK WORK;
ij> -- END TEST >>> 0840 <<< END TEST
-- *********************************************
-- TEST:0841 Multiple-join and default order of joins !
-- setup
DELETE FROM HU.STAFF4;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> INSERT INTO HU.STAFF4
SELECT * FROM HU.STAFF3
WHERE EMPNUM > 'E3';
2 rows inserted/updated/deleted
ij> --O SELECT EMPNUM FROM
SELECT a.EMPNUM FROM
--O HU.STAFF3 NATURAL LEFT JOIN HU.STAFF NATURAL INNER JOIN HU.STAFF4
HU.STAFF3 a, HU.staff b, HU.staff4 c
where a.empnum = b.empnum and b.empnum = c.empnum
ORDER BY EMPNUM DESC;
EM&
---
E5
E4
ij> -- PASS:0841 If 2 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E5, E4 ?
--O SELECT EMPNUM FROM
--O (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF) NATURAL INNER JOIN HU.STAFF4
--O ORDER BY EMPNUM ASC;
-- PASS:0841 If 2 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E4, E5 ?
--O SELECT EMPNUM FROM
--O HU.STAFF3 NATURAL LEFT JOIN (HU.STAFF NATURAL INNER JOIN HU.STAFF4)
--O ORDER BY EMPNUM;
--O ;
-- PASS:0841 If 5 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E1, E2, E3, E4, E5 ?
ROLLBACK WORK;
ij> -- END TEST >>> 0841 <<< END TEST
-- *********************************************
-- TEST:0842 Multi-column joins !
-- setup
CREATE TABLE STAFF66 (
SALARY INTEGER,
EMPNAME CHAR(20),
GRADE DECIMAL,
EMPNUM CHAR(3));
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- setup
INSERT INTO STAFF66
SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM
FROM HU.STAFF3 WHERE EMPNUM > 'E2';
3 rows inserted/updated/deleted
ij> -- PASS:0842 If 3 rows inserted ?
UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM = 'E5';
1 row inserted/updated/deleted
ij> -- PASS:0842 If 1 row updated ?
UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE = 12;
2 rows inserted/updated/deleted
ij> -- PASS:0842 If 2 rows updated ?
-- FULL OUTER JOIN of tables with unique data in the joined column
--O SELECT EMPNUM, CITY, SALARY
--O FROM HU.STAFF3 LEFT JOIN STAFF66 USING (EMPNUM)
--O UNION
--O SELECT EMPNUM, CITY, SALARY
--O FROM HU.STAFF3 RIGHT JOIN STAFF66 USING (EMPNUM)
--O ORDER BY EMPNUM;
-- PASS:0842 If 6 rows selected with ordered rows and column values ?
-- PASS:0842 E1 Deale NULL ?
-- PASS:0842 E2 Vienna NULL ?
-- PASS:0842 E3 Vienna 13000 ?
-- PASS:0842 E4 Deale 12000 ?
-- PASS:0842 E5 NULL 13000 ?
-- PASS:0842 E6 Akron NULL ?
-- 7.5 SR 6 d
-- table STAFF66 has 3 rows, only 1 matching on all columns
-- this is a 3-column join:
SELECT * FROM
--O STAFF66 NATURAL INNER JOIN HU.STAFF3;
STAFF66 a, HU.staff3 b where a.empnum = b.empnum
and a.grade = b.grade
and a.empname = b.empname;
SALARY |EMPNAME |GRADE |EMP&|EM&|EMPNAME |GRADE|CITY
-------------------------------------------------------------------------------------------
13000 |Carmen |13 |E3 |E3 |Carmen |13 |Vienna
ij> -- PASS:0842 If 1 row selected?
-- PASS:0842 If column values are in the exact order: ?
-- PASS:0842 EMPNAME=Carmen,GRADE=13,EMPNUM=E3,SALARY=13000,CITY=Vienna?
-- table STAFF66 has 3 rows, only 1 matching on all columns
-- this is a 3-column join, preserving HU.STAFF3:
--O SELECT EMPNUM, EMPNAME, SALARY FROM
--O HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66
--O WHERE EMPNUM > 'E1'
--O ORDER BY EMPNUM ASC;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842 E2 Betty NULL ?
-- PASS:0842 E3 Carmen 13000 ?
-- PASS:0842 E4 Ali NULL ?
-- PASS:0842 E6 Ed NULL ?
-- table HU.STAFF has 5 rows, only 3 matching on all columns
-- this is a 3-column join, preserving HU.STAFF:
--O SELECT EMPNUM, EMPNAME, SALARY FROM
--O STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF
--O WHERE EMPNUM > 'E1'
--O ORDER BY EMPNUM DESC;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842 E5 Ed 13000 ?
-- PASS:0842 E4 Don 12000 ?
-- PASS:0842 E3 Carmen 13000 ?
-- PASS:0842 E2 Betty NULL ?
-- table HU.STAFF has 5 rows, only 3 matching on all columns
-- ordinal position is determined by order in T1, not USING list
-- REF: 7.5 SR 6 d
-- this is a 3-column join, preserving HU.STAFF:
--O SELECT * FROM
--O STAFF66 RIGHT JOIN HU.STAFF USING ( GRADE, EMPNUM, EMPNAME)
--O WHERE EMPNUM > 'E1'
--O ORDER BY EMPNUM;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842 Betty 10 E2 NULL Vienna ?
-- PASS:0842 Carmen 13 E3 13000 Vienna ?
-- PASS:0842 Don 12 E4 12000 Deale ?
-- PASS:0842 Ed 13 E5 13000 Akron ?
-- table STAFF66 has 3 rows, with 2 matching on named columns
-- this is a 2-column join, preserving HU.STAFF3:
--O SELECT * FROM
--O HU.STAFF3 LEFT JOIN STAFF66 USING (GRADE, EMPNUM)
--O WHERE EMPNUM > 'E1'
--O ORDER BY EMPNUM ASC;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842 E2 10 Betty Vienna NULL NULL ?
-- PASS:0842 E3 13 Carmen Vienna 13000 Carmen ?
-- PASS:0842 E4 12 Ali Deale 12000 Don ?
-- PASS:0842 E6 13 Ed Akron NULL NULL ?
-- similar to above, except for explicit names of columns
--O SELECT staff3.EMPNUM, staff3.GRADE, HU.STAFF3.EMPNAME, CITY,
SELECT HU.staff3.EMPNUM, HU.staff3.GRADE, HU.STAFF3.EMPNAME, CITY,
SALARY, STAFF66.EMPNAME FROM
--O HU.STAFF3 LEFT JOIN STAFF66 USING (GRADE, EMPNUM)
--O WHERE EMPNUM = 'E3';
HU.STAFF3, STAFF66 where HU.staff3.GRADE = staff66.grade and HU.staff3.EMPNUM = staff66.empnum
and HU.staff3.EMPNUM = 'E3';
EM&|GRADE|EMPNAME |CITY |SALARY |EMPNAME
-------------------------------------------------------------------------------
E3 |13 |Carmen |Vienna |13000 |Carmen
ij> -- PASS:0842 If 1 row selected with ordered column values?
-- PASS:0842 E3 13 Carmen Vienna 13000 Carmen ?
-- REF: 7.5 GR 1 d ii
-- this is a cartesian product
--O SELECT COUNT (*) FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ;
SELECT count (*) FROM STAFF66 , HU.PROJ;
1
-----------
18
ij> -- PASS:0842 If count = 18?
ROLLBACK WORK;
ij> --O DROP TABLE STAFF66 CASCADE;
DROP TABLE STAFF66 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0842 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
