--cau 12

create proc xoa @a varchar(30), @b float as

declare @id int;

select RN = @id from Student where

Name like @a;

if (@a="*")

begin

delete from StudentSubject

delete from Student

end

else

begin

if not exists (select m.* from Student m, Subject n, StudentSubject mn where m.RN = mn.RN and n.sID = mn.sID and mn.mark>@b and m.RN = @id)

begin

delete from StudentSubject where RN = @id;

delete from Student where RN = @id;

end;

end;

--cau 13

select top 3 row_number() over(order by Mark desc) [Rank] ,a.RN, a.Name, Mark, b.SName, Date into Top3 from Student a, Subject b, StudentSubject c

where a.RN = c.RN and b.sID = c.sID;

go

select * from Top3;

--cau 14

--Cau ve trigger

create trigger tgTop3

on StudentSubject for insert, update

as

delete from top3;

insert into top3 select top 3 row_number() over(order by Mark desc) [Rank] ,a.RN, a.Name, Mark, b.SName, Date from Student a, Subject b, StudentSubject c where a.RN = c.RN and b.sID = c.sID;

go