dml147.out

来自「derby database source code.good for you.」· OUT 代码 · 共 223 行

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML147  
-- SQL Test Suite, V6.0, Interactive SQL, dml147.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER            
--O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O   ROLLBACK WORK;
-- date_time print
-- TEST:0840 Roll back schema manipulation !
   CREATE TABLE NOT_THERE (C1 CHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0840 If table is created?
   ROLLBACK WORK;
ij> INSERT INTO NOT_THERE VALUES ('1234567890');
ERROR 42X05: Table 'NOT_THERE' does not exist.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
   ROLLBACK WORK;
ij> CREATE VIEW NOT_HERE AS
      SELECT * FROM USIG;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If view is created?
   ROLLBACK WORK;
ij> SELECT COUNT (*) FROM NOT_HERE;
ERROR 42X05: Table 'NOT_HERE' does not exist.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
   ROLLBACK WORK;
ij> ALTER TABLE USIG
  ADD COLUMN NUL INT;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If column is added?
   ROLLBACK WORK;
ij> SELECT COUNT (*)
  FROM USIG WHERE NUL IS NULL;
ERROR 42X04: Column 'NUL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'NUL' is not a column in the target table.
ij> -- PASS:0840 If ERROR, syntax error/access violation, 0 rows selected?
   ROLLBACK WORK;
ij> --O   DROP TABLE USIG CASCADE;
   DROP TABLE USIG ;
0 rows inserted/updated/deleted
ij> -- PASS:0840 If table is dropped?
   ROLLBACK WORK;
ij> SELECT COUNT(*)
  FROM U_SIG;
1          
-----------
2          
ij> -- PASS:0840 If count = 2?
   ROLLBACK WORK;
ij> SELECT COUNT(*)
  FROM USIG;
1          
-----------
2          
ij> -- PASS:0840 If count = 2?
   ROLLBACK WORK;
ij> -- END TEST >>> 0840 <<< END TEST
-- *********************************************
-- TEST:0841 Multiple-join and default order of joins !
-- setup
   DELETE FROM HU.STAFF4;
0 rows inserted/updated/deleted
ij> INSERT INTO HU.STAFF4
      SELECT * FROM HU.STAFF3
         WHERE EMPNUM > 'E3';
2 rows inserted/updated/deleted
ij> --O   SELECT EMPNUM FROM
   SELECT a.EMPNUM FROM
--O      HU.STAFF3 NATURAL LEFT JOIN HU.STAFF NATURAL INNER JOIN HU.STAFF4
      HU.STAFF3 a, HU.staff b, HU.staff4 c 
	where a.empnum = b.empnum and b.empnum = c.empnum
      ORDER BY EMPNUM DESC;
EM&
---
E5 
E4 
ij> -- PASS:0841 If 2 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E5, E4 ?
--O   SELECT EMPNUM FROM
--O      (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF) NATURAL INNER JOIN HU.STAFF4
--O       ORDER BY EMPNUM ASC;
-- PASS:0841 If 2 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E4, E5 ?
--O   SELECT EMPNUM FROM
--O      HU.STAFF3 NATURAL LEFT JOIN (HU.STAFF NATURAL INNER JOIN HU.STAFF4)
--O      ORDER BY EMPNUM;
--O      ;
-- PASS:0841 If 5 rows selected?
-- PASS:0841 If ordered EMPNUM values are: E1, E2, E3, E4, E5 ?
   ROLLBACK WORK;
ij> -- END TEST >>> 0841 <<< END TEST
-- *********************************************
-- TEST:0842 Multi-column joins !
-- setup
   CREATE TABLE STAFF66 (
     SALARY   INTEGER,
     EMPNAME CHAR(20),
     GRADE   DECIMAL,
     EMPNUM  CHAR(3));
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- setup
   INSERT INTO STAFF66
      SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM
         FROM HU.STAFF3 WHERE EMPNUM > 'E2';
3 rows inserted/updated/deleted
ij> -- PASS:0842 If 3 rows inserted ?
   UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM = 'E5';
1 row inserted/updated/deleted
ij> -- PASS:0842 If 1 row updated ?
   UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE = 12;
2 rows inserted/updated/deleted
ij> -- PASS:0842 If 2 rows updated ?
-- FULL OUTER JOIN of tables with unique data in the joined column
--O   SELECT EMPNUM, CITY, SALARY
--O      FROM HU.STAFF3 LEFT JOIN STAFF66 USING (EMPNUM)
--O   UNION
--O   SELECT EMPNUM, CITY, SALARY
--O      FROM HU.STAFF3 RIGHT JOIN STAFF66 USING (EMPNUM)
--O      ORDER BY EMPNUM;
-- PASS:0842 If 6 rows selected with ordered rows and column values ?
-- PASS:0842    E1   Deale   NULL   ?
-- PASS:0842    E2   Vienna  NULL   ?
-- PASS:0842    E3   Vienna  13000  ?
-- PASS:0842    E4   Deale   12000  ?
-- PASS:0842    E5   NULL    13000  ?
-- PASS:0842    E6   Akron   NULL   ?
-- 7.5 SR 6 d
-- table STAFF66 has 3 rows, only 1 matching on all columns
-- this is a 3-column join:
   SELECT * FROM
--O      STAFF66 NATURAL INNER JOIN HU.STAFF3;
      STAFF66 a, HU.staff3 b where a.empnum = b.empnum
	and a.grade = b.grade
	and a.empname = b.empname;
SALARY     |EMPNAME             |GRADE |EMP&|EM&|EMPNAME             |GRADE|CITY           
-------------------------------------------------------------------------------------------
13000      |Carmen              |13    |E3  |E3 |Carmen              |13   |Vienna         
ij> -- PASS:0842 If 1 row selected?
-- PASS:0842 If column values are in the exact order: ?
-- PASS:0842 EMPNAME=Carmen,GRADE=13,EMPNUM=E3,SALARY=13000,CITY=Vienna?
-- table STAFF66 has 3 rows, only 1 matching on all columns
-- this is a 3-column join, preserving HU.STAFF3:
--O   SELECT EMPNUM, EMPNAME, SALARY FROM
--O      HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66
--O      WHERE EMPNUM > 'E1'
--O      ORDER BY EMPNUM ASC;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842    E2   Betty    NULL  ?
-- PASS:0842    E3   Carmen   13000 ?
-- PASS:0842    E4   Ali      NULL  ?
-- PASS:0842    E6   Ed       NULL  ?
-- table HU.STAFF has 5 rows, only 3 matching on all columns
-- this is a 3-column join, preserving HU.STAFF:
--O   SELECT EMPNUM, EMPNAME, SALARY FROM
--O      STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF
--O      WHERE EMPNUM > 'E1'
--O      ORDER BY EMPNUM DESC;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842    E5  Ed      13000  ?
-- PASS:0842    E4  Don     12000  ?
-- PASS:0842    E3  Carmen  13000  ?
-- PASS:0842    E2  Betty   NULL   ?
-- table HU.STAFF has 5 rows, only 3 matching on all columns
-- ordinal position is determined by order in T1, not USING list
-- REF:  7.5 SR 6 d
-- this is a 3-column join, preserving HU.STAFF:
--O   SELECT * FROM
--O      STAFF66 RIGHT JOIN HU.STAFF USING ( GRADE, EMPNUM, EMPNAME)
--O      WHERE EMPNUM > 'E1'
--O      ORDER BY EMPNUM;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842    Betty    10   E2   NULL    Vienna ?
-- PASS:0842    Carmen   13   E3   13000   Vienna ?
-- PASS:0842    Don      12   E4   12000   Deale  ?
-- PASS:0842    Ed       13   E5   13000   Akron  ?
-- table STAFF66 has 3 rows, with 2 matching on named columns
-- this is a 2-column join, preserving HU.STAFF3:
--O   SELECT * FROM
--O      HU.STAFF3 LEFT JOIN STAFF66 USING (GRADE, EMPNUM)
--O      WHERE EMPNUM > 'E1'
--O      ORDER BY EMPNUM ASC;
-- PASS:0842 If 4 rows selected with ordered rows and column values ?
-- PASS:0842    E2  10  Betty   Vienna    NULL    NULL   ?
-- PASS:0842    E3  13  Carmen  Vienna    13000   Carmen ?
-- PASS:0842    E4  12  Ali     Deale     12000   Don    ?
-- PASS:0842    E6  13  Ed      Akron     NULL    NULL   ?
-- similar to above, except for explicit names of columns
--O   SELECT staff3.EMPNUM, staff3.GRADE, HU.STAFF3.EMPNAME, CITY,
   SELECT HU.staff3.EMPNUM, HU.staff3.GRADE, HU.STAFF3.EMPNAME, CITY,
     SALARY, STAFF66.EMPNAME FROM
--O      HU.STAFF3 LEFT JOIN STAFF66 USING (GRADE, EMPNUM)
--O      WHERE EMPNUM = 'E3';
      HU.STAFF3, STAFF66 where HU.staff3.GRADE = staff66.grade and  HU.staff3.EMPNUM = staff66.empnum
      and HU.staff3.EMPNUM = 'E3';
EM&|GRADE|EMPNAME             |CITY           |SALARY     |EMPNAME             
-------------------------------------------------------------------------------
E3 |13   |Carmen              |Vienna         |13000      |Carmen              
ij> -- PASS:0842 If 1 row selected with ordered column values?
-- PASS:0842    E3  13  Carmen  Vienna  13000  Carmen    ?
-- REF: 7.5 GR 1 d ii
-- this is a cartesian product
--O   SELECT COUNT (*) FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ;
   SELECT count (*) FROM STAFF66 , HU.PROJ;
1          
-----------
18         
ij> -- PASS:0842 If count = 18?
   ROLLBACK WORK;
ij> --O   DROP TABLE STAFF66 CASCADE;
   DROP TABLE STAFF66 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0842 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>