refactions1.sql

derby database source code.good for you.

  dno char(3)  references db2test.dept
  on delete cascade );
insert into db2test.emp13 select * from db2test.emp;
-- create a 14th child table like emp
create table db2test.emp14 (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.emp14 select * from db2test.emp;
-- create a 15th child table like emp
create table db2test.emp15 (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.emp15 select * from db2test.emp;
create view db2test.vempjoin12 (vname1, vname2, vname3, vname4, vname5,
   vname6, vname7, vname8, vname9, vname10, vname11, vname12,
   vmgrname, vdno) as
  select e.name, e2.name, e3.name, e4.name, e5.name, e6.name, e7.name,
   e8.name, e9.name, e10.name, e11.name, e12.name,
   e.mgrname, e.dno
  from db2test.emp e, db2test.emp2 e2, db2test.emp3 e3, db2test.emp4 e4,
  db2test.emp5 e5, db2test.emp6 e6, db2test.emp7 e7, db2test.emp8 e8,
  db2test.emp9 e9, db2test.emp10 e10, db2test.emp11 e11,
  db2test.emp12 e12
  where e.dno = e2.dno
  and e.dno = e2.dno
  and e.dno = e2.dno
  and e.dno = e3.dno
  and e.dno = e4.dno
  and e.dno = e5.dno
  and e.dno = e6.dno
  and e.dno = e7.dno
  and e.dno = e8.dno
  and e.dno = e9.dno
  and e.dno = e10.dno
  and e.dno = e11.dno
  and e.dno = e12.dno;
commit;

-- FOLLOWING TWO QUERIES HANG IN DERBY NOW ..
-- UNCOMMENT once they pass.
-- select * from db2test.dept where dno in (select vdno from
--  db2test.vempjoin12)
--  and dno in ('K55', 'K52') order by 2, 3;

--delete from db2test.dept where dno in (select vdno from
--  db2test.vempjoin12)
--  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;
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;
select * from db2test.emp6 order by dno, name, mgrname;
select * from db2test.emp7 order by dno, name, mgrname;
select * from db2test.emp8 order by dno, name, mgrname;
select * from db2test.emp9 order by dno, name, mgrname;
select * from db2test.emp10 order by dno, name, mgrname;
select * from db2test.emp11 order by dno, name, mgrname;
select * from db2test.emp12 order by dno, name, mgrname;
select * from db2test.emp13 order by dno, name, mgrname;
select * from db2test.emp14 order by dno, name, mgrname;
select * from db2test.emp15 order by dno, name, mgrname;
-- "END OF TESTUNIT: 10";


-- *************************************************************************
-- TESTUNIT         : 11
-- DESCRIPTION      : delete with many SQ levels correl'd on child tables
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 11";
-- reset to original rows
delete from db2test.dept;
delete from db2test.emp;
delete from db2test.secondemp;
delete from db2test.emp2;
delete from db2test.emp3;
delete from db2test.emp4;
delete from db2test.emp5;
delete from db2test.emp6;
delete from db2test.emp7;
delete from db2test.emp8;
delete from db2test.emp9;
delete from db2test.emp10;
delete from db2test.emp11;
delete from db2test.emp12;
delete from db2test.emp13;
delete from db2test.emp14;
delete from db2test.emp15;
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;
insert into db2test.emp3 select * from db2test.emp;
insert into db2test.emp4 select * from db2test.emp;
insert into db2test.emp5 select * from db2test.emp;
insert into db2test.emp6 select * from db2test.emp;
insert into db2test.emp7 select * from db2test.emp;
insert into db2test.emp8 select * from db2test.emp;
insert into db2test.emp9 select * from db2test.emp;
insert into db2test.emp10 select * from db2test.emp;
insert into db2test.emp11 select * from db2test.emp;
insert into db2test.emp12 select * from db2test.emp;
insert into db2test.emp13 select * from db2test.emp;
insert into db2test.emp14 select * from db2test.emp;
insert into db2test.emp15 select * from db2test.emp;
commit;
select * from db2test.dept d where
  dno in (select dno from db2test.emp e where
 e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where
 e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where
 e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where
 e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where
 e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where
 e6.dno = e5.dno and e6.dno in ('K55', 'K52')))))))
 order by 2, 3;
delete from db2test.dept  where
  dno in (select dno from db2test.emp e where
 e.dno = db2test.dept.dno and e.dno in (select dno from db2test.emp2 e2 where
 e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where
 e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where
 e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where
 e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where
 e6.dno = e5.dno and e6.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;
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;
select * from db2test.emp6 order by dno, name, mgrname;
select * from db2test.emp7 order by dno, name, mgrname;
select * from db2test.emp8 order by dno, name, mgrname;
select * from db2test.emp9 order by dno, name, mgrname;
select * from db2test.emp10 order by dno, name, mgrname;
select * from db2test.emp11 order by dno, name, mgrname;
select * from db2test.emp12 order by dno, name, mgrname;
select * from db2test.emp13 order by dno, name, mgrname;
select * from db2test.emp14 order by dno, name, mgrname;
select * from db2test.emp15 order by dno, name, mgrname;
-- "END OF TESTUNIT: 11";


-- *************************************************************************
-- TESTUNIT         : 12
-- DESCRIPTION      : delete on view with union of 15 child tables
-- EXPECTED RESULTS : SQL command should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 12";
-- reset to original rows
delete from db2test.dept;
delete from db2test.secondemp;
delete from db2test.emp;
delete from db2test.emp2;
delete from db2test.emp3;
delete from db2test.emp4;
delete from db2test.emp5;
delete from db2test.emp6;
delete from db2test.emp7;
delete from db2test.emp8;
delete from db2test.emp9;
delete from db2test.emp10;
delete from db2test.emp11;
delete from db2test.emp12;
delete from db2test.emp13;
delete from db2test.emp14;
delete from db2test.emp15;
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;
insert into db2test.emp3 select * from db2test.emp;
insert into db2test.emp4 select * from db2test.emp;
insert into db2test.emp5 select * from db2test.emp;
insert into db2test.emp6 select * from db2test.emp;
insert into db2test.emp7 select * from db2test.emp;
insert into db2test.emp8 select * from db2test.emp;
insert into db2test.emp9 select * from db2test.emp;
insert into db2test.emp10 select * from db2test.emp;
insert into db2test.emp11 select * from db2test.emp;
insert into db2test.emp12 select * from db2test.emp;
insert into db2test.emp13 select * from db2test.emp;
insert into db2test.emp14 select * from db2test.emp;
insert into db2test.emp15 select * from db2test.emp;
create view db2test.vempunion15 (vname,
   vmgrname, vdno) as
  (select e.name, e.mgrname, e.dno
  from db2test.emp e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp2 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp3 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp4 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp5 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp6 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp7 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp8 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp9 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp10 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp11 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp12 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp13 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp14 e)
union all
  (select e.name, e.mgrname, e.dno
  from db2test.emp15 e);
commit;
select * from db2test.dept where dno in
(select vdno from db2test.vempunion15)
  and dno in ('K55', 'K52') order by 1, 2;
delete from db2test.dept where dno in
(select vdno from db2test.vempunion15)
  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;
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;
select * from db2test.emp6 order by dno, name, mgrname;
select * from db2test.emp7 order by dno, name, mgrname;
select * from db2test.emp8 order by dno, name, mgrname;
select * from db2test.emp9 order by dno, name, mgrname;
select * from db2test.emp10 order by dno, name, mgrname;
select * from db2test.emp11 order by dno, name, mgrname;
select * from db2test.emp12 order by dno, name, mgrname;
select * from db2test.emp13 order by dno, name, mgrname;
select * from db2test.emp14 order by dno, name, mgrname;
select * from db2test.emp15 order by dno, name, mgrname;
-- "END OF TESTUNIT: 12";
-- "cleanup";

drop view VEMPUNION15;
drop view VEMPJOIN12;
drop view VEMPJOIN;
drop table db2test.emp15;
drop table db2test.emp14;
drop table db2test.emp13;
drop table db2test.emp12;
drop table db2test.emp11;
drop table db2test.emp10;
drop table db2test.emp9;
drop table db2test.emp8;
drop table db2test.emp7;
drop table db2test.emp6;
drop table db2test.emp5;
drop table db2test.emp4;
drop table db2test.emp3;
drop table db2test.emp2;
drop table db2test.secondemp;
drop table db2test.emp;
drop table db2test.origemp;
drop table db2test.origdept;
drop table db2test.dept;
commit;

-- "cself302.sql ENDED";