第五章
6.
create table Staff --职工表
(Snum char(15),
Sname char(10),
Sage int,
Spo char(5),
Swa int,
Sde char(5),
constraint Staffkey primary key(Snum),
constraint staff_foreign foreign key(Snum) references Staff(Snum),
constraint staff_sage check(sage<=60)
);
create table Dep --部门表
(Dnum int,
Dname char(10),
Dmag char(10),
Dcall char(13),
constraint Depkey primary key(Dnum),
constraint Dep_foreign foreign key(Dnum) references Dep(Dnum)
);
第八章
2.
(1)
CREATE PROCEDURE m_grade
As
declare @0to60 int,
@60to70 int,
@70to80 int,
@80to90 int,
@90to100 int,
@CNO char(8);
select @CNO=cno from Course where Cname='离散';
select @0to60=count(*) from sc where grade<60 and Cno=@CNO
update li_Grade set num=@0to60 where score='[0,60)';
select @60to70=count(*) from sc where grade>=60 and grade<70 and Cno=@CNO
update li_Grade set num=@60to70 where score='[60,70)';
select @70to80=count(*) from sc where grade>=70 and grade<80 and Cno=@CNO
update li_Grade set num=@70to80 where score='[70,80)';
select @80to90=count(*) from sc where grade>=80 and grade<90 and Cno=@CNO
update li_Grade set num=@80to90 where score='[80,90)';
select @90to100=count(*) from sc where grade>=90 and grade<100 and Cno=@CNO
update li_Grade set num=@90to100 where score='[90,100)';
(2)
create procedure avg_score(@c_name char(10))
as
select avg(Grade)
from SC where Cno=
(select Cno from Course where Cname=@c_name);
(3)
create procedure score_level
as
begin
update SC set G_level='A' where Grade>=90;
update SC set G_level='B' where Grade>=80 and Grade<90;
update SC set G_level='C' where Grade>=70 and Grade<80;
update SC set G_level='D' where Grade>=60 and Grade<80;
update SC set G_level='E' where Grade<60;
end
这个第二题比第一题难多了,存储过程没学好,各种百度+翻书才勉强写出来,没有报错但我没有实际插表去测试。