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
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML090
-- SQL Test Suite, V6.0, Interactive SQL, dml090.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
ij> --0 SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0512 <value expression> for IN predicate!
SELECT MIN(PNAME)
FROM PROJ, WORKS, STAFF
WHERE PROJ.PNUM = WORKS.PNUM
AND WORKS.EMPNUM = STAFF.EMPNUM
AND BUDGET - GRADE * HOURS * 100 IN
(-4400, -1000, 4000);
1
--------------------
CALM
ij> -- PASS:0512 If PNAME = 'CALM'?
SELECT CITY, COUNT(*)
FROM PROJ
GROUP BY CITY
HAVING (MAX(BUDGET) - MIN(BUDGET)) / 2
IN (2, 20000, 10000)
ORDER BY CITY DESC;
CITY |2
---------------------------
Vienna |2
Deale |3
ij> -- PASS:0512 If in first row: CITY = 'Vienna' AND count = 2?
-- PASS:0512 AND in second row: CITY = 'Deale' AND count = 3?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0512 <<< END TEST
-- *********************************************;
-- TEST:0513 NUMERIC(4) implies CHECK BETWEEN -9999 AND 9999!
-- setup
--0 DELETE FROM TEMP_OBSERV;
--0 INSERT INTO TEMP_OBSERV (YEAR_OBSERV)
--0 VALUES (9999);
-- PASS:0513 If 1 row is inserted?
--0 INSERT INTO TEMP_OBSERV (YEAR_OBSERV)
--0 VALUES (10000);
-- PASS:0513 If ERROR, constraint violation, 0 rows inserted?
--0 UPDATE TEMP_OBSERV
--0 SET YEAR_OBSERV = -10000
--0 WHERE YEAR_OBSERV = 9999;
-- PASS:0513 If ERROR, constraint violation, 0 rows updated?
--0 INSERT INTO TEMP_OBSERV (YEAR_OBSERV, MAX_TEMP)
--0 VALUES (-9999, 123.4517);
-- PASS:0513 If 1 row is inserted?
--0 SELECT COUNT(*) FROM TEMP_OBSERV
--0 WHERE MAX_TEMP = 123.45
--0 AND MAX_TEMP NOT BETWEEN 123.4516 AND 123.4518;
-- PASS:0513 If count = 1?
--0 INSERT INTO TEMP_OBSERV (YEAR_OBSERV, MAX_TEMP)
--0 VALUES (-9999, 1234.51);
-- PASS:0513 If ERROR, constraint violation, 0 rows inserted?
-- restore
--0 ROLLBACK WORK;
-- END TEST >>> 0513 <<< END TEST
-- *********************************************;
-- TEST:0523 <value expression> for BETWEEN predicate!
SELECT COUNT(*)
FROM PROJ
WHERE 24 * 1000 BETWEEN BUDGET - 5000 AND 50000 / 1.7;
1
-----------
3
ij> -- PASS:0523 If count = 3?
SELECT PNAME
FROM PROJ
WHERE 'Tampa' NOT BETWEEN CITY AND 'Vienna'
AND PNUM > 'P2';
PNAME
--------------------
IRM
ij> -- PASS:0523 If PNAME = 'IRM'?
SELECT CITY, COUNT(*)
FROM PROJ
GROUP BY CITY
HAVING 50000 + 2 BETWEEN 33000 AND SUM(BUDGET) - 20;
CITY |2
---------------------------
Deale |3
ij> -- PASS:0523 If CITY = 'Deale' and count = 3?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0523 <<< END TEST
-- *********************************************;
-- TEST:0564 Outer ref. directly contained in HAVING clause!
SELECT EMPNUM, GRADE*1000
--0 FROM HU.STAFF WHERE GRADE * 1000 > ANY
--0 (SELECT SUM(BUDGET) FROM HU.PROJ
FROM STAFF WHERE GRADE * 1000 > ANY
(SELECT SUM(BUDGET) FROM PROJ
GROUP BY CITY, PTYPE
--0 HAVING HU.PROJ.CITY = HU.STAFF.CITY);
HAVING PROJ.CITY = STAFF.CITY);
EM&|2
-------------------
E3 |13000
ij> -- PASS:0564 If EMPNUM = E3 and GRADE * 1000 = 13000?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0564 <<< END TEST
-- *************************************************////END-OF-MODULE;
;
ij>
|
