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
|
ij> -- MODULE DML019
-- SQL Test Suite, V6.0, Interactive SQL, dml019.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0074 GROUP BY col with SELECT col., SUM!
SELECT PNUM, SUM(HOURS)
FROM WORKS
GROUP BY PNUM;
PN&|2
---------------
P1 |80
P2 |140
P3 |80
P4 |60
P5 |92
P6 |12
ij> -- PASS:0074 If 6 rows are selected?
-- PASS:0074 If PNUMs: 'P1', 'P2', 'P3', 'P4', 'P5', 'P6'?
-- PASS:0074 If SUM(HOURS) for 'P2' is 140 ?
-- END TEST >>> 0074 <<< END TEST
-- **********************************************************
-- TEST:0075 GROUP BY clause!
SELECT EMPNUM
FROM WORKS
GROUP BY EMPNUM
-- Derby change to standardize order for diff
order by empnum;
EM&
---
E1
E2
E3
E4
ij> -- PASS:0075 If 4 rows are selected with EMPNUMs: 'E1','E2','E3','E4'?
-- END TEST >>> 0075 <<< END TEST
-- ************************************************************
-- TEST:0076 GROUP BY 2 columns!
SELECT EMPNUM,HOURS
FROM WORKS
GROUP BY EMPNUM,HOURS
-- Derby change to standardize order for diff
order by empnum, hours;
EM&|HOURS
----------
E1 |12
E1 |20
E1 |40
E1 |80
E2 |40
E2 |80
E3 |20
E4 |20
E4 |40
E4 |80
ij> -- PASS:0076 If 10 rows are selected and EMPNUM = 'E1' in 4 rows ?
-- PASS:0076 for 1 row EMPNUM = 'E1' and HOURS = 12?
-- END TEST >>> 0076 <<< END TEST
-- ***********************************************************
-- TEST:0077 GROUP BY all columns with SELECT * !
SELECT *
FROM WORKS
GROUP BY PNUM,EMPNUM,HOURS;
EM&|PN&|HOURS
--------------
E1 |P1 |40
E1 |P2 |20
E1 |P3 |80
E1 |P4 |20
E1 |P5 |12
E1 |P6 |12
E2 |P1 |40
E2 |P2 |80
E3 |P2 |20
E4 |P2 |20
E4 |P4 |40
E4 |P5 |80
ij> -- PASS:0077 If 12 rows are selected ?
-- END TEST >>> 0077 <<< END TEST
-- ***********************************************************
-- TEST:0078 GROUP BY three columns, SELECT two!
SELECT PNUM,EMPNUM
FROM WORKS
GROUP BY EMPNUM,PNUM,HOURS;
PN&|EM&
-------
P1 |E1
P2 |E1
P3 |E1
P4 |E1
P5 |E1
P6 |E1
P1 |E2
P2 |E2
P2 |E3
P2 |E4
P4 |E4
P5 |E4
ij> -- PASS:0078 If 12 rows are selected ?
-- END TEST >>> 0078 <<< END TEST
-- *********************************************************
-- TEST:0079 GROUP BY NULL value!
-- setup
INSERT INTO STAFF(EMPNUM,EMPNAME,GRADE)
VALUES('E6','WANG',40);
1 row inserted/updated/deleted
ij> -- PASS:0079 If 1 row is inserted?
INSERT INTO STAFF(EMPNUM,EMPNAME,GRADE)
VALUES('E7','SONG',50);
1 row inserted/updated/deleted
ij> -- PASS:0079 If 1 row is inserted?
SELECT SUM(GRADE)
FROM STAFF
WHERE CITY IS NULL
GROUP BY CITY;
1
---------
90
ij> -- PASS:0079 If SUM(GRADE) = 90?
-- restore
DELETE FROM STAFF WHERE CITY IS NULL;
2 rows inserted/updated/deleted
ij> -- PASS:0079 If 2 rows deleted?
--O SELECT COUNT(*) FROM STAFF;
SELECT * FROM STAFF;
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
E1 |Alice |12 |Deale
E2 |Betty |10 |Vienna
E3 |Carmen |13 |Vienna
E4 |Don |12 |Deale
E5 |Ed |13 |Akron
ij> -- PASS:0079 If count = 5?
-- END TEST >>> 0079 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
