| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML014 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml014.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:0045 BETWEEN predicate! |
| SELECT PNUM |
| FROM PROJ |
| WHERE BUDGET BETWEEN 40000 AND 60000; |
| PN& |
| --- |
| P6 |
| ij> -- PASS:0045 If PNUM = 'P6'? |
| |
| SELECT PNUM |
| FROM PROJ |
| WHERE BUDGET >= 40000 AND BUDGET <= 60000; |
| PN& |
| --- |
| P6 |
| ij> -- PASS:0045 If PNUM = 'P6'? |
| |
| -- END TEST >>> 0045 <<< END TEST |
| -- *********************************************************** |
| |
| -- TEST:0046 NOT BETWEEN predicate ! |
| SELECT CITY |
| FROM STAFF |
| WHERE GRADE NOT BETWEEN 12 AND 13; |
| CITY |
| --------------- |
| Vienna |
| ij> -- PASS:0046 If CITY = 'Vienna'? |
| |
| SELECT CITY |
| FROM STAFF |
| WHERE NOT(GRADE BETWEEN 12 AND 13); |
| CITY |
| --------------- |
| Vienna |
| ij> -- PASS:0046 If CITY = 'Vienna'? |
| |
| -- END TEST >>> 0046 <<< END TEST |
| -- ************************************************************* |
| |
| -- TEST:0047 IN predicate! |
| SELECT STAFF.EMPNAME |
| FROM STAFF |
| WHERE STAFF.EMPNUM IN |
| (SELECT WORKS.EMPNUM |
| FROM WORKS |
| WHERE WORKS.PNUM IN |
| (SELECT PROJ.PNUM |
| FROM PROJ |
| WHERE PROJ.CITY='Tampa')); |
| EMPNAME |
| -------------------- |
| Alice |
| ij> -- PASS:0047 If EMPNAME = 'Alice'? |
| |
| SELECT STAFF.EMPNAME |
| FROM STAFF |
| WHERE STAFF.EMPNUM = ANY |
| (SELECT WORKS.EMPNUM |
| FROM WORKS |
| WHERE WORKS.PNUM IN |
| (SELECT PROJ.PNUM |
| FROM PROJ |
| WHERE PROJ.CITY='Tampa')); |
| EMPNAME |
| -------------------- |
| Alice |
| ij> -- PASS:0047 If EMPNAME = 'Alice'? |
| |
| -- END TEST >>> 0047 <<< END TEST |
| -- *********************************************************** |
| |
| -- TEST:0048 NOT IN predicate! |
| SELECT WORKS.HOURS |
| FROM WORKS |
| WHERE WORKS.PNUM NOT IN |
| (SELECT PROJ.PNUM |
| FROM PROJ |
| WHERE PROJ.BUDGET BETWEEN 5000 AND 40000); |
| HOURS |
| ------ |
| 12 |
| ij> -- PASS:0048 If HOURS = 12? |
| |
| SELECT WORKS.HOURS |
| FROM WORKS |
| WHERE NOT (WORKS.PNUM IN |
| (SELECT PROJ.PNUM |
| FROM PROJ |
| WHERE PROJ.BUDGET BETWEEN 5000 AND 40000)); |
| HOURS |
| ------ |
| 12 |
| ij> -- PASS:0048 If HOURS = 12? |
| |
| -- END TEST >>> 0048 <<< END TEST |
| -- **************************************************************** |
| |
| -- TEST:0049 IN predicate value list! |
| SELECT HOURS |
| FROM WORKS |
| WHERE PNUM NOT IN |
| (SELECT PNUM |
| FROM WORKS |
| WHERE PNUM IN ('P1','P2','P4','P5','P6')); |
| HOURS |
| ------ |
| 80 |
| ij> -- PASS:0049 If HOURS = 80? |
| |
| SELECT HOURS |
| FROM WORKS |
| WHERE NOT (PNUM IN |
| (SELECT PNUM |
| FROM WORKS |
| WHERE PNUM IN ('P1','P2','P4','P5','P6'))); |
| HOURS |
| ------ |
| 80 |
| ij> -- PASS:0049 If HOURS = 80? |
| |
| -- END TEST >>> 0049 <<< END TEST |
| -- ************************************************************** |
| |
| -- TEST:0050 LIKE predicate -- %! |
| SELECT EMPNAME |
| FROM STAFF |
| WHERE EMPNAME LIKE 'Al%'; |
| EMPNAME |
| -------------------- |
| Alice |
| ij> -- PASS:0050 If EMPNAME = 'Alice'? |
| |
| -- END TEST >>> 0050 <<< END TEST |
| -- ************************************************************** |
| |
| -- TEST:0051 LIKE predicate -- underscore! |
| SELECT CITY |
| FROM STAFF |
| WHERE EMPNAME LIKE 'B__t%'; |
| CITY |
| --------------- |
| Vienna |
| ij> -- PASS:0051 If CITY = 'Vienna'? |
| |
| -- END TEST >>> 0051 <<< END TEST |
| -- ************************************************************* |
| |
| -- TEST:0052 LIKE predicate -- ESCAPE character! |
| |
| -- setup |
| INSERT INTO STAFF |
| VALUES('E36','Huyan',36,'Xi_an%'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0052 If 1 row is inserted? |
| |
| --O SELECT CITY |
| --O FROM STAFF |
| --O WHERE CITY LIKE 'XiS___S%%' |
| --O ESCAPE 'S'; |
| -- PASS:0052 If CITY = 'Xi_an%' ? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0052 <<< END TEST |
| -- ************************************************************** |
| |
| -- TEST:0053 NOT LIKE predicate! |
| |
| -- setup |
| INSERT INTO STAFF |
| VALUES('E36','Huyan',36,'Xi_an%'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0053 If 1 row is inserted? |
| |
| SELECT COUNT(*) |
| FROM STAFF |
| WHERE EMPNUM NOT LIKE '_36'; |
| 1 |
| ----------- |
| 5 |
| ij> -- PASS:0053 If count = 5? |
| |
| SELECT COUNT(*) |
| FROM STAFF |
| WHERE NOT(EMPNUM LIKE '_36'); |
| 1 |
| ----------- |
| 5 |
| ij> -- PASS:0053 If count = 5? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0053 <<< END TEST |
| -- *************************************************************** |
| |
| -- TEST:0054 IS NULL predicate! |
| |
| -- setup |
| INSERT INTO STAFF |
| VALUES('E36','Huyan',36,NULL); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0054 If 1 row is inserted? |
| |
| SELECT EMPNAME |
| FROM STAFF |
| WHERE CITY IS NULL; |
| EMPNAME |
| -------------------- |
| Huyan |
| ij> -- PASS:0054 If EMPNAME = 'Huyan'? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0054 <<< END TEST |
| -- ************************************************************ |
| |
| -- TEST:0055 NOT NULL predicate! |
| |
| -- setup |
| INSERT INTO STAFF |
| VALUES('E36','Huyan',36,NULL); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0055 If 1 row is inserted? |
| |
| SELECT COUNT(*) |
| FROM STAFF; |
| 1 |
| ----------- |
| 6 |
| ij> -- PASS:0055 If count = 6? |
| |
| SELECT COUNT(*) |
| FROM STAFF |
| WHERE CITY IS NOT NULL; |
| 1 |
| ----------- |
| 5 |
| ij> -- PASS:0055 If count = 5? |
| |
| SELECT COUNT(*) |
| FROM STAFF |
| WHERE NOT (CITY IS NULL); |
| 1 |
| ----------- |
| 5 |
| ij> -- PASS:0055 If count = 5? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0055 <<< END TEST |
| -- *************************************************************** |
| |
| -- TEST:0056 NOT EXISTS predicate! |
| SELECT STAFF.EMPNAME |
| FROM STAFF |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM PROJ |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM WORKS |
| WHERE STAFF.EMPNUM = WORKS.EMPNUM |
| AND WORKS.PNUM=PROJ.PNUM)); |
| EMPNAME |
| -------------------- |
| Alice |
| ij> -- PASS:0056 If EMPNAME = 'Alice'? |
| |
| -- END TEST >>> 0056 <<< END TEST |
| -- ************************************************************ |
| |
| -- TEST:0057 ALL quantifier ! |
| SELECT CITY |
| FROM PROJ |
| WHERE BUDGET > ALL |
| (SELECT BUDGET |
| FROM PROJ |
| WHERE CITY='Vienna'); |
| CITY |
| --------------- |
| Deale |
| ij> -- PASS:0057 If CITY = 'Deale'? |
| |
| -- END TEST >>> 0057 <<< END TEST |
| -- ************************************************************** |
| |
| -- TEST:0058 SOME quantifier! |
| SELECT EMPNAME |
| FROM STAFF |
| WHERE GRADE < SOME |
| (SELECT BUDGET/1000 - 39 |
| FROM PROJ |
| WHERE CITY='Deale'); |
| EMPNAME |
| -------------------- |
| Betty |
| ij> -- PASS:0058 If EMPNAME = 'Betty'? |
| |
| -- END TEST >>> 0058 <<< END TEST |
| -- ************************************************************* |
| |
| -- TEST:0059 ANY quantifier ! |
| SELECT EMPNAME |
| FROM STAFF |
| WHERE GRADE < ANY |
| (SELECT BUDGET/1000 - 39 |
| FROM PROJ |
| WHERE CITY = 'Deale'); |
| EMPNAME |
| -------------------- |
| Betty |
| ij> -- PASS:0059 If EMPNAME = 'Betty'? |
| |
| -- END TEST >>> 0059 <<< END TEST |
| |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |