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
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
AUTOCOMMIT OFF;
ij> -- MODULE CDR027
-- SQL Test Suite, V6.0, Interactive SQL, cdr027.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SUN
set schema SUN;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM SUN.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
SUN
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0446 Table CHECK constraint allows unknown (NULL)!
DELETE FROM STAFF5;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF5 VALUES('E7','Mimi',NULL,'Miami');
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row inserted?
INSERT INTO STAFF5 VALUES('E8','Joe',NULL,'Boston');
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row inserted?
INSERT INTO STAFF5(EMPNUM) VALUES('E9');
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row inserted?
--O UPDATE STAFF
UPDATE HU.STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1';
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row updated?
INSERT INTO STAFF5
SELECT *
--O FROM STAFF;
FROM HU.STAFF;
5 rows inserted/updated/deleted
ij> -- PASS:0446 If 5 rows inserted?
UPDATE STAFF5
SET GRADE = NULL
WHERE EMPNUM = 'E2';
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row updated?
UPDATE STAFF5
SET GRADE = NULL
WHERE EMPNUM = 'E4';
1 row inserted/updated/deleted
ij> -- PASS:0446 If 1 row updated?
SELECT COUNT(*)
FROM STAFF5;
1
-----------
8
ij> -- PASS:0446 If count = 8?
SELECT COUNT(*)
FROM STAFF5
WHERE GRADE IS NULL;
1
-----------
6
ij> -- PASS:0446 If count = 6?
ROLLBACK WORK;
ij> -- END TEST >>> 0446 <<< END TEST
-- *********************************************
-- TEST:0447 NULLs with check constraint and check option!
DELETE FROM STAFF6;
1 row inserted/updated/deleted
ij> --O INSERT INTO STAFF6_WITH_GRADES
--O VALUES('X1','Vicki',NULL,'Houston');
-- PASS:0447 If ERROR, view check constraint, 0 rows inserted?
INSERT INTO STAFF6
VALUES('X2','Tina',NULL,'Orlando');
1 row inserted/updated/deleted
ij> -- PASS:0447 If 1 row inserted?
--O SELECT COUNT(*)
--O FROM STAFF6_WITH_GRADES;
-- PASS:0447 If count = 0?
SELECT COUNT(*)
FROM STAFF6;
1
-----------
1
ij> -- PASS:0447 If count = 1?
SELECT EMPNAME
FROM STAFF6
WHERE GRADE IS NULL;
EMPNAME
--------------------
Tina
ij> -- PASS:0447 If EMPNAME is Tina?
ROLLBACK WORK;
ij> -- END TEST >>> 0447 <<< END TEST
-- *********************************************
-- TEST:0448 PRIMARY KEY implies UNIQUE!
DELETE FROM STAFF9;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('D1','Muddley');
1 row inserted/updated/deleted
ij> -- PASS:0448 If 1 row inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('D1','Muddley');
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 'STAFF9_PK' defined on 'STAFF9'.
ij> -- PASS:0448 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('d1','Muddley');
1 row inserted/updated/deleted
ij> -- PASS:0448 If 1 row inserted?
SELECT COUNT(*)
FROM STAFF9;
1
-----------
2
ij> -- PASS:0448 If count = 2?
ROLLBACK WORK;
ij> -- END TEST >>> 0448 <<< END TEST
-- *********************************************
-- TEST:0449 Constraint definition is case sensitive!
DELETE FROM STAFF9;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('Z1','Tina');
ERROR 23513: The check constraint 'STAFF9_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF9"'.
ij> -- PASS:0449 If ERROR, check constraint, 0 rows inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('Z2','tina');
1 row inserted/updated/deleted
ij> -- PASS:0449 If 1 row inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('Z3','ANTHONY');
1 row inserted/updated/deleted
ij> -- PASS:0449 If 1 row inserted?
SELECT COUNT(*)
FROM STAFF9;
1
-----------
2
ij> -- PASS:0449 If count = 2?
ROLLBACK WORK;
ij> -- END TEST >>> 0449 <<< END TEST
-- *********************************************
-- TEST:0450 Referential integrity is case sensitive!
INSERT INTO DEPT
VALUES(11,'VOLLEYBALL','VICKI');
1 row inserted/updated/deleted
ij> -- PASS:0450 If 1 row inserted?
INSERT INTO DEPT
VALUES(10,'volleyball','vicki');
1 row inserted/updated/deleted
ij> -- PASS:0450 If 1 row inserted?
-- NOTE:0450 insert lower case value of above.
SELECT COUNT(*) FROM DEPT WHERE DNO = 10;
1
-----------
1
ij> -- PASS:0450 If count = 1?
--O INSERT INTO EMP
--O VALUES(13,'MARY','Dancer',15,'VOLLEYBALL',010101);
-- PASS:0450 If 1 row inserted?
DELETE FROM DEPT
WHERE DNO = 10;
1 row inserted/updated/deleted
ij> -- PASS:0450 If 1 row deleted?
--O UPDATE DEPT
--O SET DNAME = 'EDUCATION'
--O WHERE DNAME = 'Education';
-- PASS:0450 If RI ERROR, children exist, 0 rows updated?
--O SELECT * FROM DEPT ORDER BY DNO;
-- PASS:0450 If 5 rows selected?
-- PASS:0450 If DNO values are 11, 12, 13, 14, 15?
-- PASS:0450 If DNAME = 'Education' (not 'EDUCATION') for DNO = 14?
--O INSERT INTO EMP
--O VALUES(28,'BARBARA','Jogger',14,'EDUCATION',010101);
-- PASS:0450 If RI ERROR, parent missing, 0 rows inserted?
--O UPDATE EMP
--O SET DNAME = 'PHYSICS'
--O WHERE ENO = 25;
-- PASS:0450 If RI ERROR, parent missing, 0 rows updated?
--O SELECT ENO, ENAME, DNO, DNAME
--O FROM EMP ORDER BY ENO;
-- PASS:0450 If 8 rows selected?
-- PASS:0450 If ENO values are 13, 21 through 27?
-- PASS:0450 If DNAME = 'Physics' (not 'PHYSICS') for ENO = 25?
ROLLBACK WORK;
ij> -- END TEST >>> 0450 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
