ij> AUTOCOMMIT OFF; ij> -- MODULE DML075 -- SQL Test Suite, V6.0, Interactive SQL, dml075.sql -- 59-byte ID -- TEd Version # -- AUTHORIZATION HU set schema HU; 0 rows inserted/updated/deleted ij> --O SELECT USER FROM HU.ECCO; VALUES USER; 1 -------------------------------------------------------------------------------------------------------------------------------- HU ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment -- date_time print -- TEST:0431 Redundant rows in IN subquery! --O SELECT COUNT (*) FROM STAFF SELECT * FROM STAFF WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E1 |Alice |12 |Deale E2 |Betty |10 |Vienna E3 |Carmen |13 |Vienna E4 |Don |12 |Deale ij> -- PASS:0431 If count = 4? INSERT INTO STAFF1 SELECT * FROM STAFF; 5 rows inserted/updated/deleted ij> --O SELECT COUNT (*) FROM STAFF1 SELECT * FROM STAFF1 WHERE EMPNUM IN (SELECT EMPNUM FROM WORKS); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E1 |Alice |12 |Deale E2 |Betty |10 |Vienna E3 |Carmen |13 |Vienna E4 |Don |12 |Deale ij> -- PASS:0431 If count = 4? ROLLBACK WORK; ij> -- END TEST >>> 0431 <<< END TEST -- ************************************************************* -- TEST:0432 Unknown comparison predicate in ALL, SOME, ANY! -- setup UPDATE PROJ SET CITY = NULL WHERE PNUM = 'P3'; 1 row inserted/updated/deleted ij> --OSELECT COUNT(*) SELECT * FROM STAFF WHERE CITY = ALL (SELECT CITY FROM PROJ WHERE PNAME = 'SDP'); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- ij> -- PASS:0432 If count = 0? --OSELECT COUNT(*) SELECT * FROM STAFF WHERE CITY <> ALL (SELECT CITY FROM PROJ WHERE PNAME = 'SDP'); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- ij> -- PASS:0432 If count = 0? --OSELECT COUNT(*) SELECT * FROM STAFF WHERE CITY = ANY (SELECT CITY FROM PROJ WHERE PNAME = 'SDP'); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E1 |Alice |12 |Deale E4 |Don |12 |Deale ij> -- PASS:0432 If count = 2? --OSELECT COUNT(*) SELECT * FROM STAFF WHERE CITY <> ANY (SELECT CITY FROM PROJ WHERE PNAME = 'SDP'); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E2 |Betty |10 |Vienna E3 |Carmen |13 |Vienna E5 |Ed |13 |Akron ij> -- PASS:0432 If count = 3? --OSELECT COUNT(*) SELECT * FROM STAFF WHERE CITY = SOME (SELECT CITY FROM PROJ WHERE PNAME = 'SDP'); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E1 |Alice |12 |Deale E4 |Don |12 |Deale ij> -- PASS:0432 If count = 2? --OSELECT COUNT(*) SELECT * FROM STAFF WHERE CITY <> SOME (SELECT CITY FROM PROJ WHERE PNAME = 'SDP'); EM&|EMPNAME |GRADE|CITY ---------------------------------------------- E2 |Betty |10 |Vienna E3 |Carmen |13 |Vienna E5 |Ed |13 |Akron ij> -- PASS:0432 If count = 3? ROLLBACK WORK; ij> -- END TEST >>> 0432 <<< END TEST -- ************************************************************* -- TEST:0433 Empty subquery in ALL, SOME, ANY! --O SELECT COUNT(*) FROM PROJ SELECT * FROM PROJ WHERE PNUM = ALL (SELECT PNUM FROM WORKS WHERE EMPNUM = 'E8'); PN&|PNAME |PTYPE |BUDGET |CITY ---------------------------------------------------------- P1 |MXSS |Design|10000 |Deale P2 |CALM |Code |30000 |Vienna P3 |SDP |Test |30000 |Tampa P4 |SDP |Design|20000 |Deale P5 |IRM |Test |10000 |Vienna P6 |PAYR |Design|50000 |Deale ij> -- PASS:0433 If count = 6? --O SELECT COUNT(*) FROM PROJ SELECT * FROM PROJ WHERE PNUM <> ALL (SELECT PNUM FROM WORKS WHERE EMPNUM = 'E8'); PN&|PNAME |PTYPE |BUDGET |CITY ---------------------------------------------------------- P1 |MXSS |Design|10000 |Deale P2 |CALM |Code |30000 |Vienna P3 |SDP |Test |30000 |Tampa P4 |SDP |Design|20000 |Deale P5 |IRM |Test |10000 |Vienna P6 |PAYR |Design|50000 |Deale ij> -- PASS:0433 If count = 6? --O SELECT COUNT(*) FROM PROJ SELECT * FROM PROJ WHERE PNUM = ANY (SELECT PNUM FROM WORKS WHERE EMPNUM = 'E8'); PN&|PNAME |PTYPE |BUDGET |CITY ---------------------------------------------------------- ij> -- PASS:0433 If count = 0? --O SELECT COUNT(*) FROM PROJ SELECT * FROM PROJ WHERE PNUM <> ANY (SELECT PNUM FROM WORKS WHERE EMPNUM = 'E8'); PN&|PNAME |PTYPE |BUDGET |CITY ---------------------------------------------------------- ij> -- PASS:0433 If count = 0? --O SELECT COUNT(*) FROM PROJ SELECT * FROM PROJ WHERE PNUM = SOME (SELECT PNUM FROM WORKS WHERE EMPNUM = 'E8'); PN&|PNAME |PTYPE |BUDGET |CITY ---------------------------------------------------------- ij> -- PASS:0433 If count = 0? --O SELECT COUNT(*) FROM PROJ SELECT * FROM PROJ WHERE PNUM <> SOME (SELECT PNUM FROM WORKS WHERE EMPNUM = 'E8'); PN&|PNAME |PTYPE |BUDGET |CITY ---------------------------------------------------------- ij> -- PASS:0433 If count = 0? -- END TEST >>> 0433 <<< END TEST -- ************************************************************* -- TEST:0434 GROUP BY with HAVING EXISTS-correlated set function! SELECT PNUM, SUM(HOURS) FROM WORKS c GROUP BY PNUM --O HAVING EXISTS (SELECT PNAME FROM PROJ --O WHERE PROJ.PNUM = WORKS.PNUM AND HAVING EXISTS (SELECT PNAME FROM PROJ, works a WHERE PROJ.PNUM = a.PNUM AND --O SUM(WORKS.HOURS) > PROJ.BUDGET / 200); PROJ.BUDGET / 200 < (select sum(hours) from works b where a.pnum = b.pnum and a.pnum = c.pnum)); PN&|2 --------------- P1 |80 P5 |92 ij> -- PASS:0434 If 2 rows selected with values (in any order):? -- PASS:0434 PNUM = 'P1', SUM(HOURS) = 80? -- PASS:0434 PNUM = 'P5', SUM(HOURS) = 92? -- END TEST >>> 0434 <<< END TEST -- ************************************************************* -- TEST:0442 DISTINCT with GROUP BY, HAVING! SELECT PTYPE, CITY FROM PROJ GROUP BY PTYPE, CITY HAVING AVG(BUDGET) > 21000; PTYPE |CITY ---------------------- Code |Vienna Design|Deale Test |Tampa ij> -- PASS:0442 If 3 rows selected with PTYPE/CITY values(in any order):? -- PASS:0442 Code/Vienna, Design/Deale, Test/Tampa? SELECT DISTINCT PTYPE, CITY FROM PROJ GROUP BY PTYPE, CITY HAVING AVG(BUDGET) > 21000; PTYPE |CITY ---------------------- Code |Vienna Design|Deale Test |Tampa ij> -- PASS:0442 If 3 rows selected with PTYPE/CITY values(in any order):? -- PASS:0442 Code/Vienna, Design/Deale, Test/Tampa? SELECT DISTINCT SUM(BUDGET) FROM PROJ GROUP BY PTYPE, CITY HAVING AVG(BUDGET) > 21000; 1 ------------------- 30000 80000 ij> -- PASS:0442 If 2 rows selected (in any order):? -- PASS:0442 with SUM(BUDGET) values 30000 and 80000? -- END TEST >>> 0442 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>