| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML001 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml001.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:0001 SELECT with ORDER BY DESC! |
| |
| SELECT EMPNUM,HOURS |
| FROM WORKS |
| WHERE PNUM='P2' |
| ORDER BY EMPNUM DESC; |
| EM&|HOURS |
| ---------- |
| E4 |20 |
| E3 |20 |
| E2 |80 |
| E1 |20 |
| ij> -- PASS:0001 If 4 rows selected and last EMPNUM = 'E1'? |
| |
| -- END TEST >>> 0001 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0002 SELECT with ORDER BY integer ASC! |
| |
| SELECT EMPNUM,HOURS |
| FROM WORKS |
| WHERE PNUM='P2' |
| ORDER BY 2 ASC; |
| EM&|HOURS |
| ---------- |
| E4 |20 |
| E3 |20 |
| E1 |20 |
| E2 |80 |
| ij> -- PASS:0002 If 4 rows selected and last HOURS = 80? |
| |
| -- END TEST >>> 0002 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0003 SELECT with ORDER BY DESC integer, named column! |
| |
| SELECT EMPNUM,HOURS |
| FROM WORKS |
| WHERE PNUM = 'P2' |
| ORDER BY 2 DESC,EMPNUM DESC; |
| EM&|HOURS |
| ---------- |
| E2 |80 |
| E4 |20 |
| E3 |20 |
| E1 |20 |
| ij> -- PASS:0003 If 4 rows selected and last EMPNUM = 'E1'? |
| |
| -- END TEST >>> 0003 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0004 SELECT with UNION, ORDER BY integer DESC! |
| |
| SELECT WORKS.EMPNUM |
| FROM WORKS |
| WHERE WORKS.PNUM = 'P2' |
| UNION |
| SELECT STAFF.EMPNUM |
| FROM STAFF |
| WHERE STAFF.GRADE=13 |
| ORDER BY 1 DESC; |
| EM& |
| --- |
| E5 |
| E4 |
| E3 |
| E2 |
| E1 |
| ij> -- PASS:0004 If 5 rows selected and last EMPNUM = 'E1'? |
| |
| -- END TEST >>> 0004 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0005 SELECT with UNION ALL! |
| |
| SELECT WORKS.EMPNUM |
| FROM WORKS |
| WHERE WORKS.PNUM = 'P2' |
| UNION ALL |
| SELECT STAFF.EMPNUM |
| FROM STAFF |
| WHERE STAFF.GRADE = 13; |
| EM& |
| --- |
| E1 |
| E2 |
| E3 |
| E4 |
| E3 |
| E5 |
| ij> -- PASS:0005 If 6 rows selected? |
| |
| -- END TEST >>> 0005 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0158 SELECT with UNION and NOT EXISTS subquery! |
| |
| SELECT EMPNAME,PNUM,HOURS |
| FROM STAFF,WORKS |
| WHERE STAFF.EMPNUM = WORKS.EMPNUM |
| UNION |
| SELECT EMPNAME,PNUM,HOURS |
| FROM STAFF,WORKS |
| WHERE NOT EXISTS |
| (SELECT HOURS |
| FROM WORKS |
| WHERE STAFF.EMPNUM = WORKS.EMPNUM); |
| EMPNAME |PN&|HOURS |
| ------------------------------- |
| Alice |P1 |40 |
| Alice |P2 |20 |
| Alice |P3 |80 |
| Alice |P4 |20 |
| Alice |P5 |12 |
| Alice |P6 |12 |
| Betty |P1 |40 |
| Betty |P2 |80 |
| Carmen |P2 |20 |
| Don |P2 |20 |
| Don |P4 |40 |
| Don |P5 |80 |
| Ed |P1 |40 |
| Ed |P2 |20 |
| Ed |P2 |80 |
| Ed |P3 |80 |
| Ed |P4 |20 |
| Ed |P4 |40 |
| Ed |P5 |12 |
| Ed |P5 |80 |
| Ed |P6 |12 |
| ij> -- PASS:0158 If 21 rows selected? |
| |
| -- END TEST >>> 0158 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0159 SELECT with 2 UNIONs, ORDER BY 2 integers! |
| |
| SELECT PNUM,EMPNUM,HOURS |
| FROM WORKS |
| WHERE HOURS=80 |
| UNION |
| SELECT PNUM,EMPNUM,HOURS |
| FROM WORKS |
| WHERE HOURS=40 |
| UNION |
| SELECT PNUM,EMPNUM,HOURS |
| FROM WORKS |
| WHERE HOURS=20 |
| ORDER BY 3,1; |
| PN&|EM&|HOURS |
| -------------- |
| P2 |E4 |20 |
| P2 |E3 |20 |
| P2 |E1 |20 |
| P4 |E1 |20 |
| P1 |E2 |40 |
| P1 |E1 |40 |
| P4 |E4 |40 |
| P2 |E2 |80 |
| P3 |E1 |80 |
| P5 |E4 |80 |
| ij> -- PASS:0159 If 10 rows selected? |
| |
| -- END TEST >>> 0159 <<< END TEST |
| -- ********************************************* |
| |
| -- TEST:0160 SELECT with parenthesized UNION, UNION ALL! |
| |
| SELECT PNUM,EMPNUM,HOURS |
| FROM WORKS |
| WHERE HOURS=12 |
| UNION ALL |
| (SELECT PNUM,EMPNUM,HOURS |
| FROM WORKS |
| UNION |
| SELECT PNUM,EMPNUM,HOURS |
| FROM WORKS |
| WHERE HOURS=80) |
| ORDER BY 2,1; |
| PN&|EM&|HOURS |
| -------------- |
| P1 |E1 |40 |
| P2 |E1 |20 |
| P3 |E1 |80 |
| P4 |E1 |20 |
| P5 |E1 |12 |
| P5 |E1 |12 |
| P6 |E1 |12 |
| P6 |E1 |12 |
| P1 |E2 |40 |
| P2 |E2 |80 |
| P2 |E3 |20 |
| P2 |E4 |20 |
| P4 |E4 |40 |
| P5 |E4 |80 |
| ij> -- PASS:0160 If 14 rows selected? |
| |
| -- END TEST >>> 0160 <<< END TEST |
| |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |