ij> AUTOCOMMIT OFF;
ij> -- MODULE DML013

-- SQL Test Suite, V6.0, Interactive SQL, dml013.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:0039 COUNT DISTINCT function!

-- setup
     INSERT INTO WORKS
            VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0039 If 1 row inserted?

     SELECT COUNT(DISTINCT HOURS)
          FROM WORKS;
1          
-----------
4          
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0039 If count = 4?

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

-- TEST:0167 SUM ALL function!

-- setup
     INSERT INTO WORKS
            VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0167 If 1 row is inserted?

     SELECT SUM(ALL HOURS)
          FROM WORKS;
1          
-----------
464        
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0167 If SUM(ALL HOURS) = 464?

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

-- TEST:0168 SUM function!

-- setup
     INSERT INTO WORKS
            VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0168 If 1 row is inserted?

     SELECT SUM(HOURS)
          FROM WORKS;
1          
-----------
464        
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0168 If SUM(HOURS) = 464?

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

-- TEST:0169 COUNT(*) function !

-- setup
     INSERT INTO WORKS
            VALUES('E5','P5',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0169 If 1 row is inserted?

     SELECT COUNT(*)
          FROM WORKS;
1          
-----------
13         
ij> -- PASS:0169 If count = 13?

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

-- TEST:0040 SUM function with WHERE clause!
     SELECT SUM(HOURS)
          FROM WORKS
          WHERE PNUM = 'P2';
1          
-----------
140        
ij> -- PASS:0040 If SUM(HOURS) = 140?

-- END TEST >>> 0040 <<< END TEST
-- ***************************************************************

-- TEST:0170 SUM DISTINCT function with WHERE clause!
     SELECT SUM(DISTINCT HOURS)
          FROM WORKS
          WHERE PNUM = 'P2';
1          
-----------
100        
ij> -- PASS:0170 If SUM(DISTINCT HOURS) = 100?

-- END TEST >>> 0170 <<< END TEST
-- **************************************************************

-- TEST:0171 SUM(column) + value!
     SELECT SUM(HOURS)+10
          FROM WORKS
          WHERE PNUM = 'P2';
1           
------------
150         
ij> -- PASS:0171 If SUM(HOURS)+10 = 150?

-- END TEST >>> 0171 <<< END TEST
-- ***************************************************************

-- TEST:0041 MAX function in subquery!
     SELECT EMPNUM
          FROM STAFF
          WHERE GRADE = (SELECT MAX(GRADE) FROM STAFF)
          ORDER BY EMPNUM;
EM&
---
E3 
E5 
ij> -- PASS:0041 If 2 rows are selected and EMPNUMs = 'E3' and 'E5'?

-- END TEST >>> 0041 <<< END TEST
-- ***************************************************************

-- TEST:0042 MIN function in subquery!
     SELECT EMPNUM
          FROM STAFF
          WHERE GRADE =
               (SELECT MIN(GRADE) FROM STAFF);
EM&
---
E2 
ij> -- PASS:0042 If EMPNUM = 'E2'?

-- END TEST >>> 0042 <<< END TEST
-- ***************************************************************

-- TEST:0043 AVG function!
     SELECT AVG(GRADE)
          FROM STAFF;
1           
------------
12.0000     
ij> -- PASS:0043 If AVG(GRADE) = 12?

-- END TEST >>> 0043 <<< END TEST
-- ***************************************************************

-- TEST:0044 AVG function - empty result NULL value!
     DELETE FROM TEMP_S;
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> SELECT AVG(GRADE)
         FROM   TEMP_S;
1           
------------
NULL        
ij> -- PASS:0044 If AVG(GRADE) is NULL?

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