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
|
AUTOCOMMIT OFF;
-- MODULE DML182
-- SQL Test Suite, V6.0, Interactive SQL, dml182.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
set schema FLATER;
--O SELECT USER FROM HU.ECCO;
VALUES USER;
-- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
-- date_time print
-- TEST:0895 FIPS sizing, columns in list >= 15!
CREATE TABLE ID_CODES (
CODE1 INT NOT NULL,
CODE2 INT NOT NULL,
CODE3 INT NOT NULL,
CODE4 INT NOT NULL,
CODE5 INT NOT NULL,
CODE6 INT NOT NULL,
CODE7 INT NOT NULL,
CODE8 INT NOT NULL,
CODE9 INT NOT NULL,
CODE10 INT NOT NULL,
CODE11 INT NOT NULL,
CODE12 INT NOT NULL,
CODE13 INT NOT NULL,
CODE14 INT NOT NULL,
CODE15 INT NOT NULL,
PRIMARY KEY (CODE1, CODE2, CODE3, CODE4, CODE5,
CODE6, CODE7, CODE8, CODE9, CODE10,
CODE11, CODE12, CODE13, CODE14, CODE15));
-- PASS:0895 If table created successfully?
COMMIT WORK;
CREATE TABLE ORDERS (
CODE1 INT,
CODE2 INT,
CODE3 INT,
CODE4 INT,
CODE5 INT,
CODE6 INT,
CODE7 INT,
CODE8 INT,
CODE9 INT,
CODE10 INT,
CODE11 INT,
CODE12 INT,
CODE13 INT,
CODE14 INT,
CODE15 INT,
TITLE VARCHAR (80),
COST NUMERIC(5,2),
FOREIGN KEY (CODE1, CODE2, CODE3, CODE4, CODE5,
CODE6, CODE7, CODE8, CODE9, CODE10,
CODE11, CODE12, CODE13, CODE14, CODE15)
REFERENCES ID_CODES);
-- PASS:0895 If table created successfully?
COMMIT WORK;
CREATE VIEW ID_ORDERS (CODE1, CODE2, CODE3, CODE4,
CODE5, CODE6, CODE7, CODE8, CODE9, CODE10,
CODE11, CODE12, CODE13, CODE14, CODE15, title, cost) AS
--O SELECT * FROM ID_CODES JOIN ORDERS
SELECT
a.CODE1, a.CODE2, a.CODE3, a.CODE4, a.CODE5,
a.CODE6, a.CODE7, a.CODE8, a.CODE9, a.CODE10,
a.CODE11, a.CODE12, a.CODE13, a.CODE14, a.CODE15, title, cost
FROM ID_CODES a JOIN ORDERS
ON (a.CODE1=ORDERS.CODE1 and a.CODE2=ORDERS.CODE2 and a.CODE3=ORDERS.CODE3 and a.CODE4=ORDERS.CODE4 and a.CODE5=ORDERS.CODE5 and
a.CODE6=ORDERS.CODE6 and a.CODE7=ORDERS.CODE7 and a.CODE8=ORDERS.CODE8 and a.CODE9=ORDERS.CODE9 and a.CODE10=ORDERS.CODE10 and
a.CODE11=ORDERS.CODE11 and a.CODE12=ORDERS.CODE12 and a.CODE13=ORDERS.CODE13 and a.CODE14=ORDERS.CODE14 and a.CODE15=ORDERS.CODE15);
-- PASS:0895 If view created successfully
COMMIT WORK;
INSERT INTO ID_CODES VALUES (
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
INSERT INTO ID_CODES VALUES (
1, 2, 3, 4, 5, 6, 7, 9, 8, 10, 11, 12, 13, 14, 15);
INSERT INTO ORDERS VALUES (
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
'Gidget Goes Skiing',
29.95);
INSERT INTO ORDERS VALUES (
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
'Barney Goes Hawaiian',
19.95);
INSERT INTO ORDERS VALUES (
1, 2, 3, 4, 5, 6, 7, 9, 8, 10, 11, 12, 13, 14, 15,
'Invasion of the Smurfs',
9.95);
-- PASS:0895 If 5 rows inserted successfully in previous 5 inserts?
SELECT CODE1, CODE2, CODE3, CODE4, CODE5,
CODE6, CODE7, CODE8, CODE9, CODE10,
CODE11, CODE12, CODE13, CODE14, CODE15,
AVG(COST)
FROM ID_ORDERS
GROUP BY CODE1, CODE2, CODE3, CODE4, CODE5,
CODE6, CODE7, CODE8, CODE9, CODE10,
CODE11, CODE12, CODE13, CODE14, CODE15
ORDER BY CODE1, CODE2, CODE3, CODE4, CODE5,
CODE6, CODE7, CODE8, CODE9, CODE10,
CODE11, CODE12, CODE13, CODE14, CODE15;
-- PASS:0895 If 2 rows are returned?
-- avg(cost)
-- =========
-- PASS:0895 If 24.95 (+ or - 0.01) ?
-- PASS:0895 If 9.95 (+ or - 0.01) ?
COMMIT WORK;
--O DROP TABLE ORDERS CASCADE;
drop view id_orders;
DROP TABLE ORDERS ;
-- PASS:0895 If table dropped successfully?
COMMIT WORK;
--O DROP TABLE ID_CODES CASCADE;
DROP TABLE ID_CODES ;
-- PASS:0895 If table dropped successfully?
COMMIT WORK;
-- END TEST >>> 0895 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
|
