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
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML073
-- SQL Test Suite, V6.0, Interactive SQL, dml073.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:0393 SUM, MAX on Cartesian product!
SELECT SUM(HOURS), MAX(HOURS)
FROM STAFF, WORKS;
1 |2
------------------
2320 |80
ij> -- PASS:0393 If SUM(HOURS) = 2320 and MAX(HOURS) = 80?
-- END TEST >>> 0393 <<< END TEST
-- *************************************************************
-- TEST:0394 AVG, MIN on joined table with WHERE without GROUP!
SELECT AVG(HOURS), MIN(HOURS)
FROM STAFF, WORKS
WHERE STAFF.EMPNUM = 'E2'
AND STAFF.EMPNUM = WORKS.EMPNUM;
1 |2
--------------------
60.0000 |40
ij> -- PASS:0394 If AVG(HOURS) = 60 and MIN(HOURS) = 40?
-- END TEST >>> 0394 <<< END TEST
-- *************************************************************
-- TEST:0395 SUM, MIN on joined table with GROUP without WHERE!
SELECT STAFF.EMPNUM, SUM(HOURS), MIN(HOURS)
FROM STAFF, WORKS
GROUP BY STAFF.EMPNUM
ORDER BY 1;
EM&|2 |3
----------------------
E1 |464 |12
E2 |464 |12
E3 |464 |12
E4 |464 |12
E5 |464 |12
ij> -- PASS:0395 If 5 rows are selected with the following order?
-- PASS:0395 STAFF.EMPNUM SUM(HOURS) MIN(HOURS)?
-- PASS:0395 'E1' 464 12?
-- PASS:0395 'E2' 464 12?
-- PASS:0395 'E3' 464 12?
-- PASS:0395 'E4' 464 12?
-- PASS:0395 'E5' 464 12?
-- END TEST >>> 0395 <<< END TEST
-- *************************************************************
-- TEST:0396 SUM, MIN on joined table with WHERE, GROUP BY, HAVING!
SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS)
FROM STAFF, WORKS
WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND
STAFF.EMPNUM = WORKS.EMPNUM
GROUP BY STAFF.EMPNUM
HAVING COUNT(*) > 1
--0 ORDER BY STAFF.EMPNUM;
ORDER BY EMPNUM;
EM&|2 |3
------------------------
E1 |30.6666 |12
E4 |46.6666 |20
ij> -- PASS:0396 If 2 rows are selected with the following order?
-- PASS:0396 STAFF.EMPNUM AVG(HOURS) MIN(HOURS)?
-- PASS:0396 'E1' 30 to 31 12?
-- PASS:0396 'E4' 46 to 47 20?
-- END TEST >>> 0396 <<< END TEST
-- *************************************************************
-- TEST:0417 Cartesian product GROUP BY 2 columns with NULLs!
DELETE FROM STAFF1;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> -- Making sure the table is empty
-- setup
INSERT INTO STAFF VALUES ('E6', 'David', 17, NULL);
1 row inserted/updated/deleted
ij> INSERT INTO STAFF VALUES ('E7', 'Tony', 18, NULL);
1 row inserted/updated/deleted
ij> INSERT INTO STAFF1 SELECT * FROM STAFF;
7 rows inserted/updated/deleted
ij> SELECT MAX(STAFF1.GRADE), SUM(STAFF1.GRADE)
FROM STAFF1, STAFF
GROUP BY STAFF1.CITY, STAFF.CITY;
1 |2
---------------
13 |13
13 |26
13 |26
13 |26
12 |24
12 |48
12 |48
12 |48
13 |23
13 |46
13 |46
13 |46
18 |35
18 |70
18 |70
18 |70
ij> -- PASS:0417 If 16 rows are selected in any order?
-- PASS:0417 Including the following four rows?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 35?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 70?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 70?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 70?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0417 <<< END TEST
-- *************************************************************
-- TEST:0418 AVG, SUM, COUNT on Cartesian product with NULL!
SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4),
SUM(T2.COL4), COUNT(DISTINCT T1.COL4)
FROM VTABLE T1, VTABLE T2;
1 |2 |3 |4
-----------------------------------------------
147 |295 |1772 |3
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0418 If AVG(T1.COL4) = 147 or 148?
-- PASS:0418 If AVG(T1.COL4 + T2.COL4) = 295 or 296?
-- PASS:0418 If SUM(T2.COL4) = 1772?
-- PASS:0418 If COUNT(DISTINCT T1.COL4) = 3?
-- END TEST >>> 0418 <<< END TEST
-- *************************************************************
-- TEST:0419 SUM, MAX, MIN on joined table view!
SELECT SUM(COST), MAX(COST), MIN(COST)
FROM STAFF_WORKS_DESIGN;
1 |2 |3
--------------------------------------------------------------------------
3488 |960 |288
ij> -- PASS:0419 If SUM(COST) = 3488, MAX(COST) = 960, MIN(COST) = 288?
-- END TEST >>> 0419 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
