create database bai10

create table tcd

(Matcd char(10) primary key

,Tentcd nvarchar(30) not null)

create table cd

(Macdv char(10) primary key

,Tencdv nvarchar(30) not null

,NS datetime

,NgayV datetime

,Matcd char(10))

create table kt

(Macdv char(10) not null

,Mskt char(10) not null

,lido nvarchar(max)

,nam int

,constraint khoachinh primary key (Macdv,Mskt)

,constraint MaM_khoangoai foreign key(Macdv)references cd(Macdv))

create view v1

as

select macdv,tencdv,NS

from cd

where Matcd in(select Matcd

              from tcd

              where tentcd=N'hệ thống thông tin')

create view v2

as

select macdv,tencdv,NS

from cd

where Macdv not in(select Macdv

                   from kt)

create view v3

as

select macdv,tencdv,NS

from cd

where Macdv in(select Macdv

               from kt

               where lido=N'đề tài cấp bộ')

create proc p1

@Tentcd nvarchar(30)

as

select *

from cd

where Matcd in (select Matcd

                from tcd

                where tentcd=@Tentcd)

exec p1 N'hệ thống thông tin'

create proc p2

@Tentcd nvarchar(30),@nam int

as

select *

from cd

where Matcd in (select Matcd

                from tcd

                where tentcd=@Tentcd) and macdv in(select Macdv

                                                   from kt

                                                   where nam=@nam)

exec p2 N'hệ thống thông tin',2010

create proc p3

@Tentcd nvarchar(30)

as

delete from cd

where Matcd in (select Matcd

                from tcd

                where tentcd=@Tentcd)

create trigger ktndl

on kt

for insert

as

if (select Nam

    from inserted)<0

begin

print N'dữ liệu nhập vào không hợp lệ'

rollback tran

end

else print N'dữ liệu nhập vào thành công'

insert into kt

values ('103','k4',N'đề tài cấp bộ',-3)

declare nhap cursor

for 

  select *

  from cd

  where Macdv not in (select Macdv

                      from kt

                      where nam=2010)

declare @Macdv char(10),@Tencdv nvarchar(30),@NS datetime,@NgayV datetime,@Matcd char(10)

open nhap

print cast(N'Mã CDV' as nchar(10))+cast(N'Tên CDV' as nchar(30))+cast(N'NS' as nchar(20))+cast(N'NgayV' as nchar(20))+cast(N'Mã TCD' as nchar(10))

fetch next from nhap

into @Macdv,@Tencdv,@NS,@NgayV,@Matcd 

while @@fetch_status=0

begin

print cast(@Macdv as nchar(10))+cast(@Tencdv as nchar(30))+cast(@NS as nchar(20))+cast(@NgayV as nchar(20))+cast(@Matcd as nchar(5))

fetch next from nhap

into @Macdv,@Tencdv,@NS,@NgayV,@Matcd

end

close nhap