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
|
--
-- 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;
-- MODULE CDR027
-- SQL Test Suite, V6.0, Interactive SQL, cdr027.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SUN
set schema SUN;
--O SELECT USER FROM SUN.ECCO;
VALUES USER;
-- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0446 Table CHECK constraint allows unknown (NULL)!
DELETE FROM STAFF5;
INSERT INTO STAFF5 VALUES('E7','Mimi',NULL,'Miami');
-- PASS:0446 If 1 row inserted?
INSERT INTO STAFF5 VALUES('E8','Joe',NULL,'Boston');
-- PASS:0446 If 1 row inserted?
INSERT INTO STAFF5(EMPNUM) VALUES('E9');
-- PASS:0446 If 1 row inserted?
--O UPDATE STAFF
UPDATE HU.STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1';
-- PASS:0446 If 1 row updated?
INSERT INTO STAFF5
SELECT *
--O FROM STAFF;
FROM HU.STAFF;
-- PASS:0446 If 5 rows inserted?
UPDATE STAFF5
SET GRADE = NULL
WHERE EMPNUM = 'E2';
-- PASS:0446 If 1 row updated?
UPDATE STAFF5
SET GRADE = NULL
WHERE EMPNUM = 'E4';
-- PASS:0446 If 1 row updated?
SELECT COUNT(*)
FROM STAFF5;
-- PASS:0446 If count = 8?
SELECT COUNT(*)
FROM STAFF5
WHERE GRADE IS NULL;
-- PASS:0446 If count = 6?
ROLLBACK WORK;
-- END TEST >>> 0446 <<< END TEST
-- *********************************************
-- TEST:0447 NULLs with check constraint and check option!
DELETE FROM STAFF6;
--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');
-- PASS:0447 If 1 row inserted?
--O SELECT COUNT(*)
--O FROM STAFF6_WITH_GRADES;
-- PASS:0447 If count = 0?
SELECT COUNT(*)
FROM STAFF6;
-- PASS:0447 If count = 1?
SELECT EMPNAME
FROM STAFF6
WHERE GRADE IS NULL;
-- PASS:0447 If EMPNAME is Tina?
ROLLBACK WORK;
-- END TEST >>> 0447 <<< END TEST
-- *********************************************
-- TEST:0448 PRIMARY KEY implies UNIQUE!
DELETE FROM STAFF9;
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('D1','Muddley');
-- PASS:0448 If 1 row inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('D1','Muddley');
-- PASS:0448 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('d1','Muddley');
-- PASS:0448 If 1 row inserted?
SELECT COUNT(*)
FROM STAFF9;
-- PASS:0448 If count = 2?
ROLLBACK WORK;
-- END TEST >>> 0448 <<< END TEST
-- *********************************************
-- TEST:0449 Constraint definition is case sensitive!
DELETE FROM STAFF9;
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('Z1','Tina');
-- PASS:0449 If ERROR, check constraint, 0 rows inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('Z2','tina');
-- PASS:0449 If 1 row inserted?
INSERT INTO STAFF9(EMPNUM,EMPNAME)
VALUES('Z3','ANTHONY');
-- PASS:0449 If 1 row inserted?
SELECT COUNT(*)
FROM STAFF9;
-- PASS:0449 If count = 2?
ROLLBACK WORK;
-- END TEST >>> 0449 <<< END TEST
-- *********************************************
-- TEST:0450 Referential integrity is case sensitive!
INSERT INTO DEPT
VALUES(11,'VOLLEYBALL','VICKI');
-- PASS:0450 If 1 row inserted?
INSERT INTO DEPT
VALUES(10,'volleyball','vicki');
-- PASS:0450 If 1 row inserted?
-- NOTE:0450 insert lower case value of above.
SELECT COUNT(*) FROM DEPT WHERE DNO = 10;
-- 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;
-- 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;
-- END TEST >>> 0450 <<< END TEST
-- *************************************************////END-OF-MODULE
|
