ij> AUTOCOMMIT OFF; ij> -- MODULE DML147 -- SQL Test Suite, V6.0, Interactive SQL, dml147.sql -- 59-byte ID -- TEd Version # -- AUTHORIZATION FLATER --O SELECT USER FROM HU.ECCO; VALUES USER; 1 -------------------------------------------------------------------------------------------------------------------------------- FLATER ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment --O ROLLBACK WORK; -- date_time print -- TEST:0840 Roll back schema manipulation ! CREATE TABLE NOT_THERE (C1 CHAR (10)); 0 rows inserted/updated/deleted ij> -- PASS:0840 If table is created? ROLLBACK WORK; ij> INSERT INTO NOT_THERE VALUES ('1234567890'); ERROR 42X05: Table/View 'NOT_THERE' does not exist. ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected? ROLLBACK WORK; ij> CREATE VIEW NOT_HERE AS SELECT * FROM USIG; 0 rows inserted/updated/deleted ij> -- PASS:0840 If view is created? ROLLBACK WORK; ij> SELECT COUNT (*) FROM NOT_HERE; ERROR 42X05: Table/View 'NOT_HERE' does not exist. ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected? ROLLBACK WORK; ij> ALTER TABLE USIG ADD COLUMN NUL INT; 0 rows inserted/updated/deleted ij> -- PASS:0840 If column is added? ROLLBACK WORK; ij> SELECT COUNT (*) FROM USIG WHERE NUL IS NULL; ERROR 42X04: Column 'NUL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NUL' is not a column in the target table. ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected? ROLLBACK WORK; ij> --O DROP TABLE USIG CASCADE; DROP TABLE USIG ; 0 rows inserted/updated/deleted ij> -- PASS:0840 If table is dropped? ROLLBACK WORK; ij> SELECT COUNT(*) FROM U_SIG; 1 ----------- 2 ij> -- PASS:0840 If count = 2? ROLLBACK WORK; ij> SELECT COUNT(*) FROM USIG; 1 ----------- 2 ij> -- PASS:0840 If count = 2? ROLLBACK WORK; ij> -- END TEST >>> 0840 <<< END TEST -- ********************************************* -- TEST:0841 Multiple-join and default order of joins ! -- setup DELETE FROM HU.STAFF4; 0 rows inserted/updated/deleted WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> INSERT INTO HU.STAFF4 SELECT * FROM HU.STAFF3 WHERE EMPNUM > 'E3'; 2 rows inserted/updated/deleted ij> --O SELECT EMPNUM FROM SELECT a.EMPNUM FROM --O HU.STAFF3 NATURAL LEFT JOIN HU.STAFF NATURAL INNER JOIN HU.STAFF4 HU.STAFF3 a, HU.staff b, HU.staff4 c where a.empnum = b.empnum and b.empnum = c.empnum ORDER BY EMPNUM DESC; EM& --- E5 E4 ij> -- PASS:0841 If 2 rows selected? -- PASS:0841 If ordered EMPNUM values are: E5, E4 ? --O SELECT EMPNUM FROM --O (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF) NATURAL INNER JOIN HU.STAFF4 --O ORDER BY EMPNUM ASC; -- PASS:0841 If 2 rows selected? -- PASS:0841 If ordered EMPNUM values are: E4, E5 ? --O SELECT EMPNUM FROM --O HU.STAFF3 NATURAL LEFT JOIN (HU.STAFF NATURAL INNER JOIN HU.STAFF4) --O ORDER BY EMPNUM; --O ; -- PASS:0841 If 5 rows selected? -- PASS:0841 If ordered EMPNUM values are: E1, E2, E3, E4, E5 ? ROLLBACK WORK; ij> -- END TEST >>> 0841 <<< END TEST -- ********************************************* -- TEST:0842 Multi-column joins ! -- setup CREATE TABLE STAFF66 ( SALARY INTEGER, EMPNAME CHAR(20), GRADE DECIMAL, EMPNUM CHAR(3)); 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- setup INSERT INTO STAFF66 SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM FROM HU.STAFF3 WHERE EMPNUM > 'E2'; 3 rows inserted/updated/deleted ij> -- PASS:0842 If 3 rows inserted ? UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM = 'E5'; 1 row inserted/updated/deleted ij> -- PASS:0842 If 1 row updated ? UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE = 12; 2 rows inserted/updated/deleted ij> -- PASS:0842 If 2 rows updated ? -- FULL OUTER JOIN of tables with unique data in the joined column --O SELECT EMPNUM, CITY, SALARY --O FROM HU.STAFF3 LEFT JOIN STAFF66 USING (EMPNUM) --O UNION --O SELECT EMPNUM, CITY, SALARY --O FROM HU.STAFF3 RIGHT JOIN STAFF66 USING (EMPNUM) --O ORDER BY EMPNUM; -- PASS:0842 If 6 rows selected with ordered rows and column values ? -- PASS:0842 E1 Deale NULL ? -- PASS:0842 E2 Vienna NULL ? -- PASS:0842 E3 Vienna 13000 ? -- PASS:0842 E4 Deale 12000 ? -- PASS:0842 E5 NULL 13000 ? -- PASS:0842 E6 Akron NULL ? -- 7.5 SR 6 d -- table STAFF66 has 3 rows, only 1 matching on all columns -- this is a 3-column join: SELECT * FROM --O STAFF66 NATURAL INNER JOIN HU.STAFF3; STAFF66 a, HU.staff3 b where a.empnum = b.empnum and a.grade = b.grade and a.empname = b.empname; SALARY |EMPNAME |GRADE |EMP&|EM&|EMPNAME |GRADE|CITY ------------------------------------------------------------------------------------------- 13000 |Carmen |13 |E3 |E3 |Carmen |13 |Vienna ij> -- PASS:0842 If 1 row selected? -- PASS:0842 If column values are in the exact order: ? -- PASS:0842 EMPNAME=Carmen,GRADE=13,EMPNUM=E3,SALARY=13000,CITY=Vienna? -- table STAFF66 has 3 rows, only 1 matching on all columns -- this is a 3-column join, preserving HU.STAFF3: --O SELECT EMPNUM, EMPNAME, SALARY FROM --O HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66 --O WHERE EMPNUM > 'E1' --O ORDER BY EMPNUM ASC; -- PASS:0842 If 4 rows selected with ordered rows and column values ? -- PASS:0842 E2 Betty NULL ? -- PASS:0842 E3 Carmen 13000 ? -- PASS:0842 E4 Ali NULL ? -- PASS:0842 E6 Ed NULL ? -- table HU.STAFF has 5 rows, only 3 matching on all columns -- this is a 3-column join, preserving HU.STAFF: --O SELECT EMPNUM, EMPNAME, SALARY FROM --O STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF --O WHERE EMPNUM > 'E1' --O ORDER BY EMPNUM DESC; -- PASS:0842 If 4 rows selected with ordered rows and column values ? -- PASS:0842 E5 Ed 13000 ? -- PASS:0842 E4 Don 12000 ? -- PASS:0842 E3 Carmen 13000 ? -- PASS:0842 E2 Betty NULL ? -- table HU.STAFF has 5 rows, only 3 matching on all columns -- ordinal position is determined by order in T1, not USING list -- REF: 7.5 SR 6 d -- this is a 3-column join, preserving HU.STAFF: --O SELECT * FROM --O STAFF66 RIGHT JOIN HU.STAFF USING ( GRADE, EMPNUM, EMPNAME) --O WHERE EMPNUM > 'E1' --O ORDER BY EMPNUM; -- PASS:0842 If 4 rows selected with ordered rows and column values ? -- PASS:0842 Betty 10 E2 NULL Vienna ? -- PASS:0842 Carmen 13 E3 13000 Vienna ? -- PASS:0842 Don 12 E4 12000 Deale ? -- PASS:0842 Ed 13 E5 13000 Akron ? -- table STAFF66 has 3 rows, with 2 matching on named columns -- this is a 2-column join, preserving HU.STAFF3: --O SELECT * FROM --O HU.STAFF3 LEFT JOIN STAFF66 USING (GRADE, EMPNUM) --O WHERE EMPNUM > 'E1' --O ORDER BY EMPNUM ASC; -- PASS:0842 If 4 rows selected with ordered rows and column values ? -- PASS:0842 E2 10 Betty Vienna NULL NULL ? -- PASS:0842 E3 13 Carmen Vienna 13000 Carmen ? -- PASS:0842 E4 12 Ali Deale 12000 Don ? -- PASS:0842 E6 13 Ed Akron NULL NULL ? -- similar to above, except for explicit names of columns --O SELECT staff3.EMPNUM, staff3.GRADE, HU.STAFF3.EMPNAME, CITY, SELECT HU.staff3.EMPNUM, HU.staff3.GRADE, HU.STAFF3.EMPNAME, CITY, SALARY, STAFF66.EMPNAME FROM --O HU.STAFF3 LEFT JOIN STAFF66 USING (GRADE, EMPNUM) --O WHERE EMPNUM = 'E3'; HU.STAFF3, STAFF66 where HU.staff3.GRADE = staff66.grade and HU.staff3.EMPNUM = staff66.empnum and HU.staff3.EMPNUM = 'E3'; EM&|GRADE|EMPNAME |CITY |SALARY |EMPNAME ------------------------------------------------------------------------------- E3 |13 |Carmen |Vienna |13000 |Carmen ij> -- PASS:0842 If 1 row selected with ordered column values? -- PASS:0842 E3 13 Carmen Vienna 13000 Carmen ? -- REF: 7.5 GR 1 d ii -- this is a cartesian product --O SELECT COUNT (*) FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ; SELECT count (*) FROM STAFF66 , HU.PROJ; 1 ----------- 18 ij> -- PASS:0842 If count = 18? ROLLBACK WORK; ij> --O DROP TABLE STAFF66 CASCADE; DROP TABLE STAFF66 ; 0 rows inserted/updated/deleted ij> COMMIT WORK; ij> -- END TEST >>> 0842 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>