Sql Bai1 Quan Li Ban Hang
create database bai8create 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 tonghopasselect *from sinhvienwhere 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)asselect *from sinhvienwhere 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 cursorfor 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,DVHTdeclare @MaM char(10),@TenM nvarchar(30),@DVHT int,@TB floatopen nhapprint 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 nhapinto @MaM,@TenM,@DVHT,@TBwhile @@fetch_status=0beginprint cast(@MaM as nchar(10))+cast(@TenM as nchar(30))+cast(@DVHT as nchar(25))+cast(@TB as nchar(10))fetch next from nhapinto @MaM,@TenM,@DVHT,@TBendclose nhap
Bạn đang đọc truyện trên: ZingTruyen.Xyz