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
|
ij> -- MODULE DML022
-- SQL Test Suite, V6.0, Interactive SQL, dml022.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:0096 Subquery with MAX in < comparison predicate!
SELECT EMPNUM
FROM STAFF
WHERE GRADE <
(SELECT MAX(GRADE)
FROM STAFF);
EM&
---
E1
E2
E4
ij> -- PASS:0096 If 3 rows selected with EMPNUMs:'E1', 'E2', 'E4'?
-- END TEST >>> 0096 <<< END TEST
-- **********************************************************
-- TEST:0097 Subquery with AVG - 1 in <= comparison predicate!
SELECT *
FROM STAFF
WHERE GRADE <=
(SELECT AVG(GRADE)-1
FROM STAFF);
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
E2 |Betty |10 |Vienna
ij> -- PASS:0097 If EMPNUM = 'E2' and EMPNAME = 'Betty'?
-- END TEST >>> 0097 <<< END TEST
-- *******************************************************************
-- TEST:0098 IN predicate with simple subquery!
SELECT EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM = 'P2')
ORDER BY EMPNAME;
EMPNAME
--------------------
Alice
Betty
Carmen
Don
ij> -- PASS:0098 If 4 rows selected and first EMPNAME = 'Alice'?
-- END TEST >>> 0098 <<< END TEST
-- ***************************************************************
-- TEST:0099 Nested IN predicate - 2 levels!
SELECT EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM IN
(SELECT PNUM
FROM PROJ
WHERE PTYPE = 'Design'));
EMPNAME
--------------------
Alice
Betty
Don
ij> -- PASS:0099 If 3 rows selected with EMPNAMEs:'Alice', 'Betty', 'Don'?
-- END TEST >>> 0099 <<< END TEST
-- *****************************************************************
-- TEST:0100 Nested IN predicate - 6 levels!
SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM IN
(SELECT PNUM
FROM PROJ
WHERE PTYPE IN
(SELECT PTYPE
FROM PROJ
WHERE PNUM IN
(SELECT PNUM
FROM WORKS
WHERE EMPNUM IN
(SELECT EMPNUM
FROM WORKS
WHERE PNUM IN
(SELECT PNUM
FROM PROJ
WHERE PTYPE = 'Design'))))))
ORDER BY EMPNUM;
EM&|EMPNAME
------------------------
E1 |Alice
E2 |Betty
E3 |Carmen
E4 |Don
ij> -- PASS:0100 If 4 rows selected and first EMPNUM = 'E1'?
-- PASS:0100 and first EMPNAME = 'Alice'?
-- END TEST >>> 0100 <<< END TEST
-- ****************************************************************
-- TEST:0101 Quantified predicate <= ALL with AVG in GROUP BY!
SELECT EMPNUM,PNUM
FROM WORKS
WHERE HOURS <= ALL
(SELECT AVG(HOURS)
FROM WORKS
GROUP BY PNUM);
EM&|PN&
-------
E1 |P5
E1 |P6
ij> -- PASS:0101 If 2 rows selected and each EMPNUM = 'E1'?
-- END TEST >>> 0101 <<< END TEST
-- *******************************************************************
-- TEST:0102 Nested NOT EXISTS with correlated subquery and DISTINCT!
SELECT DISTINCT EMPNUM
FROM WORKS WORKSX
WHERE NOT EXISTS
(SELECT *
FROM WORKS WORKSY
WHERE EMPNUM = 'E2'
AND NOT EXISTS
(SELECT *
FROM WORKS WORKSZ
WHERE WORKSZ.EMPNUM = WORKSX.EMPNUM
AND WORKSZ.PNUM = WORKSY.PNUM));
EM&
---
E1
E2
ij> -- PASS:0102 If 2 rows selected with EMPNUMs:'E1', 'E2'?
-- END TEST >>> 0102 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
