ij> AUTOCOMMIT OFF; ij> -- MODULE DML114 -- SQL Test Suite, V6.0, Interactive SQL, dml114.sql -- 59-byte ID -- TEd Version # -- AUTHORIZATION FLATER set schema FLATER; 0 rows inserted/updated/deleted ij> --0 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:0635 Feature 13, grouped operations (static)! CREATE VIEW WORKWEEK AS SELECT EMPNUM, HOURS FROM HU.WORKS GROUP BY HOURS, EMPNUM; 0 rows inserted/updated/deleted ij> -- PASS:0635 If table is created? COMMIT WORK; ij> SELECT EMPNUM, SUM (HOURS) FROM WORKWEEK WHERE HOURS > 20 GROUP BY EMPNUM HAVING EMPNUM = 'E1'; EM&|2 --------------- E1 |120 ij> -- PASS:0635 If 1 row selected and EMPNUM = 'E1' and SUM(HOURS) = 120? SELECT COUNT(*) FROM WORKWEEK WHERE HOURS > 40; 1 ----------- 3 ij> -- PASS:0635 If count = 3? SELECT EMPNAME FROM HU.STAFF, WORKWEEK WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM AND HOURS = 12; EMPNAME -------------------- Alice ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'? SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM), AVG(HOURS) FROM WORKWEEK; 1 |2 |3 |4 ----------------------------------- 10 |E4 |E1 |43.2000 ij> -- PASS:0635 If 1 row selected and count = 10 and MAX(EMPNUM) = 'E4'? -- PASS:0635 AND MIN(EMPNUM) = 'E1' and AVG(HOURS) = 43 (approximately)? SELECT EMPNAME FROM HU.STAFF WHERE EMPNUM = (SELECT EMPNUM FROM WORKWEEK WHERE HOURS = 12); EMPNAME -------------------- Alice ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'? SELECT EMPNAME FROM HU.STAFF WHERE EMPNUM = (SELECT EMPNUM FROM HU.WORKS GROUP BY EMPNUM, HOURS HAVING HOURS = 12); EMPNAME -------------------- Alice ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'? -- NOTE:0635 Cursor subtest deleted. COMMIT WORK; ij> --0 DROP VIEW WORKWEEK CASCADE; DROP VIEW WORKWEEK ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- END TEST >>> 0635 <<< END TEST -- ********************************************* -- TEST:0637 Feature 14, Qualified * in select list (static)! CREATE VIEW QUALSTAR AS SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM; 0 rows inserted/updated/deleted ij> -- PASS:0637 If view is created? COMMIT WORK; ij> CREATE VIEW CORRQUALSTAR AS SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM; 0 rows inserted/updated/deleted ij> -- PASS:0637 If view is created? COMMIT WORK; ij> CREATE VIEW SUBQ2 AS SELECT DISTINCT * FROM QUALSTAR; 0 rows inserted/updated/deleted ij> -- PASS:0637 If view is created? COMMIT WORK; ij> CREATE VIEW CORRSUBQ2 AS SELECT DISTINCT * FROM CORRQUALSTAR; 0 rows inserted/updated/deleted ij> -- PASS:0637 If view is created? COMMIT WORK; ij> SELECT COUNT(*) FROM QUALSTAR; 1 ----------- 12 ij> -- PASS:0637 If count = 12? SELECT COUNT(*) FROM SUBQ2; 1 ----------- 10 ij> -- PASS:0637 If count = 10? SELECT EMPNUM, GRADE, CITY, HOURS FROM QUALSTAR WHERE EMPNAME = 'Carmen'; EM&|GRADE|CITY |HOURS -------------------------------- E3 |13 |Vienna |20 ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and GRADE = 13? -- PASS:0637 AND CITY = 'Vienna' and HOURS = 20? -- NOTE:0637 Cursor subtest deleted. SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM AND EMPNAME = 'Carmen'; EM&|EMPNAME |GRADE|CITY |HOURS ----------------------------------------------------- E3 |Carmen |13 |Vienna |20 ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and EMPNAME = 'Carmen'? -- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20? SELECT COUNT(*) FROM CORRQUALSTAR; 1 ----------- 12 ij> -- PASS:0637 If count = 12? SELECT COUNT(*) FROM CORRSUBQ2; 1 ----------- 10 ij> -- PASS:0637 If count = 10? SELECT EMPNUM, GRADE, CITY, HOURS FROM CORRQUALSTAR WHERE EMPNAME = 'Carmen'; EM&|GRADE|CITY |HOURS -------------------------------- E3 |13 |Vienna |20 ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3'? -- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20? COMMIT WORK; ij> --0 DROP VIEW QUALSTAR CASCADE; DROP VIEW SUBQ2 ; 0 rows inserted/updated/deleted ij> DROP VIEW QUALSTAR ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> --0 DROP VIEW CORRQUALSTAR CASCADE; DROP VIEW CORRSUBQ2 ; 0 rows inserted/updated/deleted ij> DROP VIEW CORRQUALSTAR ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- END TEST >>> 0637 <<< END TEST -- ********************************************* -- TEST:0639 Feature 15, Lowercase Identifiers (static)! create view Staff (Empnum, empname, Grade, City) as select empnum, EMPNAME, Grade, cItY from Hu.Staff; 0 rows inserted/updated/deleted ij> -- PASS:0639 If view is created? commit work; ij> SELECT EMPNUM as WhatsHisNumber, GRADE, CITY FROM Flater.staff FLaterStaff_Flater WHERE EMPNAME = 'Carmen' --0 SQL92 does not scope renames into the query, just outside it -- ALC --0 AND FLATERstaff_fLATER.whatshisnumber = 'E3'; AND FLATERstaff_fLATER.empnum = 'E3'; WH&|GRADE|CITY ------------------------- E3 |13 |Vienna ij> -- PASS:0639 If 1 row selected and EMPNUM = 'E3'? -- PASS:0639 AND GRADE = 13 and CITY = 'Vienna'? -- NOTE:0639 Cursor subtest deleted. COMMIT WORK; ij> --0 DROP VIEW STAFF CASCADE; DROP VIEW STAFF ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- END TEST >>> 0639 <<< END TEST -- ********************************************* -- TEST:0641 Feature 16, PRIMARY KEY enhancement (static)! CREATE TABLE FEAT16 ( EMPNUM INT NOT NULL CONSTRAINT FEAT16_PK PRIMARY KEY, PNUM INT NOT NULL CONSTRAINT FEAT16_PNUM UNIQUE); 0 rows inserted/updated/deleted ij> -- PASS:0641 If view is created? COMMIT WORK; ij> CREATE TABLE BARNO ( P1 INT NOT NULL, P2 CHAR NOT NULL, X1 INT NOT NULL, X2 CHAR NOT NULL, CONSTRAINT BARNO_UNIQUE UNIQUE (X2, X1), CONSTRAINT BARNO_PK PRIMARY KEY (P1, P2)); 0 rows inserted/updated/deleted ij> -- PASS:0641 If view is created? COMMIT WORK; ij> INSERT INTO FEAT16 VALUES (1, 10); 1 row inserted/updated/deleted ij> -- PASS:0641 If 1 row is inserted? INSERT INTO FEAT16 VALUES (2, 20); 1 row inserted/updated/deleted ij> -- PASS:0641 If 1 row is inserted? INSERT INTO FEAT16 VALUES (1, 30); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'FEAT16_PK' defined on 'FEAT16'. ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted? INSERT INTO FEAT16 VALUES (3, 20); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'FEAT16_PNUM' defined on 'FEAT16'. ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted? INSERT INTO FEAT16 VALUES (3, NULL); ERROR 23502: Column 'PNUM' cannot accept a NULL value. ij> -- PASS:0641 If 1 row is inserted? INSERT INTO FEAT16 VALUES (4, NULL); ERROR 23502: Column 'PNUM' cannot accept a NULL value. ij> -- PASS:0641 If 1 row is inserted? INSERT INTO FEAT16 VALUES (5, NULL); ERROR 23502: Column 'PNUM' cannot accept a NULL value. ij> -- PASS:0641 If 1 row is inserted? INSERT INTO BARNO VALUES (1, 'A', 10, 'a'); 1 row inserted/updated/deleted ij> -- PASS:0641 If 1 row is inserted? INSERT INTO BARNO VALUES (2, 'A', 20, 'a'); 1 row inserted/updated/deleted ij> -- PASS:0641 If 1 row is inserted? INSERT INTO BARNO VALUES (1, 'A', 30, 'a'); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'BARNO_PK' defined on 'BARNO'. ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted? INSERT INTO BARNO VALUES (3, 'A', 20, 'a'); ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'BARNO_UNIQUE' defined on 'BARNO'. ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted? INSERT INTO BARNO VALUES (3, NULL, 30, 'a'); ERROR 23502: Column 'P2' cannot accept a NULL value. ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted? -- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted? INSERT INTO BARNO VALUES (3, NULL, 30, 'b'); ERROR 23502: Column 'P2' cannot accept a NULL value. ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted? -- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted? INSERT INTO BARNO VALUES (3, 'A', 30, NULL); ERROR 23502: Column 'X2' cannot accept a NULL value. ij> -- PASS:0641 If 1 row is inserted? INSERT INTO BARNO VALUES (3, 'B', 30, NULL); ERROR 23502: Column 'X2' cannot accept a NULL value. ij> -- PASS:0641 If 1 row is inserted? INSERT INTO BARNO VALUES (4, 'B', NULL, NULL); ERROR 23502: Column 'X1' cannot accept a NULL value. ij> -- PASS:0641 If 1 row is inserted? COMMIT WORK; ij> --0 DROP TABLE FEAT16 CASCADE; DROP TABLE FEAT16 ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> --0 DROP TABLE BARNO CASCADE; DROP TABLE BARNO ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- END TEST >>> 0641 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>