SQL Server——从入门到放弃（11）-- 第五章： 数据库完整性 习题 + 存储过程

没想到啊，都已经到11了😂

今天的内容是第五章的课后习题，以及第八章存储过程的一部分习题。
（PS:在这是废话，题目都说了😛）
好了，现在开始练习

这里注意，因为在职工表中有来自部门表的外码—部门号，所以在建表时应该先建立部门表。

/*部门表*/
create table Dept
(
   Dno char(10)  primary key,  --主码
   Dname char(5),
   Mname char(5),
   Tel char(15),
)


/*职工表*/
create table Worker
(
   Wno char(10)  primary key,  --主码
   Wname char(5),
   Wage smallint,
   constraint C1 check(Wage<=60),  --定义约束。不超过60
   Job  char(5),
   Wpay char(5),
   Dno char(10),
   foreign key (Dno) references Dept(Dno)   --参照完整性

（1）SC表内容

建立分段表

drop table if exists Section
create table Section
(
    grade char(10),  --成绩分段
	num smallint,    --人数
)


insert into Section values('0-20','0')
insert into Section values('21-40','0')
insert into Section values('41-60','0')
insert into Section values('61-80','0')
insert into Section values('81-100','0')

select * from Section

定义存储过程

if (exists (select * from sys.objects where name = 'SCC'))
    drop procedure SCC
go
create procedure SCC

as
declare		/*定义变量*/
@0to20   smallint,                 
@21to40  smallint,
@41to60  smallint,
@61to80  smallint,
@81to100 smallint,
@CNO char(10);  

	select @CNO=Cno 
	from   Course 
	where  Cname='离散数学';

	select @0to20=count(*)
	from SC
	where Grade<=20 and Cno=@CNO
	
	select @21to40=count(*)
	from SC
	where Grade<=40 and Grade>=21 and Cno=@CNO

	select @41to60=count(*)
	from SC
	where Grade<=60 and Grade>=41 and Cno=@CNO
	
	select @61to80=count(*)
	from SC
	where Grade<=80 and Grade>=61 and Cno=@CNO

	select @81to100=count(*)
	from SC
	where Grade<=100 and Grade>=81 and Cno=@CNO

update Section set num=@0to20 where grade='0-20';
update Section set num=@21to40 where grade='21-40';
update Section set num=@41to60 where grade='41-60';
update Section set num=@61to80 where grade='61-80';
update Section set num=@81to100 where grade='81-100';

exec SCC
select * from Section

这执行结果。。。
咋和我想得不太一样嘞😓
在多次实验失败后，我参考了一下同学的代码

定义存储过程

if (exists (select * from sys.objects where name = 'SCC'))
    drop procedure SCC
go
create procedure SCC

as
begin
declare		/*定义变量*/
@0to20   smallint,                 
@21to40  smallint,
@41to60  smallint,
@61to80  smallint,
@81to100 smallint

	select @0to20=count(*)
	from SC,Course
	where Grade<=20 and SC.Cno=Course.Cno and Cname='离散'
	
	select @21to40=count(*)
	from SC,Course
	where (Grade<=40 and Grade>=21) and SC.Cno=Course.Cno and Cname='离散'

	select @41to60=count(*)
	from SC,Course
	where (Grade<=60 and Grade>=41)and SC.Cno=Course.Cno and Cname='离散'
	
	select @61to80=count(*)
	from SC,Course
	where (Grade<=80 and Grade>=61) and SC.Cno=Course.Cno and Cname='离散'

	select @81to100=count(*)
	from SC,Course
	where (Grade<=100 and Grade>=81)and SC.Cno=Course.Cno and Cname='离散'

update Section set num=@0to20 where grade='0-20';
update Section set num=@21to40 where grade='21-40';
update Section set num=@41to60 where grade='41-60';
update Section set num=@61to80 where grade='61-80';
update Section set num=@81to100 where grade='81-100';

end

本来我以为是忘加begin...end的缘故，后来加上之后发现还是不行，继续研究，找到一些不同，本来我的想法是定义一个CNO存储离散数学的课程号，之后只要对比相等就行了，但事实证明想法是错的。吸取同学代码的意见，多一条对比，执行结果正确。
但这两种方式是一个意思啊，我还是感觉我的想法是对的，应该是我代码描述有问题。

（2）统计平均成绩

建立平均成绩表

create table Avg_Grade
(Cno char(10),
Avg_grade float
);

if(exists(select * from sys.objects where name = 'CSSS'))
  drop procedure CSSS;
go
create procedure CSSS

as
begin
declare
  @AVG1 float,
  @AVG9 float;
select @AVG1 = avg(Grade) from SC
where Cno = 1

select @AVG9 = avg(Grade) from SC
where Cno = 9

insert into Avg_Grade VALUES(1,@AVG1);
insert into Avg_Grade VALUES(9,@AVG9);
end;

exec CSSS

select * from Avg_Grade;

（3）百分制改等级制

	alter table SC add Level char(2);  /*先添加一列*/
	
	if (exists (select * from sys.objects where name = 'CS'))
	    DROP PROCEDURE CS
	GO
	CREATE PROCEDURE CS
	AS
	 	update SC set Level='E' WHERE Grade<=60;
	 	update SC set Level='D' WHERE Grade<=70 and Grade>60;
		update SC set Level='C' WHERE Grade<=80and Grade>70;
		update SC set Level='B' WHERE Grade<=90and Grade>80;
		update SC set Level='A' WHERE Grade<=100and Grade>90;

exec CS
select * from SC

这里面个人感觉吃不消的就是 存储过程这一块，上次作业就让我很迷，这次有点改善，但还是不太熟，创建存储过程有基本固定的套路，但还是不太熟，还得继续练呐。