create database bai15

create table lop

(Malop char(10) primary key

,Tenlop nvarchar(30) not null)

create table sv

(Masv char(10) primary key

,Tensv nvarchar(30) not null

,NS datetime

,MaLop char(10)

,constraint ML_kn foreign key(Malop)references lop(Malop))

create table diem

(Masv char(10) not null

,HK nvarchar(10) not null

,Nam int not null

,Diem float

,constraint khoachinh primary key (Masv,hk,Nam)

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

create view v1

as

select sv.masv,tensv,HK,nam,diem

from sv,diem,lop

where sv.masv=diem.masv and sv.Malop=lop.malop and Tenlop=N'công nghệ thông tin' 

create view v2

as

select sv.masv,tensv,HK,nam,diem

from sv,diem,lop

where sv.masv=diem.masv and sv.Malop=lop.malop 

and diem=(select max(diem)

          from diem)

create proc t1

@Masv char(10)

as

select sv.masv,tensv,HK,nam,diem

from sv,diem,lop

where sv.masv=diem.masv and sv.Malop=lop.malop 

and sv.Masv=@masv

exec t1 'a1'

create proc t2

@malop char(10),@nam int

as

select sv.masv,tensv,HK,nam,tenlop,diem

from sv,diem,lop

where sv.masv=diem.masv and sv.Malop=lop.malop 

and nam=@nam and lop.malop=@malop

exec t2 '2',2009

create proc t3

@malop char(10)

as

select sv.masv,tensv,HK,nam,tenlop,diem

from sv,diem,lop

where sv.masv=diem.masv and sv.Malop=lop.malop 

and lop.malop=@malop

exec t3 '1'

create trigger kt1

on diem

for insert

as

if (select diem

    from inserted)<0 

or (select diem

    from inserted)>11

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 diem

values ('a2','2',2011,-2)

declare nhap cursor

for 

  select sv.masv,tensv,HK,nam,tenlop,diem

  from sv,diem,lop

  where sv.masv=diem.masv and sv.Malop=lop.malop 

  and nam=2009 and HK='1'

declare @Masv char(10),@tensv nvarchar(30),@HK nvarchar(10),@nam int,@tenlop nvarchar(30),@diem float

open nhap

print cast(N'Mã SV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Học kì' as nchar(20))+cast(N'Năm học' as nchar(10))+cast(N'Tên lớp' as nchar(30))+cast(N'Điểm' as nchar(10))

fetch next from nhap

into @Masv,@tensv,@HK,@nam,@tenlop,@diem

while @@fetch_status=0

begin

print cast(@Masv as nchar(10))+cast(@tensv as nchar(30))+cast(@HK as nchar(20))+cast(@nam as nchar(10))+cast(@tenlop as nchar(30))+cast(@diem as nchar(10))

fetch next from nhap

into @Masv,@tensv,@HK,@nam,@tenlop,@diem

end

close nhap

declare nhap1 cursor

for 

  select sv.masv,tensv,HK,nam,tenlop,diem

  from sv,diem,lop

  where sv.masv=diem.masv and sv.Malop=lop.malop 

  and nam=2009

declare @Masv char(10),@tensv nvarchar(30),@HK nvarchar(10),@nam int,@tenlop nvarchar(30),@diem float

open nhap1

print cast(N'Mã SV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Học kì' as nchar(20))+cast(N'Năm học' as nchar(10))+cast(N'Tên lớp' as nchar(30))+cast(N'Điểm' as nchar(10))

fetch next from nhap1

into @Masv,@tensv,@HK,@nam,@tenlop,@diem

while @@fetch_status=0

begin

print cast(@Masv as nchar(10))+cast(@tensv as nchar(30))+cast(@HK as nchar(20))+cast(@nam as nchar(10))+cast(@tenlop as nchar(30))+cast(@diem as nchar(10))

fetch next from nhap1

into @Masv,@tensv,@HK,@nam,@tenlop,@diem

end

close nhap1