refactions1.sql

derby database source code.good for you.

-- #########################################################################
-- # TESTCASE NAME    : cself303.sql
-- # LINE ITEM        : Self-referencing subqueries
-- # DESCRIPTION      : Allow use of subqueries on the same table being
-- #                  : inserted, deleted, or updated.  Cursors updated
-- #                  : or delete where current of are now similarly
-- #                  : unrestricted. Also allowed are subqueries
-- #                  : on tables related to the modified table by
-- #                  : referential relationships, either directly or
-- #                  : indirectly.
-- #                  : This file covers cases where of delete statements
-- #                  : where the deleted table is connected to other
-- #                  : tables in the query by cascade on delete.
-- #                  : shape of the RI tree is a 6 level chain.
-- #########################################################################

-- "START OF TESTCASE: cself303.sql";

autocommit off;

-- *************************************************************************
-- TESTUNIT         : 01
-- DESCRIPTION      : Create tables db2test.emp
--                  : insert some data into it
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 01";
create table db2test.dept (c0 int, dno char(3) not null primary key,
   dname char(10));
create table db2test.origdept (c0 int, dno char(3) not null primary key,
   dname char(10));
insert into db2test.dept values (1, 'K55', 'DB');
insert into db2test.dept values (2, 'K52', 'OFC');
insert into db2test.dept values (3, 'K51', 'CS');
insert into db2test.origdept select * from db2test.dept;
create table db2test.emp (c0 int, name char(10) not null primary key,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp (c0, name, dno) values (1, 'ASHOK', 'K51');
insert into db2test.emp values (2, 'JOHN', 'ASHOK', 'K51');
insert into db2test.emp values (3, 'ROBIN', 'ASHOK', 'K51');
insert into db2test.emp values (4, 'JOE1', 'ASHOK', 'K51');
insert into db2test.emp values (5, 'JOE2', 'ASHOK', 'K51');
insert into db2test.emp values (6, 'HAMID', 'JOHN', 'K55');
insert into db2test.emp values (7, 'TRUONG', 'HAMID', 'K55');
insert into db2test.emp values (8, 'LARRY1', 'HAMID', 'K55');
insert into db2test.emp values (9, 'LARRY2', 'HAMID', 'K55');
insert into db2test.emp values (10, 'BOBBIE', 'HAMID', 'K55');
insert into db2test.emp values (11, 'ROGER', 'ROBIN', 'K52');
insert into db2test.emp values (12, 'JIM', 'ROGER', 'K52');
insert into db2test.emp values (13, 'DAN', 'ROGER', 'K52');
insert into db2test.emp values (14, 'SAM1', 'ROGER', 'K52');
insert into db2test.emp values (15, 'SAM2', 'ROGER', 'K52');
insert into db2test.emp values (16, 'GUY', 'JOHN', 'K55');
insert into db2test.emp values (17, 'DON', 'GUY', 'K55');
insert into db2test.emp values (18, 'MONICA', 'GUY', 'K55');
insert into db2test.emp values (19, 'LILY1', 'GUY', 'K55');
insert into db2test.emp values (20, 'LILY2', 'GUY', 'K55');
create table db2test.origemp (c0 int, name char(10) not null primary key,
  mgrname char(10),
  dno char(3));
insert into db2test.origemp select * from db2test.emp;
-- create a second child table like emp
create table db2test.emp2 (c0 int, name char(10) not null primary key
  references db2test.emp  on delete cascade,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp2 select * from db2test.emp;
-- create a third child table like emp
create table db2test.emp3 (c0 int, name char(10) not null primary key
  references db2test.emp  on delete cascade,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp3 select * from db2test.emp;
-- create a 4th child table like emp
create table db2test.emp4 (c0 int, name char(10) not null primary key
  references db2test.emp  on delete cascade,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp4 select * from db2test.emp;
-- create a 5th child table like emp
create table db2test.emp5 (c0 int, name char(10) not null primary key
  references db2test.emp  on delete cascade,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp5 select * from db2test.emp;
-- create a 6th child table like emp
create table db2test.emp6 (c0 int, name char(10) not null primary key
  references db2test.emp  on delete cascade,
  mgrname char(10),
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp6 select * from db2test.emp;

-- "END OF TESTUNIT: 01";

-- *************************************************************************
-- TESTUNIT         : 02
-- DESCRIPTION      : delete where SQ on child table on RI cascade col
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 02";
select * from db2test.emp e where dno in (select dno from db2test.emp3 e3
   where e3.dno in (select dno from db2test.emp2 e2
   where mgrname = 'JOHN' and e3.mgrname = e2.mgrname)) order by 2, 3, 4;
delete from db2test.emp  where dno in (select dno from db2test.emp3 e3
   where e3.dno in (select dno from db2test.emp2 e2
   where mgrname = 'JOHN' and e3.mgrname = e2.mgrname));
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
select * from db2test.emp3 order by dno, name, mgrname;
select * from db2test.emp4 order by dno, name, mgrname;
select * from db2test.emp5 order by dno, name, mgrname;

-- "END OF TESTUNIT: 02";

-- *************************************************************************
-- TESTUNIT         : 03
-- DESCRIPTION      : delete where SQ on child table on RI cascade col
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 03";
-- reset to original rows
delete from db2test.dept;
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.emp3;
delete from db2test.emp4;
delete from db2test.emp5;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
insert into db2test.emp3 select * from db2test.origemp;
insert into db2test.emp4 select * from db2test.origemp;
insert into db2test.emp5 select * from db2test.origemp;
select * from db2test.emp e where dno in (select dno from db2test.emp3 e3
   where e3.dno in (select dno from db2test.emp2 e2
   where mgrname = 'JOHN')) order by 2,3,4;
delete from db2test.emp where dno in (select dno from db2test.emp3 e3
   where e3.dno in (select dno from db2test.emp2 e2
   where mgrname = 'JOHN'));
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
select * from db2test.emp3 order by dno, name, mgrname;
select * from db2test.emp4 order by dno, name, mgrname;
select * from db2test.emp5 order by dno, name, mgrname;

-- "END OF TESTUNIT: 03";

-- *************************************************************************
-- TESTUNIT         : 04
-- DESCRIPTION      : delete where SQ chain reversing RI child chain
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 04";
-- reset to original rows
delete from db2test.dept;
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.emp3;
delete from db2test.emp4;
delete from db2test.emp5;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
insert into db2test.emp3 select * from db2test.origemp;
insert into db2test.emp4 select * from db2test.origemp;
insert into db2test.emp5 select * from db2test.origemp;
select * from db2test.emp e
  where dno in (select dno from db2test.emp5 e5
   where e5.dno in (select dno from db2test.emp4 e4 where
   e5.name = e4.mgrname and e4.dno in
     (select dno from db2test.emp3 e3 where e4.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e3.name = e2.mgrname and
                  e2.dno in (select dno from db2test.emp  e1
                  where e1.name = e.mgrname and e1.mgrname = 'JOHN')))))
    order by 2, 3, 4;
delete from db2test.emp 
  where dno in (select dno from db2test.emp5 e5
   where e5.dno in (select dno from db2test.emp4 e4 where
   e5.name = e4.mgrname and e4.dno in
     (select dno from db2test.emp3 e3 where e4.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e3.name = e2.mgrname and
                  e2.dno in (select dno from db2test.emp  e1
                  where e1.name = db2test.emp.mgrname and 
                  e1.mgrname = 'JOHN')))));
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
select * from db2test.emp3 order by dno, name, mgrname;
select * from db2test.emp4 order by dno, name, mgrname;
select * from db2test.emp5 order by dno, name, mgrname;

-- "END OF TESTUNIT: 04";

-- *************************************************************************
-- TESTUNIT         : 05
-- DESCRIPTION      : delete where SQ chain reversing RI child chain
--                  : combining where and having
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 05";
-- reset to original rows
delete from db2test.dept;
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.emp3;
delete from db2test.emp4;
delete from db2test.emp5;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
insert into db2test.emp3 select * from db2test.origemp;
insert into db2test.emp4 select * from db2test.origemp;
insert into db2test.emp5 select * from db2test.origemp;
--FOLLOWING TWO QUERIES are giving syntax errors currently
--select * from db2test.emp e
--  where dno in (select dno from db2test.emp5 e5
--   where e5.dno in (select dno from db2test.emp4 e4 where
--   e5.name = e4.mgrname group by dno having dno in
--     (select dno from db2test.emp3 e3 where e4.dno = e3.dno and
--       e3.dno in (select dno from db2test.emp2 e2
--                  where e3.name = e2.mgrname group by dno having
--                  e2.dno in (select dno from db2test.emp e1
--                  where e1.mgrname = e.mgrname and
--                  e1.mgrname = 'JOHN'))))) order by 2, 3, 4;
-- delete from db2test.emp 
--  where dno in (select dno from db2test.emp5 e5
--   where e5.dno in (select dno from db2test.emp4 e4 where
--   e5.name = e4.mgrname group by dno having dno in
--     (select dno from db2test.emp3 e3 where e4.dno = e3.dno and
--       e3.dno in (select dno from db2test.emp2 e2
--                  where e3.name = e2.mgrname group by dno having
--                  e2.dno in (select dno from db2test.emp  e1
--                  where e1.mgrname = e.mgrname and
--                  e1.mgrname = 'JOHN')))));
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
select * from db2test.emp3 order by dno, name, mgrname;
select * from db2test.emp4 order by dno, name, mgrname;
select * from db2test.emp5 order by dno, name, mgrname;

-- "END OF TESTUNIT: 05";


-- *************************************************************************
-- TESTUNIT         : 06
-- DESCRIPTION      : delete where SQ chain reversing RI child chain
--                  : combining where and having, correl to iudt
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 06";
-- reset to original rows
delete from db2test.dept;
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.emp3;
delete from db2test.emp4;
delete from db2test.emp5;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
insert into db2test.emp3 select * from db2test.origemp;
insert into db2test.emp4 select * from db2test.origemp;
insert into db2test.emp5 select * from db2test.origemp;
select * from db2test.emp e
  where dno in (select dno from db2test.emp5 e5
   where e5.dno in (select dno from db2test.emp4 e4 where
   db2test.emp.name = e4.mgrname group by dno having dno in
     (select dno from db2test.emp3 e3 where e.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e.mgrname = e2.mgrname group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                   where db2test.emp.mgrname = 'JOHN'))))) order by 2,3,4;
delete from db2test.emp 
  where dno in (select dno from db2test.emp5 e5
   where e5.dno in (select dno from db2test.emp4 e4 where
   db2test.emp.name = e4.mgrname group by dno having dno in
     (select dno from db2test.emp3 e3 where db2test.emp.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e.mgrname = e2.mgrname group by dno having
                  e2.dno in (select dno from db2test.emp  e1