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
|
AUTOCOMMIT OFF;
-- 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;
-- 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));
-- PASS:0840 If table is created?
ROLLBACK WORK;
INSERT INTO NOT_THERE VALUES ('1234567890');
-- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
ROLLBACK WORK;
CREATE VIEW NOT_HERE AS
SELECT * FROM USIG;
-- PASS:0840 If view is created?
ROLLBACK WORK;
SELECT COUNT (*) FROM NOT_HERE;
-- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
ROLLBACK WORK;
ALTER TABLE USIG
ADD COLUMN NUL INT;
-- PASS:0840 If column is added?
ROLLBACK WORK;
SELECT COUNT (*)
FROM USIG WHERE NUL IS NULL;
-- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
ROLLBACK WORK;
--O DROP TABLE USIG CASCADE;
DROP TABLE USIG ;
-- PASS:0840 If table is dropped?
ROLLBACK WORK;
SELECT COUNT(*)
FROM U_SIG;
-- PASS:0840 If count = 2?
ROLLBACK WORK;
SELECT COUNT(*)
FROM USIG;
-- PASS:0840 If count = 2?
ROLLBACK WORK;
-- END TEST >>> 0840 <<< END TEST
-- *********************************************
-- TEST:0841 Multiple-join and default order of joins !
-- setup
DELETE FROM HU.STAFF4;
INSERT INTO HU.STAFF4
SELECT * FROM HU.STAFF3
WHERE EMPNUM > 'E3';
--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;
-- 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;
-- END TEST >>> 0841 <<< END TEST
-- *********************************************
-- TEST:0842 Multi-column joins !
-- setup
CREATE TABLE STAFF66 (
SALARY INTEGER,
EMPNAME CHAR(20),
GRADE DECIMAL,
EMPNUM CHAR(3));
COMMIT WORK;
-- setup
INSERT INTO STAFF66
SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM
FROM HU.STAFF3 WHERE EMPNUM > 'E2';
-- PASS:0842 If 3 rows inserted ?
UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM = 'E5';
-- PASS:0842 If 1 row updated ?
UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE = 12;
-- 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;
-- 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';
-- 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;
-- PASS:0842 If count = 18?
ROLLBACK WORK;
--O DROP TABLE STAFF66 CASCADE;
DROP TABLE STAFF66 ;
COMMIT WORK;
-- END TEST >>> 0842 <<< END TEST
-- *************************************************////END-OF-MODULE
|
