ij> AUTOCOMMIT OFF;
ij> -- MODULE DML073

-- SQL Test Suite, V6.0, Interactive SQL, dml073.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION HU 
   set schema HU;
0 rows inserted/updated/deleted
ij> --0   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
HU                                                                                                                              
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print

-- TEST:0393 SUM, MAX on Cartesian product!

     SELECT SUM(HOURS), MAX(HOURS)
           FROM  STAFF, WORKS;
1          |2     
------------------
2320       |80    
ij> -- PASS:0393 If SUM(HOURS) = 2320 and MAX(HOURS) = 80?

-- END TEST >>> 0393 <<< END TEST
-- *************************************************************

-- TEST:0394 AVG, MIN on joined table with WHERE without GROUP!

     SELECT AVG(HOURS), MIN(HOURS)
           FROM  STAFF, WORKS
           WHERE STAFF.EMPNUM = 'E2'
                 AND STAFF.EMPNUM = WORKS.EMPNUM;
1            |2     
--------------------
60.0000      |40    
ij> -- PASS:0394 If AVG(HOURS) = 60 and MIN(HOURS) = 40?

-- END TEST >>> 0394 <<< END TEST
-- *************************************************************

-- TEST:0395 SUM, MIN on joined table with GROUP without WHERE!

     SELECT STAFF.EMPNUM, SUM(HOURS), MIN(HOURS)
           FROM  STAFF, WORKS
           GROUP BY STAFF.EMPNUM
           ORDER BY 1;
EM&|2          |3     
----------------------
E1 |464        |12    
E2 |464        |12    
E3 |464        |12    
E4 |464        |12    
E5 |464        |12    
ij> -- PASS:0395 If 5 rows are selected with the following order?
-- PASS:0395 STAFF.EMPNUM  SUM(HOURS)  MIN(HOURS)?
-- PASS:0395    'E1'         464          12?
-- PASS:0395    'E2'         464          12?
-- PASS:0395    'E3'         464          12?
-- PASS:0395    'E4'         464          12?
-- PASS:0395    'E5'         464          12?

-- END TEST >>> 0395 <<< END TEST
-- *************************************************************

-- TEST:0396 SUM, MIN on joined table with WHERE, GROUP BY, HAVING!
 
     SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS)
           FROM  STAFF, WORKS
           WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND
                 STAFF.EMPNUM = WORKS.EMPNUM
                 GROUP BY STAFF.EMPNUM
                 HAVING COUNT(*) > 1
--0                 ORDER BY STAFF.EMPNUM;
                 ORDER BY EMPNUM;
EM&|2            |3     
------------------------
E1 |30.6666      |12    
E4 |46.6666      |20    
ij> -- PASS:0396 If 2 rows are selected with the following order?
-- PASS:0396 STAFF.EMPNUM   AVG(HOURS)  MIN(HOURS)?
-- PASS:0396     'E1'        30 to 31      12?
-- PASS:0396     'E4'        46 to 47      20?

-- END TEST >>> 0396 <<< END TEST
-- *************************************************************

-- TEST:0417 Cartesian product GROUP BY 2 columns with NULLs!

     DELETE FROM STAFF1;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> -- Making sure the table is empty

-- setup          
     INSERT INTO STAFF VALUES ('E6', 'David', 17, NULL);
1 row inserted/updated/deleted
ij> INSERT INTO STAFF VALUES ('E7', 'Tony', 18, NULL);
1 row inserted/updated/deleted
ij> INSERT INTO STAFF1 SELECT * FROM STAFF;
7 rows inserted/updated/deleted
ij> SELECT MAX(STAFF1.GRADE), SUM(STAFF1.GRADE)
           FROM STAFF1, STAFF
           GROUP BY STAFF1.CITY, STAFF.CITY;
1    |2        
---------------
13   |13       
13   |26       
13   |26       
13   |26       
12   |24       
12   |48       
12   |48       
12   |48       
13   |23       
13   |46       
13   |46       
13   |46       
18   |35       
18   |70       
18   |70       
18   |70       
ij> -- PASS:0417 If 16 rows are selected in any order?
-- PASS:0417 Including the following four rows? 
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 35?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 70?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 70?
-- PASS:0417 MAX(STAFF1.GRADE) = 18 and SUM(STAFF1.GRADE) = 70?

-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0417 <<< END TEST
-- *************************************************************

-- TEST:0418 AVG, SUM, COUNT on Cartesian product with NULL!

     SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4),
           SUM(T2.COL4), COUNT(DISTINCT T1.COL4)
           FROM VTABLE T1, VTABLE T2;
1          |2          |3          |4          
-----------------------------------------------
147        |295        |1772       |3          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0418 If AVG(T1.COL4) = 147 or 148? 
-- PASS:0418 If AVG(T1.COL4 + T2.COL4) = 295 or 296?
-- PASS:0418 If SUM(T2.COL4) = 1772?
-- PASS:0418 If COUNT(DISTINCT T1.COL4) = 3?

-- END TEST >>> 0418 <<< END TEST
-- *************************************************************

-- TEST:0419 SUM, MAX, MIN on joined table view!

     SELECT SUM(COST), MAX(COST), MIN(COST)
           FROM STAFF_WORKS_DESIGN;
1                               |2                   |3                   
--------------------------------------------------------------------------
3488                            |960                 |288                 
ij> -- PASS:0419 If SUM(COST) = 3488, MAX(COST) = 960, MIN(COST) = 288?

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