--alter table employee add address varchar(20); 00228033
--select * from employee; 00229033
--update employee set address='chennai' where deptno=102; 00230034
--update employee set address='bangalore' where deptno=101 and 00231035
-- empno=003; 00232035
--update employee set address='mangalore'; 00233036
--select * from employee; 00240036
--create table employee1(empno integer not null, 00250037
--empname varchar(15),deptno int) in db61.ts61; 00260037
--insert into employee1 values(001,'charles',103); 00261038
--create unique index empidx1 on employee1(empno); 00262040
--alter table employee1 add primary key(empno); 00270040
--create table tab1(empno smallint not null,deptno smallint not null, 00280045
-- primary key(empno))in db61.ts61; 00281045
--create unique index tab1idx on tab1(empno); 00282045
--create table tab2(empno smallint not null,deptno smallint not null, 00283045
-- primary key(empno))in db61.ts61; 00284045
--create unique index tab2idx on tab2(empno); 00285045
--insert into tab1 values(101,110); 00286046
--insert into tab1 values(102,111); 00287046
--insert into tab1 values(103,112); 00288046
--insert into tab1 values(104,113); 00289046
--insert into tab2 values(101,114); 00290046
--insert into tab2 values(102,111); 00300046
--insert into tab2 values(103,114); 00310046
--insert into tab2 values(105,112); 00320046
--select * from tab1; 00330099
--select * from tab2; 00340099
--select tab1.*,tab2.* from tab1,tab2; 00350099
--select tab1.*,tab2.* from tab1 inner join tab2 00360099
-- on tab1.empno=tab2.empno; 00370099
--select tab1.*,tab2.* from tab1 left outer join tab2 00380099
-- on tab1.empno=tab2.empno; 00390099
--select tab1.*,tab2.* from tab1 right outer join tab2 00400099
-- on tab1.empno=tab2.empno; 00410099
--select tab1.*,tab2.* from tab1 full outer join tab2 00420099
-- on tab1.empno=tab2.empno; 00430099
--select * from tab1 union select * from tab2; 00431099
--select * from tab1 union all select * from tab2; 00432099
--select * from employee; 00440056
--select * from employee where deptno=102; 00450057
--select * from employee where deptno=102 or deptno=103; 00460058
--select * from employee where deptno=102 and deptno=103; 00470059
--create table parentdept1(deptno smallint not null primary key, 00480060
-- deptname varchar(10))in db61.ts61; 00490060
--create unique index prnt1 on parentdept1(deptno); 00500060
--create table childemp1(empno smallint not null primary key, 00510060
-- empname varchar(10), 00520060
-- deptno smallint, 00530060
-- salary decimal(7,2), 00540060
-- foreign key(deptno) references parentdept1 00550060
-- on delete cascade) in db61.ts61; 00560060
--create unique index cld1 on childemp1(empno); 00570060
--insert into parentdept1 values(110,'sales'); 00580061
--insert into parentdept1 values(111,'production'); 00590076
--insert into parentdept1 values(112,'r and d'); 00600076
--insert into parentdept1 values(113,'account'); 00610076
--insert into parentdept1 values(114,'stock'); 00620063
--insert into parentdept1 values(115,'accck'); 00621099
--insert into childemp1 values(1001,'giri',110,20000.0); 00630066
--insert into childemp1 values(1002,'senthil',112,25000.0); 00640076
--insert into childemp1 values(1003,'bala',111,30000.0); 00650076
--insert into childemp1 values(1004,'prabu',110,35000.0); 00660076
--insert into childemp1 values(1005,'shankar',113,40000.0); 00670076
--insert into childemp1 values(1006,'arun',114,45000.0); 00680076
--insert into childemp1 values(1007,'vijay',112,50000.0); 00690076
--insert into childemp1 values(1008,'charles',112,30000.0); 00700076
--insert into childemp1 values(1009,'char',115,30000.0); 00701099
--insert into childemp1 values(1010,'ar',115,30000.0); 00702099
--delete from parentdept1 where deptno=115; 00703099
--delete from childemp1 where deptno=112; 00704099
--select * from parentdept1; 00710099
--select * from childemp1; 00720099
--delete from parentdept1 where deptno=110; 00730079
--create table parentdept2(deptno smallint not null primary key, 00740082
-- deptname varchar(10))in db61.ts61; 00750082
--create unique index prnt2 on parentdept2(deptno); 00760082
--create table childemp2(empno smallint not null primary key, 00770082
-- empname varchar(10), 00780082
-- deptno smallint, 00790082
-- salary decimal(7,2), 00800082
-- foreign key(deptno) references parentdept2 00810082
-- on delete restrict) in db61.ts61; 00820082
--create unique index cld2 on childemp2(empno); 00830082
--insert into parentdept2 values(110,'sales'); 00840083
--insert into parentdept2 values(111,'production'); 00850083
--insert into parentdept2 values(112,'r and d'); 00860083
--insert into parentdept2 values(113,'account'); 00870083
--insert into parentdept2 values(114,'stock'); 00880083
--select * from parentdept2; 00881083
--insert into childemp2 values(1001,'giri',110,20000.0); 00890084
--insert into childemp2 values(1002,'senthil',112,25000.0); 00900084
--insert into childemp2 values(1003,'bala',111,30000.0); 00910084
--insert into childemp2 values(1004,'prabu',110,35000.0); 00920084
--insert into childemp2 values(1005,'shankar',113,40000.0); 00930084
--insert into childemp2 values(1006,'arun',114,45000.0); 00940084
--insert into childemp2 values(1007,'vijay',112,50000.0); 00950084
--insert into childemp2 values(1008,'charles',112,30000.0); 00960084
--select * from childemp2; 00970084
--delete from parentdept2 where deptno=110; 00980099
--create table parentdept3(deptno smallint not null primary key, 00990086
-- deptname varchar(10))in db61.ts61; 01000086
--create unique index prnt3 on parentdept3(deptno); 01010086
--create table childemp3(empno smallint not null primary key, 01020086
-- empname varchar(10), 01030086
-- deptno smallint, 01040086
-- salary decimal(7,2), 01050086
-- foreign key(deptno) references parentdept3 01060086
-- on delete set null) in db61.ts61; 01070086
--create unique index cld3 on childemp3(empno); 01080086
--insert into parentdept3 values(110,'sales'); 01090099
--insert into parentdept3 values(115,'sale'); 01091099
--insert into parentdept3 values(111,'production'); 01100087
--insert into parentdept3 values(112,'r and d'); 01110087
--insert into parentdept3 values(113,'account'); 01120087
--insert into parentdept3 values(114,'stock'); 01130087
--select * from parentdept3; 01140087
--insert into childemp3 values(1001,'giri',110,20000.0); 01150088
--insert into childemp3 values(1002,'senthil',112,25000.0); 01160088
--insert into childemp3 values(1003,'bala',111,30000.0); 01170088
--insert into childemp3 values(1004,'prabu',110,35000.0); 01180088
--insert into childemp3 values(1005,'shankar',113,40000.0); 01190088
--insert into childemp3 values(1006,'arun',114,45000.0); 01200088
--insert into childemp3 values(1007,'vijay',112,50000.0); 01210088
--insert into childemp3 values(1008,'charles',112,30000.0); 01220088
--insert into childemp3 values(1009,'asdrles',115,30000.0); 01221099
--insert into childemp3 values(1010,'cfgfles',115,30000.0); 01222099
--select * from childemp3; 01230099
--delete from parentdept3 where deptno=115; 01240099
--select * from childemp3; 01250099
--insert into empdet values(110,'giri'); 01251099
--insert into empdet values(111,'charles'); 01252099
--insert into empdet values(112,'prabu'); 01252199
--insert into empdet(eno) values(160); 01253095
--delete from empdet; 01254099
--update empdet set ename='cccc' where eno=112; 01255099
--select * from empdet; 01260099
--grant select,update on empdet to mapl687; 01270099
--select * from sysibm.systrigger where creator="mapl701"; 01280099
--drop trigger trigemp; 01290099
--create trigger trigemp1 after update on empdet 01300099
--referencing new as emptbl 01310099
--for each row mode db2sql 01320099
--when(emptbl.eno = 112) 01330099
--signal sqlstate '75002' ('record updated'); 01340099
-- CREATE TRIGGER TRIGG1 NO CASCADE BEFORE UPDATE ON EMPDET 01340199
-- REFERENCING OLD AS OPDTBL 01340299
-- FOR EACH ROW MODE DB2SQL 01340399
-- WHEN(OPDTBL.ENO=110) 01340499
-- SIGNAL SQLSTATE '75003'('UPDATION DENIED'); 01340599
--drop trigger trigg1; 01340699
--update empdet set ename='cccc' where eno=110; 01341099
--select * from empdet; 01350099
********************************************************************* 01360099
No comments:
Post a Comment