java/testing/org/apache/derbyTesting/functionTests/master/dml075.out

ij> AUTOCOMMIT OFF;
ij> -- MODULE DML075

-- SQL Test Suite, V6.0, Interactive SQL, dml075.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:0431 Redundant rows in IN subquery!

--O   SELECT COUNT (*) FROM STAFF
   SELECT * FROM STAFF
       WHERE EMPNUM IN
            (SELECT EMPNUM FROM WORKS);
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E4 |Don                 |12   |Deale          
ij> -- PASS:0431 If count = 4?

   INSERT INTO STAFF1
       SELECT * FROM STAFF;
5 rows inserted/updated/deleted
ij> --O   SELECT COUNT (*) FROM STAFF1
   SELECT * FROM STAFF1
       WHERE EMPNUM IN
            (SELECT EMPNUM FROM WORKS);
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E4 |Don                 |12   |Deale          
ij> -- PASS:0431 If count = 4?

   ROLLBACK WORK;
ij> -- END TEST >>> 0431 <<< END TEST
-- *************************************************************

-- TEST:0432 Unknown comparison predicate in ALL, SOME, ANY!

-- setup

UPDATE PROJ SET CITY = NULL 
  WHERE PNUM = 'P3';
1 row inserted/updated/deleted
ij> --OSELECT COUNT(*)
SELECT *
  FROM STAFF
  WHERE CITY = ALL (SELECT CITY
                   FROM PROJ
                   WHERE PNAME = 'SDP');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
ij> -- PASS:0432 If count = 0?

--OSELECT COUNT(*)
SELECT *
  FROM STAFF
  WHERE CITY <> ALL (SELECT CITY 
                    FROM PROJ
                    WHERE PNAME = 'SDP');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
ij> -- PASS:0432 If count = 0?

--OSELECT COUNT(*)
SELECT *
  FROM STAFF
  WHERE CITY = ANY (SELECT CITY    
                   FROM PROJ
                   WHERE PNAME = 'SDP');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E4 |Don                 |12   |Deale          
ij> -- PASS:0432 If count = 2?

--OSELECT COUNT(*)
SELECT *
  FROM STAFF
  WHERE CITY <> ANY (SELECT CITY
                     FROM PROJ
                     WHERE PNAME = 'SDP');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E5 |Ed                  |13   |Akron          
ij> -- PASS:0432 If count = 3?

--OSELECT COUNT(*)
SELECT *
  FROM STAFF
  WHERE CITY = SOME (SELECT CITY
                     FROM PROJ
                     WHERE PNAME = 'SDP');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E4 |Don                 |12   |Deale          
ij> -- PASS:0432 If count = 2?

--OSELECT COUNT(*)
SELECT *
  FROM STAFF
  WHERE CITY <> SOME (SELECT CITY
                      FROM PROJ
                      WHERE PNAME = 'SDP');
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E5 |Ed                  |13   |Akron          
ij> -- PASS:0432 If count = 3?

   ROLLBACK WORK;
ij> -- END TEST >>> 0432 <<< END TEST
-- *************************************************************

-- TEST:0433 Empty subquery in ALL, SOME, ANY!

--O   SELECT COUNT(*) FROM PROJ
   SELECT * FROM PROJ
        WHERE PNUM = ALL (SELECT PNUM
                          FROM WORKS WHERE EMPNUM = 'E8');
PN&|PNAME               |PTYPE |BUDGET    |CITY           
----------------------------------------------------------
P1 |MXSS                |Design|10000     |Deale          
P2 |CALM                |Code  |30000     |Vienna         
P3 |SDP                 |Test  |30000     |Tampa          
P4 |SDP                 |Design|20000     |Deale          
P5 |IRM                 |Test  |10000     |Vienna         
P6 |PAYR                |Design|50000     |Deale          
ij> -- PASS:0433 If count = 6?

--O   SELECT COUNT(*) FROM PROJ
   SELECT * FROM PROJ
        WHERE PNUM <> ALL (SELECT PNUM
                          FROM WORKS WHERE EMPNUM = 'E8');
PN&|PNAME               |PTYPE |BUDGET    |CITY           
----------------------------------------------------------
P1 |MXSS                |Design|10000     |Deale          
P2 |CALM                |Code  |30000     |Vienna         
P3 |SDP                 |Test  |30000     |Tampa          
P4 |SDP                 |Design|20000     |Deale          
P5 |IRM                 |Test  |10000     |Vienna         
P6 |PAYR                |Design|50000     |Deale          
ij> -- PASS:0433 If count = 6?

--O   SELECT COUNT(*) FROM PROJ
   SELECT * FROM PROJ
        WHERE PNUM = ANY (SELECT PNUM
                          FROM WORKS WHERE EMPNUM = 'E8');
PN&|PNAME               |PTYPE |BUDGET    |CITY           
----------------------------------------------------------
ij> -- PASS:0433 If count = 0?

--O   SELECT COUNT(*) FROM PROJ
   SELECT * FROM PROJ
        WHERE PNUM <> ANY (SELECT PNUM
                          FROM WORKS WHERE EMPNUM = 'E8');
PN&|PNAME               |PTYPE |BUDGET    |CITY           
----------------------------------------------------------
ij> -- PASS:0433 If count = 0?

--O   SELECT COUNT(*) FROM PROJ
   SELECT * FROM PROJ
        WHERE PNUM = SOME (SELECT PNUM
                          FROM WORKS WHERE EMPNUM = 'E8');
PN&|PNAME               |PTYPE |BUDGET    |CITY           
----------------------------------------------------------
ij> -- PASS:0433 If count = 0?

--O   SELECT COUNT(*) FROM PROJ
   SELECT * FROM PROJ
        WHERE PNUM <> SOME (SELECT PNUM
                          FROM WORKS WHERE EMPNUM = 'E8');
PN&|PNAME               |PTYPE |BUDGET    |CITY           
----------------------------------------------------------
ij> -- PASS:0433 If count = 0?

-- END TEST >>> 0433 <<< END TEST
-- *************************************************************

-- TEST:0434 GROUP BY with HAVING EXISTS-correlated set function!

   SELECT PNUM, SUM(HOURS) FROM WORKS c
          GROUP BY PNUM
--O          HAVING EXISTS (SELECT PNAME FROM PROJ
--O                         WHERE PROJ.PNUM = WORKS.PNUM AND
          HAVING EXISTS (SELECT PNAME FROM PROJ, works a
                         WHERE PROJ.PNUM = a.PNUM AND
--O                               SUM(WORKS.HOURS) > PROJ.BUDGET / 200);
                         PROJ.BUDGET / 200 < (select sum(hours) from works b
			  where a.pnum = b.pnum
			  and a.pnum = c.pnum));
PN&|2          
---------------
P1 |80         
P5 |92         
ij> -- PASS:0434 If 2 rows selected with values (in any order):?
-- PASS:0434 PNUM = 'P1', SUM(HOURS) = 80?
-- PASS:0434 PNUM = 'P5', SUM(HOURS) = 92?

-- END TEST >>> 0434 <<< END TEST
-- *************************************************************

-- TEST:0442 DISTINCT with GROUP BY, HAVING!

   SELECT PTYPE, CITY FROM PROJ
          GROUP BY PTYPE, CITY
          HAVING AVG(BUDGET) > 21000;
PTYPE |CITY           
----------------------
Code  |Vienna         
Design|Deale          
Test  |Tampa          
ij> -- PASS:0442 If 3 rows selected with PTYPE/CITY values(in any order):?
-- PASS:0442 Code/Vienna, Design/Deale, Test/Tampa?

   SELECT DISTINCT PTYPE, CITY FROM PROJ
          GROUP BY PTYPE, CITY
          HAVING AVG(BUDGET) > 21000;
PTYPE |CITY           
----------------------
Code  |Vienna         
Design|Deale          
Test  |Tampa          
ij> -- PASS:0442 If 3 rows selected with PTYPE/CITY values(in any order):?
-- PASS:0442 Code/Vienna, Design/Deale, Test/Tampa?

   SELECT DISTINCT SUM(BUDGET) FROM PROJ
          GROUP BY PTYPE, CITY
          HAVING AVG(BUDGET) > 21000;
1                  
-------------------
30000              
80000              
ij> -- PASS:0442 If 2 rows selected (in any order):?
-- PASS:0442 with SUM(BUDGET) values 30000 and 80000?

-- END TEST >>> 0442 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>