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
|
AUTOCOMMIT OFF;
-- MODULE DML079
-- SQL Test Suite, V6.0, Interactive SQL, dml079.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
set schema HU;
--O SELECT USER FROM HU.ECCO;
VALUES USER;
-- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0451 UNIQUEness is case sensitive!
UPDATE STAFF SET EMPNUM = 'e2'
WHERE EMPNUM = 'E4';
-- PASS:0451 If 1 row updated?
INSERT INTO STAFF(EMPNUM)
VALUES ('E1');
-- PASS:0451 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO STAFF(EMPNUM)
VALUES ('e1');
-- PASS:0451 If 1 row inserted?
UPDATE STAFF
SET EMPNUM = 'E1' WHERE EMPNUM = 'e1';
-- PASS:0451 If ERROR, unique constraint, 0 rows updated?
SELECT * FROM STAFF;
-- PASS:0451 If 6 rows are selected?
-- PASS:0451 If EMPNUMs are 'e1','e2','E1','E2','E3','E5'?
INSERT INTO WORKS (EMPNUM,PNUM)
VALUES ('e1','p2');
-- PASS:0451 If 1 row inserted?
INSERT INTO WORKS (EMPNUM,PNUM)
VALUES ('E1','p2');
-- PASS:0451 If 1 row inserted?
INSERT INTO WORKS (EMPNUM,PNUM)
VALUES ('E1','P2');
-- PASS:0451 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO WORKS (EMPNUM,PNUM)
VALUES ('e1', 'P2');
-- PASS:0451 If 1 row inserted?
UPDATE WORKS
SET EMPNUM = 'E1'
WHERE PNUM = 'P5' AND EMPNUM = 'E4';
-- PASS:0451 If ERROR, unique constraint, 0 rows updated?
UPDATE WORKS
SET EMPNUM = 'e1'
WHERE PNUM = 'P5' AND EMPNUM = 'E4';
-- PASS:0451 If 1 row updated?
UPDATE WORKS
SET PNUM = 'P4'
WHERE PNUM = 'P2' AND EMPNUM = 'E4';
-- PASS:0451 If ERROR, unique constraint, 0 rows updated?
UPDATE WORKS
SET PNUM = 'p4'
WHERE PNUM = 'P2' AND EMPNUM = 'E4';
-- PASS:0451 If 1 row updated?
SELECT * FROM WORKS
ORDER BY EMPNUM, PNUM;
-- PASS:0451 If 15 rows are selected?
-- PASS:0451 If EMPNUM/PNUM values include ?
-- PASS:0451 e1/p2, E1/p2, e1/P2, e1/P5, E4/p4 ?
-- PASS:0451 If no EMPNUM/PNUM values are duplicates ?
ROLLBACK WORK;
-- END TEST >>> 0451 <<< END TEST
-- *********************************************
-- TEST:0452 Order of precedence, left-to-right in UNION [ALL]!
SELECT EMPNAME FROM STAFF
UNION
SELECT EMPNAME FROM STAFF
UNION ALL
SELECT EMPNAME FROM STAFF;
-- PASS:0452 If 10 rows selected?
SELECT EMPNAME FROM STAFF
UNION ALL
SELECT EMPNAME FROM STAFF
UNION
SELECT EMPNAME FROM STAFF;
-- PASS:0452 If 5 rows selected?
-- END TEST >>> 0452 <<< END TEST
-- *********************************************
-- TEST:0453 NULL with empty subquery of ALL, SOME, ANY!
UPDATE PROJ
SET CITY = NULL WHERE PNAME = 'IRM';
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY IS NULL;
-- PASS:0453 If count = 1?
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY = ALL (SELECT CITY
FROM STAFF
WHERE EMPNUM = 'E8');
-- PASS:0453 If count = 6?
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY <> ALL (SELECT CITY
FROM STAFF
WHERE EMPNUM = 'E8');
-- PASS:0453 If count = 6?
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY = ANY (SELECT CITY
FROM STAFF
WHERE EMPNUM = 'E8');
-- PASS:0453 If count = 0?
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY <> ANY (SELECT CITY
FROM STAFF
WHERE EMPNUM = 'E8');
-- PASS:0453 If count = 0?
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY = SOME (SELECT CITY
FROM STAFF
WHERE EMPNUM = 'E8');
-- PASS:0453 If count = 0?
--O SELECT COUNT(*)
SELECT CITY
FROM PROJ
WHERE CITY <> SOME (SELECT CITY
FROM STAFF
WHERE EMPNUM = 'E8');
-- PASS:0453 If count = 0?
ROLLBACK WORK;
-- END TEST >>> 0453 <<< END TEST
-- *************************************************////END-OF-MODULE
|
