create database bai4

create table sv

(Masv char(10) primary key

,Tensv nvarchar(30) not null

,NS datetime

,Diachi nvarchar(30))

create table dt

(Madt char(10) primary key

,Tendt nvarchar(30) not null

,GVHD nvarchar(30))

create table sv_dt

(Madt char(10) not null

,Masv char(10) not null

,NTT nvarchar(30)

,KQ float not null

,constraint khoachinh primary key (Madt,Masv)

,constraint kn1 foreign key(Masv)references sv(Masv)

,constraint kn2 foreign key(Madt)references dt(Madt))

create view v1

as

select sv.*

from sv,sv_dt

where sv.Masv=sv_dt.Masv and Diachi=NTT

create view v2

as

select *

from sv

where Masv in (select Masv

               from sv_dt

               where KQ =(select max(KQ)

                          from sv_dt))

create view v3

as

select GVHD,count(Masv) as sosv

from dt,sv_dt

where dt.Madt=sv_dt.Madt

group by GVHD

create proc t1

@GVHD nvarchar(30)

as

select sv.*

from sv

where Masv in (select Masv

               from sv_dt

               where Madt in(select Madt

                             from dt

                             where GVHD=@GVHD))

exec t1 N'trần thị lan phương'

create proc t2

@NTT nvarchar(30)

as

select *

from sv

where Masv in(select Masv

              from sv_dt

              where NTT=@NTT)

exec t2 N'thái nguyên'

create proc t3

as

select *

from dt

where Madt not in(select Madt

              from sv_dt)

exec t3

create trigger kt1

on sv_dt

for insert

as

if (select KQ

    from inserted)<0 

begin

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

rollback tran

end

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

insert into sv_dt

values ('3','a1',N'thái nguyên',-2)

declare nhap cursor

for 

  select *

  from sv

  where Masv in(select Masv

                from sv_dt

                where KQ<5)

declare @Masv char(10),@tensv nvarchar(30),@NS datetime,@Diachi nvarchar(30)

open nhap

print cast(N'Mã SV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Năm sinh' as nchar(20))+cast(N'Giới tính' as nchar(10))+cast(N'mã lớp' as nchar(10))+cast(N'Mã tỉnh' as nchar(10))+cast('DTB' as nchar(10))

fetch next from nhap

into @Masv,@tensv,@NS,@Diachi

while @@fetch_status=0

begin

print cast(@Masv as nchar(10))+cast(@tensv as nchar(30))+cast(@NS as nchar(20))+cast(@Diachi as nchar(30))

fetch next from nhap

into @Masv,@tensv,@NS,@Diachi

end

close nhap

declare nhap1 cursor

for 

  select *

  from dt

  where Madt in(select Madt

                from sv_dt

                where KQ=(select max(KQ)

                          from sv_dt))

declare @Madt char(10),@tendt nvarchar(30),@GVHD nvarchar(30)

open nhap1

print cast(N'Mã DT' as nchar(10))+cast(N'Tên DT' as nchar(30))+cast(N'GVHD' as nchar(30))

fetch next from nhap1

into @Madt,@tendt,@GVHD

while @@fetch_status=0

begin

print cast(@Madt as nchar(10))+cast(@tendt as nchar(30))+cast(@GVHD as nchar(30))

fetch next from nhap1

into @Madt,@tendt,@GVHD

end

close nhap1