作业14

建立apartment表
create table apartment
(
ano smallint primary key,
aname char(8),
mname char(10),
telep char(13)
);
create table worker
(
wno char(8) primary key,
wname char(8),
wage smallint check(wage<=60),
wsalary smallint,
wano smallint
foreign key(wano)references apartment(ano)
);
2.
–建立分段统计表
create table Section
(
grade char(10), /成绩分段/
num int /人数/
);
insert into Section values(‘0-60’,0);
insert into Section values(‘61-80’,0);
insert into Section values(‘81-100’,0);

if(exists(select *from sys.objects where name=‘statistic’))
drop procedure statistic
go
create procedure statistic
as
begin
declare
@first_s int,
@second_s int,
@third_s int,
@ssno char(4)

select @ssno=Cno 

from Course
where Cname=‘离散数学’;/获取离散数学课程号/
select @first_s=count(*)
from sc
where grade>=80 and grade<100 and cno=@ssno

select @second_s=count(*)

from sc
where grade>=60 and grade<80 and cno=@ssno

select @third_s=count(*)

from sc
where grade>0 and grade<60 and cno=@ssno

update section SET Num=@third_s where grade='0-60';

update section SET Num=@second_s where grade=‘61-80’;
update section SET Num=@first_s where grade=‘81-100’;
end
exec statistic
select * from section

（2).
create table avg_s
(
cno char(20),
cname char(20),
ascore float
)
insert into avg_s values(‘1’,‘数据库’,0)
insert into avg_s values(‘2’,‘数学’,0)
insert into avg_s values(‘3’,‘离散数学’,0)

if(exists(select * from sys.objects where name=‘Statistic’))
drop procedure Statistic
go
create procedure Statistic
as
begin
declare
@avg1 float,
@avg2 float,
@avg3 float
select @avg1 = avg(grade)
from sc
where cno=‘1’
update avg_s set ascore=@avg1
where cno=‘1’
select @avg2 = avg(grade)
from sc
where cno=‘2’
update avg_s set ascore=@avg2
where cno=‘2’
select @avg3 = avg(grade)
from sc
where cno=‘3’
update avg_s set ascore=@avg3
where cno=‘3’
exec Statistic
(3).
alter table sc add NewGrade char(1);
if(exists(select * from sys.objects where name=‘Statistic’))
drop procedure Statistic
go
create procedure Statistic
as
update sc set NewGrade=‘D’ where grade<60;
update sc set NewGrade=‘C’ where grade>=60 and grade<70;
update sc set NewGrade=‘B’ where grade>=70 and grade<80;
update sc set NewGrade=‘A’ where grade>=80;