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';