ij> AUTOCOMMIT OFF;
ij> -- MODULE DML001

-- SQL Test Suite, V6.0, Interactive SQL, dml001.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:0001 SELECT with ORDER BY DESC!

                SELECT EMPNUM,HOURS
                     FROM WORKS
                     WHERE PNUM='P2'
                     ORDER BY EMPNUM DESC;
EM&|HOURS 
----------
E4 |20    
E3 |20    
E2 |80    
E1 |20    
ij> -- PASS:0001 If 4 rows selected and last EMPNUM = 'E1'?

-- END TEST >>> 0001 <<< END TEST
-- *********************************************

-- TEST:0002 SELECT with ORDER BY integer ASC!

                SELECT EMPNUM,HOURS
                     FROM WORKS
                     WHERE PNUM='P2'
                     ORDER BY 2 ASC;
EM&|HOURS 
----------
E4 |20    
E3 |20    
E1 |20    
E2 |80    
ij> -- PASS:0002 If 4 rows selected and last HOURS = 80?

-- END TEST >>> 0002 <<< END TEST
-- *********************************************

-- TEST:0003 SELECT with ORDER BY DESC integer, named column!

                SELECT EMPNUM,HOURS
                     FROM WORKS
                     WHERE PNUM = 'P2'
                     ORDER BY 2 DESC,EMPNUM DESC;
EM&|HOURS 
----------
E2 |80    
E4 |20    
E3 |20    
E1 |20    
ij> -- PASS:0003 If 4 rows selected and last EMPNUM = 'E1'?

-- END TEST >>> 0003 <<< END TEST
-- *********************************************

-- TEST:0004 SELECT with UNION, ORDER BY integer DESC!

                SELECT WORKS.EMPNUM 
                     FROM WORKS
                     WHERE WORKS.PNUM = 'P2'
            UNION
                SELECT STAFF.EMPNUM  
                     FROM STAFF
                     WHERE STAFF.GRADE=13 
                     ORDER BY 1 DESC;
EM&
---
E5 
E4 
E3 
E2 
E1 
ij> -- PASS:0004 If 5 rows selected and last EMPNUM = 'E1'?

-- END TEST >>> 0004 <<< END TEST
-- *********************************************

-- TEST:0005 SELECT with UNION ALL!

                SELECT WORKS.EMPNUM 
                     FROM WORKS
                     WHERE WORKS.PNUM = 'P2'
            UNION ALL    
                SELECT STAFF.EMPNUM  
                     FROM STAFF
                     WHERE STAFF.GRADE = 13;
EM&
---
E1 
E2 
E3 
E4 
E3 
E5 
ij> -- PASS:0005 If 6 rows selected?

-- END TEST >>> 0005 <<< END TEST
-- *********************************************

-- TEST:0158 SELECT with UNION and NOT EXISTS subquery!

                SELECT EMPNAME,PNUM,HOURS
                     FROM STAFF,WORKS
                     WHERE STAFF.EMPNUM = WORKS.EMPNUM
            UNION
                SELECT EMPNAME,PNUM,HOURS
                     FROM STAFF,WORKS
                     WHERE NOT EXISTS
                       (SELECT HOURS 
                             FROM WORKS
                             WHERE STAFF.EMPNUM = WORKS.EMPNUM);
EMPNAME             |PN&|HOURS 
-------------------------------
Alice               |P1 |40    
Alice               |P2 |20    
Alice               |P3 |80    
Alice               |P4 |20    
Alice               |P5 |12    
Alice               |P6 |12    
Betty               |P1 |40    
Betty               |P2 |80    
Carmen              |P2 |20    
Don                 |P2 |20    
Don                 |P4 |40    
Don                 |P5 |80    
Ed                  |P1 |40    
Ed                  |P2 |20    
Ed                  |P2 |80    
Ed                  |P3 |80    
Ed                  |P4 |20    
Ed                  |P4 |40    
Ed                  |P5 |12    
Ed                  |P5 |80    
Ed                  |P6 |12    
ij> -- PASS:0158 If 21 rows selected?

-- END TEST >>> 0158 <<< END TEST
-- *********************************************

-- TEST:0159 SELECT with 2 UNIONs, ORDER BY 2 integers!

             SELECT PNUM,EMPNUM,HOURS
                  FROM WORKS
                  WHERE HOURS=80
         UNION
             SELECT PNUM,EMPNUM,HOURS
                  FROM WORKS
                  WHERE HOURS=40
         UNION
             SELECT PNUM,EMPNUM,HOURS
                  FROM WORKS
                  WHERE HOURS=20
                  ORDER BY 3,1;
PN&|EM&|HOURS 
--------------
P2 |E4 |20    
P2 |E3 |20    
P2 |E1 |20    
P4 |E1 |20    
P1 |E2 |40    
P1 |E1 |40    
P4 |E4 |40    
P2 |E2 |80    
P3 |E1 |80    
P5 |E4 |80    
ij> -- PASS:0159 If 10 rows selected?

-- END TEST >>> 0159 <<< END TEST
-- *********************************************

-- TEST:0160 SELECT with parenthesized UNION, UNION ALL!

             SELECT PNUM,EMPNUM,HOURS
                  FROM WORKS
                  WHERE HOURS=12
             UNION ALL 
            (SELECT PNUM,EMPNUM,HOURS
                  FROM WORKS
             UNION
             SELECT PNUM,EMPNUM,HOURS
                  FROM WORKS
                  WHERE HOURS=80)
                  ORDER BY 2,1;
PN&|EM&|HOURS 
--------------
P1 |E1 |40    
P2 |E1 |20    
P3 |E1 |80    
P4 |E1 |20    
P5 |E1 |12    
P5 |E1 |12    
P6 |E1 |12    
P6 |E1 |12    
P1 |E2 |40    
P2 |E2 |80    
P2 |E3 |20    
P2 |E4 |20    
P4 |E4 |40    
P5 |E4 |80    
ij> -- PASS:0160 If 14 rows selected?

-- END TEST >>> 0160 <<< END TEST

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