| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML158 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml158.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 |
| ROLLBACK WORK; |
| ij> -- date_time print |
| |
| -- TEST:0857 <join condition> set function, outer reference! Error |
| |
| DELETE FROM WORKS |
| WHERE EXISTS |
| (SELECT * FROM PROJ JOIN STAFF |
| ON PROJ.CITY <> STAFF.CITY |
| AND EMPNUM = WORKS.EMPNUM |
| AND PNUM = WORKS.PNUM); |
| ERROR 42X04: Column 'WORKS.EMPNUM' 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 'WORKS.EMPNUM' is not a column in the target table. |
| ij> -- PASS:0857 If ERROR OR SQLSTATE = 42X04 |
| |
| SELECT EMPNUM, PNUM FROM WORKS |
| ORDER BY EMPNUM, PNUM; |
| EM&|PN& |
| ------- |
| E1 |P1 |
| E1 |P2 |
| E1 |P3 |
| E1 |P4 |
| E1 |P5 |
| E1 |P6 |
| E2 |P1 |
| E2 |P2 |
| E3 |P2 |
| E4 |P2 |
| E4 |P4 |
| E4 |P5 |
| ij> -- PASS:0857 If 12 rows are returned in the following order? |
| -- empnum pnum |
| -- ====== ==== |
| -- PASS:0857 If E1 P1 ? |
| -- PASS:0857 If E1 P2 ? |
| -- PASS:0857 If E1 P3 ? |
| -- PASS:0857 If E1 P4 ? |
| -- PASS:0857 If E1 P5 ? |
| -- PASS:0857 If E1 P6 ? |
| -- PASS:0857 If E2 P1 ? |
| -- PASS:0857 If E2 P2 ? |
| -- PASS:0857 If E3 P2 ? |
| -- PASS:0857 If E4 P2 ? |
| -- PASS:0857 If E4 P4 ? |
| -- PASS:0857 If E4 P5 ? |
| |
| ROLLBACK WORK; |
| ij> SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS |
| GROUP BY EMPNUM |
| HAVING EMPNUM IN ( |
| --O SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF |
| --O ON WORKS.EMPNUM = STAFF.EMPNUM |
| SELECT a.EMPNUM FROM WORKS a, STAFF |
| where a.EMPNUM = STAFF.EMPNUM |
| --O AND HOURS < SUM (OWORKS.HOURS) / 3 |
| AND a.HOURS < (select SUM (b.HOURS) / 3 from works b where a.empnum = b.empnum) |
| AND GRADE > 10) |
| ORDER BY EMPNUM; |
| EM&|2 |
| --------------- |
| E1 |184 |
| E4 |140 |
| ij> -- PASS:0857 If 2 rows are returned in the following order? |
| -- empnum sum(hours) |
| -- ====== ========== |
| -- PASS:0857 If E1 184 ? |
| -- PASS:0857 If E4 140 ? |
| |
| SELECT EMPNUM, SUM (HOURS) FROM WORKS OWORKS |
| GROUP BY EMPNUM |
| HAVING EMPNUM IN ( |
| --O SELECT WORKS.EMPNUM FROM WORKS JOIN STAFF |
| --O ON WORKS.EMPNUM = STAFF.EMPNUM |
| SELECT a.EMPNUM FROM WORKS a, STAFF |
| where a.EMPNUM = STAFF.EMPNUM |
| --O AND HOURS >= 10 + AVG (OWORKS.HOURS) |
| AND a.HOURS >= (select 10 + AVG (b.HOURS) from works b where a.empnum = b.empnum) |
| AND CITY = 'Deale') |
| ORDER BY EMPNUM; |
| EM&|2 |
| --------------- |
| E1 |184 |
| E4 |140 |
| ij> -- PASS:0857 If 2 rows are returned in the following order? |
| -- empnum sum(hours) |
| -- ====== ========== |
| -- PASS:0857 If E1 184 ? |
| -- PASS:0857 If E4 140 ? |
| |
| COMMIT WORK; |
| ij> -- END TEST >>> 0857 <<< END TEST |
| -- ********************************************* |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |