ij> -- MODULE DML019

-- SQL Test Suite, V6.0, Interactive SQL, dml019.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:0074 GROUP BY col with SELECT col., SUM!
     SELECT PNUM, SUM(HOURS)                 
          FROM WORKS                              
          GROUP BY PNUM;
PN&|2          
---------------
P1 |80         
P2 |140        
P3 |80         
P4 |60         
P5 |92         
P6 |12         
ij> -- PASS:0074 If 6 rows are selected?
-- PASS:0074 If PNUMs: 'P1', 'P2', 'P3', 'P4', 'P5', 'P6'?
-- PASS:0074 If SUM(HOURS) for 'P2' is 140 ?

-- END TEST >>> 0074 <<< END TEST
-- **********************************************************

-- TEST:0075 GROUP BY clause!
     SELECT EMPNUM                           
          FROM WORKS                              
          GROUP BY EMPNUM
-- Derby change to standardize order for diff
	order by empnum;
EM&
---
E1 
E2 
E3 
E4 
ij> -- PASS:0075 If 4 rows are selected with EMPNUMs: 'E1','E2','E3','E4'?

-- END TEST >>> 0075 <<< END TEST
-- ************************************************************

-- TEST:0076 GROUP BY 2 columns!
     SELECT EMPNUM,HOURS                     
          FROM WORKS                              
          GROUP BY EMPNUM,HOURS
-- Derby change to standardize order for diff
	order by empnum, hours;
EM&|HOURS 
----------
E1 |12    
E1 |20    
E1 |40    
E1 |80    
E2 |40    
E2 |80    
E3 |20    
E4 |20    
E4 |40    
E4 |80    
ij> -- PASS:0076 If 10 rows are selected and EMPNUM = 'E1' in 4 rows ?
-- PASS:0076 for 1 row EMPNUM = 'E1' and HOURS = 12?

-- END TEST >>> 0076 <<< END TEST
-- ***********************************************************

-- TEST:0077 GROUP BY all columns with SELECT * !
     SELECT *                    
          FROM WORKS                              
          GROUP BY PNUM,EMPNUM,HOURS;
EM&|PN&|HOURS 
--------------
E1 |P1 |40    
E1 |P2 |20    
E1 |P3 |80    
E1 |P4 |20    
E1 |P5 |12    
E1 |P6 |12    
E2 |P1 |40    
E2 |P2 |80    
E3 |P2 |20    
E4 |P2 |20    
E4 |P4 |40    
E4 |P5 |80    
ij> -- PASS:0077 If 12 rows are selected ?

-- END TEST >>> 0077 <<< END TEST
-- ***********************************************************

-- TEST:0078 GROUP BY three columns, SELECT two!
     SELECT PNUM,EMPNUM                      
          FROM WORKS                              
          GROUP BY EMPNUM,PNUM,HOURS;
PN&|EM&
-------
P1 |E1 
P2 |E1 
P3 |E1 
P4 |E1 
P5 |E1 
P6 |E1 
P1 |E2 
P2 |E2 
P2 |E3 
P2 |E4 
P4 |E4 
P5 |E4 
ij> -- PASS:0078 If 12 rows are selected  ?

-- END TEST >>> 0078 <<< END TEST
-- *********************************************************

-- TEST:0079 GROUP BY NULL value!

-- setup
     INSERT INTO STAFF(EMPNUM,EMPNAME,GRADE)
            VALUES('E6','WANG',40);
1 row inserted/updated/deleted
ij> -- PASS:0079 If 1 row is inserted?

     INSERT INTO STAFF(EMPNUM,EMPNAME,GRADE)
            VALUES('E7','SONG',50);
1 row inserted/updated/deleted
ij> -- PASS:0079 If 1 row is inserted?
              
     SELECT SUM(GRADE)                       
          FROM STAFF                            
          WHERE CITY IS NULL                     
          GROUP BY CITY;
1        
---------
90       
ij> -- PASS:0079 If SUM(GRADE) = 90?

-- restore
   DELETE FROM STAFF WHERE CITY IS NULL;
2 rows inserted/updated/deleted
ij> -- PASS:0079 If 2 rows deleted?

--O   SELECT COUNT(*) FROM STAFF;
   SELECT * FROM STAFF;
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E4 |Don                 |12   |Deale          
E5 |Ed                  |13   |Akron          
ij> -- PASS:0079 If count = 5?

-- END TEST >>> 0079 <<< END TEST

-- *************************************************////END-OF-MODULE
;
ij>