第五章课后习题(以及存储过程习题)

 
  开心的打开SQL Server，结果弹出来下面提示信息，我以为是我打开的姿势不对enmmm，于是乎又打开啦一遍，好家伙，不出所料，还是这样，我可真是太开心啦。

裂开，数据库连不上啦，能怎么办那，我还能怎么办呐，哎！！！那就让百度来救救孩子吧

原来是启动这个就好啦。哎。

  下面开始做题吧，一共有两道题，第五章一道题，第八章一道题，第五章还好，就是对两个表进行定义，用SQL语句创建，第八章涉及到储存过程的创建，有点小难。

  第五章章主要是讲啦与完整性定义和触发器的SQL语句。

直接看SQL语句吧，这个也不是很难，如下：

create table Dept
		(Deptno char(15),
		 Deptname char(20),
		 Manager char(10),
		 PhoneNumber char(12),
		 constraint PK_SC primary key (Deptno)
		 );
create table EMP
		(Empno char(15) primary key,
		 Ename char(10),
		 Age int,
		 constraint C1 check(Age<=60 and Age>0),
		 Job char(9),
		 Sal float,
		 Deptno char(15),
		 constraint FK_DEPTNO
		 foreign key(Deptno)
		 references Dept(Deptno)
		);

  也可以直接在列后面直接写上对应的列级完整性约束条件，也可以通过完整性约束命名子句定义。

  第八章是存储过程和函数的定义和使用，这一块是比较难的。

好在之前做过相关的例题，感觉不是那么糟糕！

  我们之前建立的没有离散数学这个课，我们可以往里面SC表插入相关的信息，并且在Course表也插入相关的信息，重点不是这个，是建立存储过程，建立完，先查询其他已有的课程试试。

存储过程：

create procedure Proc_GetStatus
@Cname char(9)
as
begin
	declare 
	@less60 int,@more60less70 int,
	@more70less80 int,
	@more80less90 int,
	@more90 int,
	@Cno char(5),
	@cnt int
	select @Cno=Cno from Course      --选择出想选课程名的课程号，存在@Cno里面，用于后面select的选择语句
		where Cname=@Cname
	select @cnt=count(*) from SC    
		where Cno=@Cno;
	if @cnt=0                  --先判断是否有人选课
	begin
		print '没有学生选这个课'
		rollback
		return ;
	end 
	--开始挑选
	select @less60 = count(*)
			from SC
			where SC.Cno=@Cno and SC.Grade<60;
	select @more60less70 = count(*)
			from SC
			where SC.Cno=@Cno and SC.Grade>=60 and SC.Grade<70;
	select @more70less80 = count(*)
			from SC
			where SC.Cno=@Cno and SC.Grade>=70 and SC.Grade<80;
	select @more80less90 = count(*)
			from SC
			where SC.Cno=@Cno and SC.Grade>=80 and SC.Grade<90;
	select @more90 = count(*)
			from SC
			where SC.Cno=@Cno and SC.Grade>=90;
	--输出：
	print'[0,60):  'print @less60
	print'[60,70):  'print @more60less70
	print'[70,80):  'print @more70less80
	print'[80,90):  'print @more80less90
	print'[90,100]: 'print @more90
end;

测试一下：

exec Proc_GetStatus
		@Cname='数据库';

exec Proc_GetStatus
		@Cname='数学';

通过查看SC和Course表，结果是正确的，nice！！！

 
（2）统计任意一门课的平均成绩

首先，我们也是建立存储过程，如下：

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

create procedure Proc_GetAvgGrade
@Cname char(9)
as
begin transaction trans
	declare
	@Cno varchar(20),
	@Avg int
	select @Cno=Cno from Course where Cname=@Cname;
	if @Cno is null
	begin 
		print '这门课不存在'
		rollback;
		return ;
	end;
	select @Avg=avg(Grade) from SC where Cno=@Cno;
	print @Cname + '平均分'+ convert(varchar,@Avg)
    commit;
return ;

检测一下：

exec Proc_GetAvgGrade
     @Cname ='信息系统';
     
exec Proc_GetAvgGrade
     @Cname ='数据库';

通过查看表也是正确的。
 
（3）将学生选课成绩从百分制改为等级制

alter table SC 
add Level char(2); --给原先的SC表增加一列

if(exists (select * from sys.objects where name='Get_Level'))
	drop procedure Get_Level
go
create procedure Get_Level
as
	update SC set Level='A' where Grade>=90 and Grade<=100;
	update SC set Level='B' where Grade>=80 and Grade<90;
	update SC set Level='C' where Grade>=70 and Grade<80;
	update SC set Level='D' where Grade>=60 and Grade<70;
	update SC set Level='E' where Grade<60;

exec Get_Level --执行以下该存储过程

select *    --最后查询一下
from SC;

测试结果如下：
 
总结:

  相对来说，存储过程这一块还是有点小困难的，结合老师上课讲的和自己从百度上看的资料，还是可以看懂和理解的。不过刚开始出现的那个问题还是很突然的，幸好解决啦。