Bai11 Quan liSinh vien diem thi
create database bai11
create table sv
(Masv char(10) primary key
,Tensv nvarchar(30) not null
,NS datetime
,Diachi nvarchar(30)
,DVHT int)
create table mon
(MaM char(10) primary key
,TenM nvarchar(30) not null)
create table diem
(MaM char(10) not null
,Masv char(10) not null
,phach char(10)
,diem float
,constraint khoachinh primary key (MaM,Masv)
,constraint MaM_khoangoai foreign key(MaM)references mon(MaM)
,constraint Masv_khoangoai foreign key(Masv)references sv(Masv))
create view v1
as
select *
from sv
where Masv in(select Masv
from diem d
where diem=(select max(diem)
from diem
where d.MaM=diem.MaM and MaM in (select MaM
from mon
where TenM=N'cơ sở dữ liệu')))
create view v2
as
select *
from sv
where diachi=N'cao bằng'
create view v3
as
select *
from sv
where year(NS)<1992
create proc p1
@TenM nvarchar(30)
as
select *
from sv
where Masv in (select Masv
from diem
where diem<5 and MaM in(select MaM
from mon
where tenM=@TenM))
exec p1 N'cơ sở dữ liệu'
create proc p2
@diem float
as
select *
from sv
where Masv in (select Masv
from diem
where diem<@diem and maM in(select MaM
from Mon
where TenM=N'cơ sở dữ liệu'))
exec p2 6
create trigger ktndl
on diem
for insert
as
if (select diem
from inserted)<0
or (select diem
from inserted)>10
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 diem
values ('a2','103','hfla',11)
declare nhap cursor
for
select sv.Masv,Tensv,TenM,Diem
from sv,diem d,mon
where sv.Masv=d.Masv and d.MaM=mon.MaM and diem=(select Max(diem)
from diem
where d.MaM=diem.MaM)
declare @Masv char(10),@Tensv nvarchar(30),@tenm nvarchar(30),@diem float
open nhap
print cast(N'Mã SV' as nchar(10))+cast(N'Tên SV' as nchar(30))+cast(N'Tên Môn' as nchar(30))+cast(N'Điểm' as nchar(10))
fetch next from nhap
into @Masv,@Tensv,@tenm,@diem
while @@fetch_status=0
begin
print cast(@Masv as nchar(10))+cast(@Tensv as nchar(30))+cast(@Tenm as nchar(30))+cast(@diem as nchar(10))
fetch next from nhap
into @Masv,@Tensv,@tenm,@diem
end
close nhap
Bạn đang đọc truyện trên: AzTruyen.Top