| 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> |
