| ij> -- MODULE DML020 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml020.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:0080 Simple two-table join! |
| SELECT EMPNUM,EMPNAME,GRADE,STAFF.CITY, PNAME, PROJ.CITY |
| FROM STAFF, PROJ |
| WHERE STAFF.CITY = PROJ.CITY |
| ORDER BY EMPNUM, EMPNAME, GRADE, STAFF.CITY, PNAME; |
| EM&|EMPNAME |GRADE|CITY |PNAME |CITY |
| ----------------------------------------------------------------------------------- |
| E1 |Alice |12 |Deale |MXSS |Deale |
| E1 |Alice |12 |Deale |PAYR |Deale |
| E1 |Alice |12 |Deale |SDP |Deale |
| E2 |Betty |10 |Vienna |CALM |Vienna |
| E2 |Betty |10 |Vienna |IRM |Vienna |
| E3 |Carmen |13 |Vienna |CALM |Vienna |
| E3 |Carmen |13 |Vienna |IRM |Vienna |
| E4 |Don |12 |Deale |MXSS |Deale |
| E4 |Don |12 |Deale |PAYR |Deale |
| E4 |Don |12 |Deale |SDP |Deale |
| ij> -- PASS:0080 If 10 rows are selected with EMPNAMEs:'Alice', 'Betty', ? |
| -- PASS:0080 'Carmen', and 'Don' but not 'Ed'? |
| |
| -- END TEST >>> 0080 <<< END TEST |
| -- ************************************************************** |
| |
| -- TEST:0081 Simple two-table join with filter! |
| -- |
| -- Added ORDER BY clause to get stable results across platforms - Jeff |
| SELECT EMPNUM,EMPNAME,GRADE,STAFF.CITY,PNUM,PNAME, |
| PTYPE,BUDGET,PROJ.CITY |
| FROM STAFF, PROJ |
| WHERE STAFF.CITY = PROJ.CITY |
| AND GRADE <> 12 |
| ORDER BY EMPNUM, EMPNAME, GRADE, STAFF.CITY, PNUM, PNAME; |
| EM&|EMPNAME |GRADE|CITY |PN&|PNAME |PTYPE |BUDGET |CITY |
| --------------------------------------------------------------------------------------------------------- |
| E2 |Betty |10 |Vienna |P2 |CALM |Code |30000 |Vienna |
| E2 |Betty |10 |Vienna |P5 |IRM |Test |10000 |Vienna |
| E3 |Carmen |13 |Vienna |P2 |CALM |Code |30000 |Vienna |
| E3 |Carmen |13 |Vienna |P5 |IRM |Test |10000 |Vienna |
| ij> -- PASS:0081 If 4 rows selected with EMPNAMEs 'Betty' and 'Carmen' ? |
| |
| -- END TEST >>> 0081 <<< END TEST |
| -- ************************************************************** |
| |
| -- TEST:0082 Join 3 tables! |
| SELECT DISTINCT STAFF.CITY, PROJ.CITY |
| FROM STAFF, WORKS, PROJ |
| WHERE STAFF.EMPNUM = WORKS.EMPNUM |
| AND WORKS.PNUM = PROJ.PNUM |
| ORDER BY STAFF.CITY, PROJ.CITY; |
| CITY |CITY |
| ------------------------------- |
| Deale |Deale |
| Deale |Tampa |
| Deale |Vienna |
| Vienna |Deale |
| Vienna |Vienna |
| ij> -- PASS:0082 If 5 distinct rows are selected ? |
| |
| -- END TEST >>> 0082 <<< END TEST |
| -- ************************************************************ |
| |
| -- TEST:0083 Join a table with itself! |
| SELECT FIRST1.EMPNUM, SECOND2.EMPNUM |
| FROM STAFF FIRST1, STAFF SECOND2 |
| WHERE FIRST1.CITY = SECOND2.CITY |
| AND FIRST1.EMPNUM < SECOND2.EMPNUM |
| ORDER BY FIRST1.EMPNUM, SECOND2.EMPNUM; |
| EM&|EM& |
| ------- |
| E1 |E4 |
| E2 |E3 |
| ij> -- PASS:0083 If 2 rows are selected and ? |
| -- PASS:0083 If EMPNUM pairs are 'E1'/'E4' and 'E2'/'E3'? |
| |
| -- END TEST >>> 0083 <<< END TEST |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |
