数据库作业14：第五章： 数据库完整性 习题 + 存储过程

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)
 );

需要注意的是建表的顺序，由于外键在worker中，所以必须先建apartment表。

 --建立分段统计表
 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

注意：
我刚开始写的时候写了个这个

系统一直报ssno需要定义的错误，就一直找错，老也找不到，最后才发现把逗号写成了分号！！！
（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;

这一部分写到这感觉也没那么难，主要是代码一多，手敲的话难免出一些问题，尤其是那种不容易发现的，就包括上面的代码，有的就是敲完以后对着答案找都找不到的问题，但仍然有error，这就很气了。。。点像刚学C语言那个阶段了，后面只能更加细心认真，多犯几次错，吃亏要趁早，后面可能就不会这么别扭了。