dml058.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE DML058
-- SQL Test Suite, V6.0, Interactive SQL, dml058.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:0251 COMMIT keeps changes of current transaction!
     DELETE FROM STAFF1;
0 rows inserted/updated/deleted
ij> -- Making sure the table is empty
-- setup
     INSERT INTO STAFF1
            SELECT *
                 FROM STAFF;
5 rows inserted/updated/deleted
ij> -- PASS:0251 If 5 rows are inserted?
--O     SELECT COUNT(*) 
     SELECT * 
          FROM STAFF1;
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:0251 If count = 5?
     INSERT INTO STAFF1
            VALUES('E9','Tom',50,'London');
1 row inserted/updated/deleted
ij> -- PASS:0251 If 1 row is inserted?
     UPDATE STAFF1
            SET GRADE = 40 
            WHERE EMPNUM = 'E2';
1 row inserted/updated/deleted
ij> -- PASS:0251 If 1 row is updated?
     COMMIT WORK;
ij> DELETE FROM STAFF1;
6 rows inserted/updated/deleted
ij> -- PASS:0251 If 6 rows are deleted?
-- verify
     ROLLBACK WORK;
ij> -- verify previous commit
--O     SELECT COUNT(*)
     SELECT *
          FROM STAFF1
          WHERE GRADE > 12;
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E2 |Betty               |40   |Vienna         
E3 |Carmen              |13   |Vienna         
E5 |Ed                  |13   |Akron          
E9 |Tom                 |50   |London         
ij> -- PASS:0251 If count = 4?
-- restore
     DELETE FROM STAFF1;
6 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0251 <<< END TEST
-- ***************************************************************
-- TEST:0252 ROLLBACK cancels changes of current transaction!
     DELETE FROM STAFF1;
0 rows inserted/updated/deleted
ij> -- Making sure the table is empty
-- setup
     INSERT INTO STAFF1
            SELECT *
                 FROM STAFF;
5 rows inserted/updated/deleted
ij> -- PASS:0252 If 5 rows are inserted?
     COMMIT WORK;
ij> INSERT INTO STAFF1
            VALUES('E10','Tom',50,'London');
1 row inserted/updated/deleted
ij> -- PASS:0252 If 1 row is inserted?
     UPDATE STAFF1
            SET GRADE = 40
            WHERE EMPNUM = 'E1';
1 row inserted/updated/deleted
ij> -- PASS:0252 If 1 row is updated?
     DELETE FROM STAFF1
            WHERE EMPNUM = 'E2';
1 row inserted/updated/deleted
ij> -- PASS:0252 If 1 row is deleted?
     ROLLBACK WORK;
ij> -- verify     
     SELECT SUM(GRADE)
          FROM STAFF1;
1        
---------
60       
ij> -- PASS:0252 If SUM(GRADE) = 60?
-- restore
     DELETE FROM STAFF1;
5 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0252 <<< END TEST
-- ****************************************************************
-- TEST:0253 TEST0124 workaround (key = key+1)!
     SELECT NUMKEY
          FROM UPUNIQ
          ORDER BY NUMKEY DESC;
NUM&
----
8   
6   
4   
3   
2   
1   
ij> -- PASS:0253 If 6 rows are selected and first NUMKEY = 8 ?
     UPDATE UPUNIQ
          SET NUMKEY = 8 + 1
          WHERE NUMKEY = 8;
1 row inserted/updated/deleted
ij> -- PASS:0253 If 1 row is updated?
     UPDATE UPUNIQ
          SET NUMKEY = 6 + 1
          WHERE NUMKEY = 6;
1 row inserted/updated/deleted
ij> -- PASS:0253 If 1 row is updated?
     UPDATE UPUNIQ
          SET NUMKEY = 4 + 1
          WHERE NUMKEY = 4;
1 row inserted/updated/deleted
ij> -- PASS:0253 If 1 row is updated?
     UPDATE UPUNIQ
          SET NUMKEY = 3 + 1
          WHERE NUMKEY = 3;
1 row inserted/updated/deleted
ij> -- PASS:0253 If 1 row is updated?
     UPDATE UPUNIQ
          SET NUMKEY = 2 + 1 
          WHERE NUMKEY = 2;
1 row inserted/updated/deleted
ij> -- PASS:0253 If 1 row is updated?
     UPDATE UPUNIQ
          SET NUMKEY = 1 + 1
          WHERE NUMKEY = 1;
1 row inserted/updated/deleted
ij> -- PASS:0253 If 1 row is updated?
     SELECT MAX(NUMKEY), MIN(NUMKEY)
          FROM UPUNIQ;
1   |2   
---------
9   |2   
ij> -- PASS:0253 If MAX(NUMKEY) = 9 AND MIN(NUMKEY) = 2?
-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0253 <<< END TEST
-- **************************************************************
-- TEST:0254 Column name in SET clause!
     DELETE FROM PROJ1;
0 rows inserted/updated/deleted
ij> -- Making sure the table is empty
-- setup
     INSERT INTO PROJ1
            SELECT *
                 FROM PROJ;
6 rows inserted/updated/deleted
ij> -- PASS:0254 If 6 rows are inserted?
     UPDATE PROJ1
          SET CITY = PTYPE;
6 rows inserted/updated/deleted
ij> -- PASS:0254 If 6 rows are updated?
     SELECT CITY
          FROM PROJ1
          WHERE PNUM = 'P1';
CITY           
---------------
Design         
ij> -- PASS:0254 If CITY = 'Design'?
-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0254 <<< END TEST
-- **************************************************************
-- TEST:0255 Key word USER for INSERT, UPDATE!
     DELETE FROM T4;
0 rows inserted/updated/deleted
ij> -- Making sure the table is empty
-- setup
     INSERT INTO T4
          VALUES(USER,100,'good','luck');
1 row inserted/updated/deleted
ij> -- PASS:0255 If 1 row is inserted?
     SELECT STR110 
          FROM T4
          WHERE NUM6 = 100;
STR110                                                                                                        
--------------------------------------------------------------------------------------------------------------
HU                                                                                                            
ij> -- PASS:0255 If STR110 = 'HU'?
-- setup
     INSERT INTO T4
          VALUES('Hello',101,'good','luck');
1 row inserted/updated/deleted
ij> -- PASS:0255 If 1 row is inserted?
     UPDATE T4
          SET STR110 = USER
          WHERE NUM6 = 101;
1 row inserted/updated/deleted
ij> -- PASS:0255 If 1 row is updated?
     SELECT STR110 
          FROM T4
          WHERE NUM6 = 101;
STR110                                                                                                        
--------------------------------------------------------------------------------------------------------------
HU                                                                                                            
ij> -- PASS:0255 If STR110 = 'HU'?
-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0255 <<< END TEST
-- ***************************************************************
-- TEST:0256 Key word USER in WHERE clause!
     DELETE FROM T4;
0 rows inserted/updated/deleted
ij> -- Making sure the table is empty
-- setup
     INSERT INTO T4
            VALUES('HU',100,'good','luck');
1 row inserted/updated/deleted
ij> -- PASS:0256 If 1 row is inserted?
     SELECT STR110 
          FROM T4
          WHERE STR110 = USER;
STR110                                                                                                        
--------------------------------------------------------------------------------------------------------------
HU                                                                                                            
ij> -- PASS:0256 If STR110 = 'HU'?
-- setup
     INSERT INTO T4
            VALUES('Hello',101,'good','luck');
1 row inserted/updated/deleted
ij> -- PASS:0256 If 1 row is inserted?
     DELETE FROM T4
            WHERE STR110 = USER;
1 row inserted/updated/deleted
ij> -- PASS:0256 If 1 row is deleted?
     SELECT COUNT(*)
          FROM T4
          WHERE STR110 LIKE '%HU%';
1          
-----------
0          
ij> -- PASS:0256 If count = 0?
-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0256 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>