create database bai8

create table sinhvien

(MaSV char(10) primary key

,TenSv nvarchar(30) not null

,NS datetime

,GT bit

,Que nvarchar(30))

create table monhoc

(MaM char(10) primary key

,TenM nvarchar(30) not null

,DVHT int)

create table diem

(MaM char(10) not null

,MaSV char(10) not null

,Diemthi float

,constraint khoachinh primary key (MaM,MaSv)

,constraint MaM_khoangoai foreign key(MaM)references monhoc(MaM)

,constraint MaSV_khoangoai foreign key(MaSV)references sinhvien(MaSV))

create view tonghop

as

select *

from sinhvien

where Masv in(select Masv

              from diem d

              where diemthi=(select max(diemthi)

                             from diem 

                             where d.MaM=diem.MaM and MaM in(select MaM

                                                             from monhoc

                                                             where TenM=N'cơ sở dữ liệu')))

create proc dsthilai

@TenM nvarchar(30)

as

select *

from sinhvien

where MaSv in (select MaSV

               from diem

               where diemthi<5 and MaM in (select MaM

                                        from monhoc

                                        where TenM=@TenM))

exec dsthilai N'lý thuyết mạch'

declare nhap cursor

for 

  select m.MaM,TenM,DVHT,sum(diemthi)/count(diem.MaM) as TB

  from monhoc m,diem

  where m.MaM=diem.MaM

  group by m.MaM,TenM,DVHT

declare @MaM char(10),@TenM nvarchar(30),@DVHT int,@TB float

open nhap

print cast(N'Mã Môn' as nchar(10))+cast(N'Tên Môn' as nchar(30))+cast(N'DVHT' as nchar(20))+cast(N'Trung bình' as nchar(10))

fetch next from nhap

into @MaM,@TenM,@DVHT,@TB

while @@fetch_status=0

begin

print cast(@MaM as nchar(10))+cast(@TenM as nchar(30))+cast(@DVHT as nchar(25))+cast(@TB as nchar(10))

fetch next from nhap

into @MaM,@TenM,@DVHT,@TB

end

close nhap