Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop table briefing;
- drop table briefing_reg;
- create table briefing
- (
- sessionID char(4),
- bdate date not null,
- venus char (10)not null,
- constraint briefingPK primary key (sessionID)
- );
- -- A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.
- -- A foreign key with cascade delete can be created using either a CREATE TABLE statement or an ALTER TABLE statement.
- create table briefing_reg
- (
- regID char (4),
- empID char (4)not null unique,
- sessionID char(4),
- constraint regPK primary key(regID),
- constraint regfk foreign key(sessionID) references briefing(sessionID)
- on delete cascade
- on update cascade
- );
- -- insert, update, delete to test the casade operations
- insert into briefing values('b1','2018-10-1','A101');
- insert into briefing values('b2','2018-10-1','A101');
- insert into briefing values('b3','2018-10-1','A101');
- insert into briefing_reg values('r1','e1','b1');
- insert into briefing_reg values('r2','e2','b1');
- insert into briefing_reg values('r3','e3','b1');
- insert into briefing_reg values('r4','e4','b1');
- insert into briefing_reg values('r11','e11','b2');
- insert into briefing_reg values('r12','e12','b2');
- insert into briefing_reg values('r13','e13','b2');
- insert into briefing_reg values('r14','e14','b2');
- select * from briefing;
- select * from briefing_reg
- order by regID;
- -- join combine records from 2 tables
- select B.sessionID, BR.empID
- from briefing B, briefing_reg BR
- where B.sessionID = BR.sessionID;
- -- outer join is to retrieve records that are not
- -- included in inner (normal) join
- select B.sessionID, BR.empID
- from briefing B left join briefing_reg BR
- on B.sessionID = BR.sessionID;
- -- change the PR of briefing 'b1' to 'b100'
- update briefing
- set sessionID = 'b100'
- where sessionID = 'b1';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement