refactions1.sql

derby database source code.good for you.

delete from db2test.dept where dno in (select dno
   from db2test.secondemp E
   where E.dno in (select dno from db2test.emp D where E.dno = D.dno
   and D.dno = 'K55'));
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;

-- "END OF TESTUNIT: 03";

-- *************************************************************************
-- TESTUNIT         : 04
-- DESCRIPTION      : delete with SQ GB Having SQ on
--                  : child table
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 04";
-- reset to original rows
delete from db2test.emp;
delete from db2test.dept;
delete from db2test.secondemp;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
select * from db2test.dept where exists ( select max(mgrname) from
  db2test.secondemp group by dno having dno in (select dno from
  db2test.emp where mgrname = 'ASHOK')) order by 2, 3;
delete from db2test.dept where exists ( select max(mgrname) from
  db2test.secondemp group by dno having dno in (select dno from
  db2test.emp where mgrname = 'ASHOK'));
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;
-- "END OF TESTUNIT: 04";

-- *************************************************************************
-- TESTUNIT         : 05
-- DESCRIPTION      : delete with SQ on child table correlated to SQ
--                  : above -- 7 levels
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 05";
-- reset to original rows
delete from db2test.emp;
delete from db2test.dept;
delete from db2test.secondemp;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
commit;
select * from db2test.dept dtop where exists
  (select * from db2test.secondemp  where exists
     (select dno from
      db2test.dept D1 where dno = dtop.dno and dno in
         (select dno from db2test.emp E2 where D1.dno = E2.dno and dno in
         (select dno from db2test.emp E3 where E2.dno = E3.dno and dno in
         (select dno from db2test.emp E4 where E3.dno = E4.dno and dno in
         (select dno from db2test.emp E5 where E4.dno = E5.dno and dno in
         (select dno from db2test.emp E6 where E5.dno = E6.dno)
      )))))) order by 2, 3;
-- begin RJC950405 (commented out and added ; to the following line as I think we're fixed?)
-- -- testcase commented out pending defect fix;
-- end RJC950405
 delete from db2test.dept where exists
  (select * from db2test.secondemp  where exists
     (select dno from
      db2test.dept D1 where dno = db2test.dept.dno and dno in
         (select dno from db2test.emp E2 where D1.dno = E2.dno and dno in
         (select dno from db2test.emp E3 where E2.dno = E3.dno and dno in
         (select dno from db2test.emp E4 where E3.dno = E4.dno and dno in
         (select dno from db2test.emp E5 where E4.dno = E5.dno and dno in
         (select dno from db2test.emp E6 where E5.dno = E6.dno)
      ))))));
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;
-- "END OF TESTUNIT: 05";

-- *************************************************************************
-- TESTUNIT         : 06
-- DESCRIPTION      : delete with SQ on child table correlated to SQ
--                  : on second child table, delete cascade on each
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 06";
-- reset to original rows
delete from db2test.emp;
delete from db2test.dept;
delete from db2test.secondemp;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
-- create a second child table like emp
create table db2test.emp2 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp2 select * from db2test.emp;
commit;
select * from db2test.dept dtop where exists
    (select dno from db2test.emp2 E2 where Dtop.dno = E2.dno and dno in
    (select dno from db2test.emp E3 where E2.dno = E3.dno))
    order by 2, 3;
delete from db2test.dept where exists
    (select dno from db2test.emp2 E2 where db2test.dept.dno = E2.dno and dno in
    (select dno from db2test.emp E3 where E2.dno = E3.dno));
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
-- "END OF TESTUNIT: 06";


-- *************************************************************************
-- TESTUNIT         : 07
-- DESCRIPTION      : delete with SQ on child table correlated to SQ
--                  : on second child table, delete cascade on each
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 07";
-- reset to original rows
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.dept;
delete from db2test.secondemp;
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.secondemp select * from db2test.origemp;
commit;
select * from db2test.dept dtop where exists
    (select dno from db2test.emp2 E2 where dtop.dno = E2.dno)
     and exists
    (select dno from db2test.emp E3 where dtop.dno = E3.dno)
    order by 2,3;
delete from db2test.dept  where exists
    (select dno from db2test.emp2 E2 where db2test.dept.dno = E2.dno)
     and exists
    (select dno from db2test.emp E3 where db2test.dept.dno = E3.dno);
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
-- "END OF TESTUNIT: 07";

-- *************************************************************************
-- TESTUNIT         : 08
-- DESCRIPTION      : delete with SQ on child table correlated to SQ
--                  : on second child table, delete cascade on each
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 08";
-- reset to original rows
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.secondemp;
delete from db2test.dept;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
select * from db2test.dept dtop where exists
    (select dno from db2test.emp2 E2 where dtop.dno = E2.dno)
     or exists
    (select dno from db2test.emp E3 where dtop.dno = E3.dno)
    order by 2, 3;
delete from db2test.dept where exists
    (select dno from db2test.emp2 E2 where db2test.dept.dno = E2.dno)
     or exists
    (select dno from db2test.emp E3 where db2test.dept.dno = E3.dno);
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
-- "END OF TESTUNIT: 08";

-- *************************************************************************
-- TESTUNIT         : 09
-- DESCRIPTION      : delete on view with SQ GB Having SQ on
--                  : modified table -- 3 levels
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 09";
-- reset to original rows
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.dept;
delete from db2test.secondemp;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
create view db2test.vempjoin (vname1, vname2, vmgrname, vdno) as
  select e.name, e2.name, e.mgrname, e.dno
  from db2test.emp e, db2test.emp2 e2
  where e.dno = e2.dno;
commit;
select * from db2test.dept where dno in (select vdno from
  db2test.vempjoin)
  and dno in ('K55', 'K52') order by 2, 3;
delete from db2test.dept where dno in (select vdno from
  db2test.vempjoin)
  and dno in ('K55', 'K52');
select * from db2test.dept order by dno, dname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.secondemp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
-- "END OF TESTUNIT: 09";

-- *************************************************************************
-- TESTUNIT         : 10
-- DESCRIPTION      : delete on iudt where SQ on
--                  : view with join on 15 child tables
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 10";
-- reset to original rows
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.dept;
delete from db2test.secondemp;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.secondemp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
-- create a third child table like emp
create table db2test.emp3 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  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,
  mgrname char(10) references db2test.emp2 on delete cascade,
  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,
  mgrname char(10) references db2test.emp2 on delete cascade,
  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,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp6 select * from db2test.emp;
-- create a 7th child table like emp
create table db2test.emp7 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp7 select * from db2test.emp;
-- create a 8th child table like emp
create table db2test.emp8 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp8 select * from db2test.emp;
-- create a 9th child table like emp
create table db2test.emp9 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp9 select * from db2test.emp;
-- create a 10th child table like emp
create table db2test.emp10 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp10 select * from db2test.emp;
-- create a 11th child table like emp
create table db2test.emp11 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp11 select * from db2test.emp;
-- create a 12th child table like emp
create table db2test.emp12 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,
  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp12 select * from db2test.emp;
-- create a 13th child table like emp
create table db2test.emp13 (c0 int, name char(10) not null primary key,
  mgrname char(10) references db2test.emp2 on delete cascade,