refactions1.sql

derby database source code.good for you.

                   where e.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: 06";

-- *************************************************************************
-- TESTUNIT         : 07
-- DESCRIPTION      : delete where SQ chain reversing RI child chain
--                  : combining where and having, correl to grandparent
-- EXPECTED RESULTS : SQL commands should get error -119
-- *************************************************************************
-- "START OF TESTUNIT: 07";
-- 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
   e.name = e4.mgrname group by dno having dno in
     (select dno from db2test.emp3 e3 where e5.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e4.dno = e2.dno group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                   where e.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 e5.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e4.dno = e2.dno group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                   where db2test.emp.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: 07";

-- *************************************************************************
-- TESTUNIT         : 08
-- DESCRIPTION      : delete where SQ chain reversing RI child chain
--                  : combining where and having, correl to grandparent
-- EXPECTED RESULTS : select should get -119, delete should get ???
-- *************************************************************************
-- "START OF TESTUNIT: 08";
-- 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
   e.name = e4.mgrname group by dno having dno in
     (select dno from db2test.emp3 e3 where e5.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e4.name = e2.mgrname group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                   where e.mgrname = 'JOHN'))))) order by 2, 3, 4;
-- select should get -119;
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 e5.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e4.name = e2.mgrname group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                  where db2test.emp.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: 08";

-- *************************************************************************
-- TESTUNIT         : 09
-- DESCRIPTION      : delete where SQ chain reversing RI child chain
--                  : combining where and having, correl to grandparent
-- EXPECTED RESULTS : SQL commands should complete successfully
-- *************************************************************************
-- "START OF TESTUNIT: 09";
-- 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
   e.name = e4.mgrname group by dno having dno in
     (select dno from db2test.emp3 e3 where e5.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e4.dno = e2.dno group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                   where e.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 e5.name = e3.mgrname and
       e3.dno in (select dno from db2test.emp2 e2
                  where e4.dno = e2.dno group by dno having
                  e2.dno in (select dno from db2test.emp  e1
                   where e.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: 09";
-- "cleanup";
drop table db2test.origemp;
drop table db2test.emp6;
drop table db2test.emp5;
drop table db2test.emp4;
drop table db2test.emp3;
drop table db2test.emp2;
drop table db2test.emp;
drop table db2test.dept;
drop table db2test.origdept;

rollback;
-- "cself303.clp ENDED";

-- #########################################################################
-- # TESTCASE NAME    : cself304.sql
-- # LINE ITEM        : Self-referencing subqueries
-- # COMPONENT(S)     : SQN and SQR
-- # 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 3-way cycle to parent
-- #########################################################################
-- "START OF TESTCASE: cself304.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), dmgrname char(10));
create table db2test.origdept (c0 int, dno char(3) not null primary key,
   dname char(10), dmgrname char(10));
insert into db2test.dept values (1, 'K55', 'DB', 'JOHN');
insert into db2test.dept values (2, 'K52', 'OFC', 'ROBIN');
insert into db2test.dept values (3, 'K51', 'CS', 'ASHOK');
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,
  mgrname char(10) references db2test.emp on delete cascade,
  dno char(3));
insert into db2test.emp2 select * from db2test.emp;
alter table db2test.dept add constraint dmgr foreign key (dmgrname)
  references db2test.emp2 on delete cascade;

commit;
-- "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.emp2 e2
   where e2.dno in (select dno from db2test.dept d
   where dmgrname = 'JOHN'));

delete from db2test.emp where dno in (select dno from db2test.emp2 e2
   where e2.dno in (select dno from db2test.dept d
   where dmgrname = 'JOHN'));
select * from db2test.dept order by dno, dname, dmgrname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
alter table db2test.dept drop constraint dmgr;
commit;
-- "END OF TESTUNIT: 02";

-- ************************************************************************
-- TESTUNIT         : 03
-- DESCRIPTION      : same as 02, but with correlation to top table
-- 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;
insert into db2test.dept select * from db2test.origdept;
insert into db2test.emp select * from db2test.origemp;
insert into db2test.emp2 select * from db2test.origemp;
alter table db2test.dept add constraint dmgr foreign key (dmgrname)
  references db2test.emp2 on delete cascade;

commit;
select * from db2test.emp e where dno in (select dno from db2test.emp2 e2
   where e2.dno in (select dno from db2test.dept d
   where d.dmgrname = 'john' and e2.name = d.dmgrname));
delete from db2test.emp where dno in (select dno from db2test.emp2 e2
   where e2.dno in (select dno from db2test.dept d
   where mgrname = 'john' and e2.name = d.dmgrname));
select * from db2test.dept order by dno, dname, dmgrname;
select * from db2test.emp order by dno, name, mgrname;
select * from db2test.emp2 order by dno, name, mgrname;
alter table db2test.dept drop constraint dmgr;
commit;

-- "END OF TESTUNIT: 03";

-- ************************************************