第五章习题

第五章
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

这个第二题比第一题难多了，存储过程没学好，各种百度+翻书才勉强写出来，没有报错但我没有实际插表去测试。