dml148.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML148  
-- SQL Test Suite, V6.0, Interactive SQL, dml148.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
   ROLLBACK WORK;
ij> -- date_time print
-- TEST:0843 Ordering of column names in joins !
-- REFERENCE:  7.5 sr 6 f
-- NOTE:0843 ordering of column names in NATURAL JOIN
   SELECT * 
--O     FROM HU.WORKS NATURAL LEFT JOIN HU.PROJ
     FROM HU.WORKS JOIN HU.PROJ on (HU.WORKS.pnum=HU.PROJ.pnum)
--O     FROM WORKS , PROJ where works.pnum = proj.pnum
     ORDER BY EMPNUM DESC, HU.PROJ.PNUM;
EM&|PN&|HOURS |PN&|PNAME               |PTYPE |BUDGET    |CITY           
-------------------------------------------------------------------------
E4 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E4 |P4 |40    |P4 |SDP                 |Design|20000     |Deale          
E4 |P5 |80    |P5 |IRM                 |Test  |10000     |Vienna         
E3 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E2 |P1 |40    |P1 |MXSS                |Design|10000     |Deale          
E2 |P2 |80    |P2 |CALM                |Code  |30000     |Vienna         
E1 |P1 |40    |P1 |MXSS                |Design|10000     |Deale          
E1 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E1 |P3 |80    |P3 |SDP                 |Test  |30000     |Tampa          
E1 |P4 |20    |P4 |SDP                 |Design|20000     |Deale          
E1 |P5 |12    |P5 |IRM                 |Test  |10000     |Vienna         
E1 |P6 |12    |P6 |PAYR                |Design|50000     |Deale          
ij> -- PASS:0843 If 12 rows selected?
-- PASS:0843 If ordered row and column values for first two rows are: ?
-- PASS:0843    P2  E4  20  CALM  Code    30000  Vienna ?
-- PASS:0843    P4  E4  40  SDP   Design  20000  Deale  ?
-- NOTE:0843 ordering of column names in JOIN ... ON
   SELECT * 
    FROM HU.WORKS JOIN HU.PROJ ON (HU.WORKS.PNUM=HU.PROJ.PNUM)
    ORDER BY EMPNUM DESC, HU.PROJ.PNUM;
EM&|PN&|HOURS |PN&|PNAME               |PTYPE |BUDGET    |CITY           
-------------------------------------------------------------------------
E4 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E4 |P4 |40    |P4 |SDP                 |Design|20000     |Deale          
E4 |P5 |80    |P5 |IRM                 |Test  |10000     |Vienna         
E3 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E2 |P1 |40    |P1 |MXSS                |Design|10000     |Deale          
E2 |P2 |80    |P2 |CALM                |Code  |30000     |Vienna         
E1 |P1 |40    |P1 |MXSS                |Design|10000     |Deale          
E1 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E1 |P3 |80    |P3 |SDP                 |Test  |30000     |Tampa          
E1 |P4 |20    |P4 |SDP                 |Design|20000     |Deale          
E1 |P5 |12    |P5 |IRM                 |Test  |10000     |Vienna         
E1 |P6 |12    |P6 |PAYR                |Design|50000     |Deale          
ij> -- PASS:0843 If 12 rows selected?
-- PASS:0843 If ordered row and column values for first two rows are: ?
-- PASS:0843    P2  E4  20  CALM  Code    30000  Vienna ?
-- PASS:0843    P4  E4  40  SDP   Design  20000  Deale  ?
-- NOTE:0843 Same answer as above
-- NOTE:0843 ordering of column names in NATURAL JOIN
-- REFERENCE:  7.5 sr 5
    SELECT * 
--O      FROM HU.WORKS RIGHT JOIN HU.PROJ
--O      ON HU.WORKS.PNUM = HU.PROJ.PNUM
      FROM HU.WORKS JOIN HU.PROJ
      ON HU.PROJ.PNUM = HU.WORKS.PNUM
      ORDER BY 1 DESC, 2;
EM&|PN&|HOURS |PN&|PNAME               |PTYPE |BUDGET    |CITY           
-------------------------------------------------------------------------
E4 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E4 |P4 |40    |P4 |SDP                 |Design|20000     |Deale          
E4 |P5 |80    |P5 |IRM                 |Test  |10000     |Vienna         
E3 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E2 |P1 |40    |P1 |MXSS                |Design|10000     |Deale          
E2 |P2 |80    |P2 |CALM                |Code  |30000     |Vienna         
E1 |P1 |40    |P1 |MXSS                |Design|10000     |Deale          
E1 |P2 |20    |P2 |CALM                |Code  |30000     |Vienna         
E1 |P3 |80    |P3 |SDP                 |Test  |30000     |Tampa          
E1 |P4 |20    |P4 |SDP                 |Design|20000     |Deale          
E1 |P5 |12    |P5 |IRM                 |Test  |10000     |Vienna         
E1 |P6 |12    |P6 |PAYR                |Design|50000     |Deale          
ij> -- PASS:0843 If 12 rows selected?
-- PASS:0843 If ordered row and column values for first two rows are: ?
-- PASS:0843    E4  P2  20  P2  CALM  Code    30000  Vienna ?
-- PASS:0843    E4  P4  40  P4  SDP   Design  20000  Deale  ?
   ROLLBACK WORK;
ij> -- END TEST >>> 0843 <<< END TEST
-- *********************************************
-- TEST:0844 Outer join predicates !
   CREATE TABLE SEVEN_TYPES (
       T_INT     INTEGER,
       T_CHAR    CHAR(10),
       T_SMALL   SMALLINT,
       T_DECIMAL DECIMAL(10,2),
       T_REAL    REAL,
       T_FLOAT   FLOAT,
       T_DOUBLE  DOUBLE PRECISION);
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- setup
   DELETE FROM SEVEN_TYPES;
0 rows inserted/updated/deleted
ij> INSERT INTO SEVEN_TYPES VALUES (1, 'E1',-11,   2,  3,   4,   5);
1 row inserted/updated/deleted
ij> INSERT INTO SEVEN_TYPES VALUES (2, 'E2', -5,  13, 33,-444, -55);
1 row inserted/updated/deleted
ij> INSERT INTO SEVEN_TYPES VALUES (3, 'E6', -3,-222,333,  44, 555);
1 row inserted/updated/deleted
ij> INSERT INTO SEVEN_TYPES VALUES (12,'DUP', 0,   0, -1,   1,1E+1);
1 row inserted/updated/deleted
ij> INSERT INTO SEVEN_TYPES VALUES (12,'DUP', 0,   0, -1,   1,1E+1);
1 row inserted/updated/deleted
ij> --
-- NOTE:0844 BETWEEN predicate
   SELECT EMPNAME, CITY, T_DECIMAL
     FROM HU.STAFF LEFT OUTER JOIN SEVEN_TYPES 
        ON -GRADE / 11 BETWEEN T_REAL AND T_DECIMAL
     ORDER BY EMPNAME;
EMPNAME             |CITY           |T_DECIMAL    
--------------------------------------------------
Alice               |Deale          |NULL         
Betty               |Vienna         |0.00         
Betty               |Vienna         |0.00         
Carmen              |Vienna         |NULL         
Don                 |Deale          |NULL         
Ed                  |Akron          |NULL         
ij> -- PASS:0844 If 6 rows selected with ordered rows and column values ?
-- PASS:0844    Alice  Deale  NULL  ?
-- PASS:0844    Betty  Vienna    0  ?
-- PASS:0844    Betty  Vienna    0  ?
-- PASS:0844    Carmen Vienna NULL  ?
-- PASS:0844    Don    Deale  NULL  ?
-- PASS:0844    Ed     Akron  NULL  ?
-- NOTE:0844 comparable CHAR types
-- NOTE:0844 IN predicate, with literals and variable value
   SELECT T_INT, T_CHAR, EMPNAME, EMPNUM, GRADE 
--O     FROM SEVEN_TYPES RIGHT JOIN HU.STAFF
    FROM SEVEN_TYPES right outer JOIN HU.STAFF
       ON GRADE IN (10, 11, 13) AND EMPNUM = T_CHAR
   ORDER BY EMPNAME, T_INT;
T_INT      |T_CHAR    |EMPNAME             |EMP&|GRADE
------------------------------------------------------
NULL       |NULL      |Alice               |E1  |12   
2          |E2        |Betty               |E2  |10   
NULL       |NULL      |Carmen              |E3  |13   
NULL       |NULL      |Don                 |E4  |12   
NULL       |NULL      |Ed                  |E5  |13   
ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ?
-- PASS:0844    NULL NULL Alice  E1 12  ?
-- PASS:0844       2 E2   Betty  E2 10  ?
-- PASS:0844    NULL NULL Carmen E3 13  ?
-- PASS:0844    NULL NULL Don    E4 12  ?
-- PASS:0844    NULL NULL Ed     E5 13  ?
   SELECT HU.STAFF.CITY,EMPNAME,PNAME,BUDGET
--O     FROM HU.STAFF LEFT JOIN HU.PROJ
     FROM HU.STAFF left outer JOIN HU.PROJ
       ON HU.STAFF.CITY = HU.PROJ.CITY
      AND HU.STAFF.CITY <> 'Vienna'
      AND EMPNAME <> 'Don'
     WHERE BUDGET > 15000 OR BUDGET IS NULL
--O   ORDER BY HU.STAFF.CITY, EMPNAME, BUDGET;
   ORDER BY 1,2,4;
CITY           |EMPNAME             |PNAME               |BUDGET    
--------------------------------------------------------------------
Akron          |Ed                  |NULL                |NULL      
Deale          |Alice               |SDP                 |20000     
Deale          |Alice               |PAYR                |50000     
Deale          |Don                 |NULL                |NULL      
Vienna         |Betty               |NULL                |NULL      
Vienna         |Carmen              |NULL                |NULL      
ij> -- PASS:0844 If 6 rows selected with ordered rows and column values ?
-- PASS:0844    Akron   Ed     NULL NULL   ?
-- PASS:0844    Deale   Alice  SDP  20000  ?
-- PASS:0844    Deale   Alice  PAYR 50000  ?
-- PASS:0844    Deale   Don    NULL NULL   ?
-- PASS:0844    Vienna  Betty  NULL NULL   ?
-- PASS:0844    Vienna  Carmen NULL NULL   ?
-- NOTE:0844 difference between WHERE and ON
   SELECT HU.STAFF.CITY,EMPNAME,PNAME,BUDGET
--O     FROM HU.STAFF LEFT JOIN HU.PROJ
     FROM HU.STAFF left outer JOIN HU.PROJ
       ON HU.STAFF.CITY = HU.PROJ.CITY
      AND HU.STAFF.CITY <> 'Vienna'
     WHERE (BUDGET > 15000 OR BUDGET IS NULL)
      AND EMPNAME <> 'Don'
--O   ORDER BY HU.STAFF.CITY, EMPNAME, BUDGET;
   ORDER BY 1,2,4;
CITY           |EMPNAME             |PNAME               |BUDGET    
--------------------------------------------------------------------
Akron          |Ed                  |NULL                |NULL      
Deale          |Alice               |SDP                 |20000     
Deale          |Alice               |PAYR                |50000     
Vienna         |Betty               |NULL                |NULL      
Vienna         |Carmen              |NULL                |NULL      
ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ?
-- PASS:0844    Akron   Ed     NULL NULL   ?
-- PASS:0844    Deale   Alice  SDP  20000  ?
-- PASS:0844    Deale   Alice  PAYR 50000  ?
-- PASS:0844    Vienna  Betty  NULL NULL   ?
-- PASS:0844    Vienna  Carmen NULL NULL   ?
-- NOTE:0844 correlation name with self-JOIN
   SELECT XX.T_INT, YY.T_INT
    FROM SEVEN_TYPES XX RIGHT OUTER JOIN SEVEN_TYPES YY
       ON XX.T_INT = YY.T_INT +1
--O   ORDER BY YY.T_INT;
   ORDER BY 2;
T_INT      |T_INT      
-----------------------
2          |1          
3          |2          
NULL       |3          
NULL       |12         
NULL       |12         
ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ?
-- PASS:0844    2      1  ?
-- PASS:0844    3      2  ?
-- PASS:0844    NULL   3  ?
-- PASS:0844    NULL  12  ?
-- PASS:0844    NULL  12  ?
-- NOTE:0844 nested booleans
-- NOTE:0844 data types are merely comparable
   SELECT GRADE, T_FLOAT, T_DOUBLE
--O     FROM HU.STAFF LEFT JOIN SEVEN_TYPES T7
     FROM HU.STAFF left outer JOIN SEVEN_TYPES T7
       ON GRADE * -40 > T7.T_FLOAT
       OR (T_DOUBLE -542.5 < GRADE AND T_DOUBLE -541.5 > GRADE)
   ORDER BY GRADE;
GRADE|T_FLOAT               |T_DOUBLE              
---------------------------------------------------
10   |-444.0                |-55.0                 
12   |NULL                  |NULL                  
12   |NULL                  |NULL                  
13   |44.0                  |555.0                 
13   |44.0                  |555.0                 
ij> -- PASS:0844 If 5 rows selected with ordered rows and column values ?
-- PASS:0844    10 -444 (approximately)  -55 (approximately) ?
-- PASS:0844    12 NULL                 NULL                 ?
-- PASS:0844    12 NULL                 NULL                 ?
-- PASS:0844    13   44 (approximately)  555 (approximately) ?
-- PASS:0844    13   44 (approximately)  555 (approximately) ?
ROLLBACK WORK;
ij> --ODROP TABLE SEVEN_TYPES CASCADE;
DROP TABLE SEVEN_TYPES ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0844 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>