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
|
AUTOCOMMIT OFF;
-- MODULE DML023
-- SQL Test Suite, V6.0, Interactive SQL, dml023.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:0103 Subquery with comparison predicate!
SELECT PNUM
FROM PROJ
WHERE PROJ.CITY =
(SELECT STAFF.CITY
FROM STAFF
WHERE EMPNUM = 'E1');
-- PASS:0103 If 3 rows are selected with PNUMs:'P1','P4','P6?
-- END TEST >>> 0103 <<< END TEST
-- **************************************************************
-- TEST:0104 SQLCODE < 0, subquery with more than 1 value!
SELECT PNUM
FROM PROJ
WHERE PROJ.CITY =
(SELECT STAFF.CITY
FROM STAFF
WHERE EMPNUM > 'E1' );
-- PASS:0104 If ERROR, SELECT returns more than 1 row in subquery?
-- PASS:0104 If 0 rows are selected?
-- END TEST >>> 0104 <<< END TEST
-- ************************************************************
-- TEST:0105 Subquery in comparison predicate is empty!
--O SELECT COUNT(*)
SELECT *
FROM STAFF
WHERE STAFF.CITY =
(SELECT PROJ.CITY
FROM PROJ
WHERE PNUM > 'P7');
-- PASS:0105 If count = 0?
--O SELECT COUNT(*)
SELECT *
FROM STAFF
WHERE NOT (STAFF.CITY =
(SELECT PROJ.CITY
FROM PROJ
WHERE PNUM > 'P7' ));
-- PASS:0105 If count = 0?
-- END TEST >>> 0105 <<< END TEST
-- *************************************************************
-- TEST:0106 Comparison predicate <> !
SELECT PNUM
FROM PROJ
WHERE CITY <> 'Deale';
-- PASS:0106 If 3 rows are selected with PNUMs:'P2','P3','P5'?
-- END TEST >>> 0106 <<< END TEST
-- *************************************************************
-- TEST:0107 Comp predicate with short string logically blank padded!
--O SELECT COUNT(*)
SELECT empnum
FROM WORKS
WHERE EMPNUM = 'E1';
-- PASS:0107 If count = 6 ?
--O SELECT COUNT(*)
SELECT empnum
FROM WORKS
WHERE EMPNUM = 'E1' AND EMPNUM = 'E1 ';
-- PASS:0107 If count = 6?
-- END TEST >>> 0107 <<< END TEST
-- ****************************************************************
-- TEST:0180 NULLs sort together in ORDER BY!
-- setup
UPDATE STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
-- PASS:0180 If 3 rows are updated?
SELECT EMPNUM,GRADE
FROM STAFF
ORDER BY GRADE,EMPNUM;
-- PASS:0180 If 5 rows are selected with NULLs together ?
-- PASS:0180 If first EMPNUM is either 'E1' or 'E2'?
-- PASS:0180 If last EMPNUM is either 'E4' or 'E5?
-- restore
ROLLBACK WORK;
-- END TEST >>> 0180 <<< END TEST
-- ***************************************************************
-- TEST:0181 NULLs are equal for DISTINCT!
-- setup
UPDATE STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
-- PASS:0181 If 3 rows are updated?
SELECT DISTINCT USER, GRADE
FROM STAFF
ORDER BY GRADE;
-- PASS:0181 If 3 rows are selected with GRADEs:10, 12, NULL ?
-- PASS:0181 GRADE 10 precedes GRADE 12?
-- restore
ROLLBACK WORK;
-- END TEST >>> 0181 <<< END TEST
-- *************************************************////END-OF-MODULE
|
