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
|
AUTOCOMMIT OFF;
-- MODULE DML114
-- SQL Test Suite, V6.0, Interactive SQL, dml114.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
set schema FLATER;
--0 SELECT USER FROM HU.ECCO;
VALUES USER;
-- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
-- date_time print
-- TEST:0635 Feature 13, grouped operations (static)!
CREATE VIEW WORKWEEK AS
SELECT EMPNUM, HOURS FROM HU.WORKS
GROUP BY HOURS, EMPNUM;
-- PASS:0635 If table is created?
COMMIT WORK;
SELECT EMPNUM, SUM (HOURS)
FROM WORKWEEK
WHERE HOURS > 20
GROUP BY EMPNUM
HAVING EMPNUM = 'E1';
-- PASS:0635 If 1 row selected and EMPNUM = 'E1' and SUM(HOURS) = 120?
SELECT COUNT(*)
FROM WORKWEEK WHERE HOURS > 40;
-- PASS:0635 If count = 3?
SELECT EMPNAME
FROM HU.STAFF, WORKWEEK
WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM
AND HOURS = 12;
-- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM), AVG(HOURS)
FROM WORKWEEK;
-- 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);
-- 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);
-- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
-- NOTE:0635 Cursor subtest deleted.
COMMIT WORK;
--0 DROP VIEW WORKWEEK CASCADE;
DROP VIEW WORKWEEK ;
COMMIT WORK;
-- 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;
-- PASS:0637 If view is created?
COMMIT WORK;
CREATE VIEW CORRQUALSTAR AS
SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS
WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM;
-- PASS:0637 If view is created?
COMMIT WORK;
CREATE VIEW SUBQ2 AS
SELECT DISTINCT * FROM QUALSTAR;
-- PASS:0637 If view is created?
COMMIT WORK;
CREATE VIEW CORRSUBQ2 AS
SELECT DISTINCT * FROM CORRQUALSTAR;
-- PASS:0637 If view is created?
COMMIT WORK;
SELECT COUNT(*) FROM QUALSTAR;
-- PASS:0637 If count = 12?
SELECT COUNT(*) FROM SUBQ2;
-- PASS:0637 If count = 10?
SELECT EMPNUM, GRADE, CITY, HOURS
FROM QUALSTAR WHERE EMPNAME = 'Carmen';
-- 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';
-- 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;
-- PASS:0637 If count = 12?
SELECT COUNT(*) FROM CORRSUBQ2;
-- PASS:0637 If count = 10?
SELECT EMPNUM, GRADE, CITY, HOURS
FROM CORRQUALSTAR WHERE EMPNAME = 'Carmen';
-- PASS:0637 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
COMMIT WORK;
--0 DROP VIEW QUALSTAR CASCADE;
DROP VIEW SUBQ2 ;
DROP VIEW QUALSTAR ;
COMMIT WORK;
--0 DROP VIEW CORRQUALSTAR CASCADE;
DROP VIEW CORRSUBQ2 ;
DROP VIEW CORRQUALSTAR ;
COMMIT WORK;
-- 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;
-- PASS:0639 If view is created?
commit work;
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';
-- PASS:0639 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0639 AND GRADE = 13 and CITY = 'Vienna'?
-- NOTE:0639 Cursor subtest deleted.
COMMIT WORK;
--0 DROP VIEW STAFF CASCADE;
DROP VIEW STAFF ;
COMMIT WORK;
-- 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);
-- PASS:0641 If view is created?
COMMIT WORK;
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));
-- PASS:0641 If view is created?
COMMIT WORK;
INSERT INTO FEAT16 VALUES (1, 10);
-- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (2, 20);
-- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (1, 30);
-- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO FEAT16 VALUES (3, 20);
-- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO FEAT16 VALUES (3, NULL);
-- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (4, NULL);
-- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (5, NULL);
-- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (1, 'A', 10, 'a');
-- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (2, 'A', 20, 'a');
-- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (1, 'A', 30, 'a');
-- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO BARNO VALUES (3, 'A', 20, 'a');
-- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO BARNO VALUES (3, NULL, 30, 'a');
-- 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');
-- 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);
-- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (3, 'B', 30, NULL);
-- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (4, 'B', NULL, NULL);
-- PASS:0641 If 1 row is inserted?
COMMIT WORK;
--0 DROP TABLE FEAT16 CASCADE;
DROP TABLE FEAT16 ;
COMMIT WORK;
--0 DROP TABLE BARNO CASCADE;
DROP TABLE BARNO ;
COMMIT WORK;
-- END TEST >>> 0641 <<< END TEST
-- *************************************************////END-OF-MODULE
|
