| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML181 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml181.sql |
| -- 59-byte ID |
| -- TEd Version # |
| |
| -- AUTHORIZATION FLATER |
| set schema FLATER; |
| 0 rows inserted/updated/deleted |
| ij> --O SELECT USER FROM HU.ECCO; |
| VALUES USER; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| FLATER |
| ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment |
| ROLLBACK WORK; |
| ij> -- date_time print |
| |
| -- TEST:0894 FIPS sizing, length of column lists >= 750! |
| |
| CREATE TABLE LONG_NAMED_PEOPLE ( |
| FIRSTNAME VARCHAR (373) NOT NULL, |
| LASTNAME VARCHAR (373) NOT NULL, |
| AGE INT, |
| PRIMARY KEY (FIRSTNAME, LASTNAME)); |
| 0 rows inserted/updated/deleted |
| ij> -- PASS:0894 If table created successfully? |
| |
| COMMIT WORK; |
| ij> CREATE TABLE ORDERS ( |
| FIRSTNAME VARCHAR (373), |
| LASTNAME VARCHAR (373), |
| TITLE VARCHAR (80), |
| COST NUMERIC(5,2), |
| FOREIGN KEY (FIRSTNAME, LASTNAME) |
| REFERENCES LONG_NAMED_PEOPLE); |
| 0 rows inserted/updated/deleted |
| ij> -- PASS:0894 If table created successfully? |
| |
| COMMIT WORK; |
| ij> CREATE VIEW PEOPLE_ORDERS AS |
| --O SELECT * FROM LONG_NAMED_PEOPLE JOIN ORDERS |
| SELECT a.firstname, a.lastname, age, cost FROM LONG_NAMED_PEOPLE a JOIN ORDERS |
| ON (a.FIRSTNAME=ORDERS.FIRSTNAME and a.LASTNAME=ORDERS.LASTNAME); |
| 0 rows inserted/updated/deleted |
| ij> -- PASS:0894 If view created successfully? |
| |
| COMMIT WORK; |
| ij> INSERT INTO LONG_NAMED_PEOPLE VALUES ( |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaa', |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbb', |
| 20); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0894 If 1 row inserted successfully? |
| |
| INSERT INTO LONG_NAMED_PEOPLE VALUES ( |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccc', |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddd', |
| 25); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0894 If 1 row inserted successfully? |
| |
| INSERT INTO ORDERS VALUES ( |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaa', |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbb', |
| 'Gidget Goes Skiing', |
| 29.95); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0894 If 1 row inserted successfully? |
| |
| INSERT INTO ORDERS VALUES ( |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || |
| 'aaaaaaaaaaaaaaaaaaaaaaaaa', |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || |
| 'bbbbbbbbbbbbbbbbbbbbbbbbb', |
| 'Barney Goes Hawaiian', |
| 19.95); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0894 If 1 row inserted successfully? |
| |
| INSERT INTO ORDERS VALUES ( |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' || |
| 'ccccccccccccccccccccccccccccccc', |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' || |
| 'ddddddddddddddddddddddddddddddd', |
| 'Invasion of the Smurfs', |
| 9.95); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0894 If 1 row inserted successfully? |
| |
| SELECT FIRSTNAME, LASTNAME, AVG(COST) |
| FROM PEOPLE_ORDERS |
| GROUP BY LASTNAME, FIRSTNAME |
| ORDER BY LASTNAME, FIRSTNAME; |
| FIRSTNAME |LASTNAME |3 |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa&|bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb&|24.9500 |
| ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc&|ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd&|9.9500 |
| ij> -- PASS:0894 If 2 rows are returned in the following order? |
| -- NOTE: Columns c1 and c2 are 373 characters each! |
| -- c1 c2 c3 |
| -- == == == |
| -- PASS:0894 If aaaaaaaa.... bbbbbbbb.... 24.95 (+ or - 0.01)? |
| -- PASS:0894 If cccccccc.... dddddddd.... 9.95 (+ or - 0.01)? |
| |
| COMMIT WORK; |
| ij> --O DROP TABLE ORDERS CASCADE; |
| drop view people_orders; |
| 0 rows inserted/updated/deleted |
| ij> DROP TABLE ORDERS ; |
| 0 rows inserted/updated/deleted |
| ij> -- PASS:0894 If table dropped successfully? |
| |
| COMMIT WORK; |
| ij> --O DROP TABLE LONG_NAMED_PEOPLE CASCADE; |
| DROP TABLE LONG_NAMED_PEOPLE ; |
| 0 rows inserted/updated/deleted |
| ij> -- PASS:0894 If table dropped successfully? |
| |
| COMMIT WORK; |
| ij> -- END TEST >>> 0894 <<< END TEST |
| -- ********************************************* |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |