第五章课后题

数据库完整性

6、假没有下面两个关系模式:
职工(职工号，姓名，年龄，职务，工资，部门号)，其中职工号为主码；
部门(部门号，名称，经理名，电话)，其中部门号为主码。
用SOL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义:
(1)定义每个模式的主码; (2) 定义参照完整性; (3) 定义职工年龄不得超过60岁。

--部门(部门号，名称，经理名，电话)
CREATE TABLE DEPARTMENT
(Deptno CHAR(15) PRIMARY KEY,--主码
Dname CHAR(20),
Manager CHAR(15),
Telephon CHAR(15)
)
--职工(职工号，姓名，年龄，职务，工资，部门号)
CREATE TABLE  STAFF
(Stno CHAR(15) PRIMARY KEY,--主码
Stname CHAR(20),
Stage INT CHECK(Stage <= 60),--职工年龄不得超过60岁。
Job CHAR(15),
Salary INT,
Deptno CHAR(15)
FOREIGN KEY(Deptno) REFERENCES DEPARTMENT(Deptno)--参照完整性
)

第八章作业

第八章作业：习题二 2、对学生-课程数据库编写存储过程，完成下述功能:
(1) 统计离散数学的成绩分布情况，即按照各分数段统计人数。
(2)统计任意一门课的平均成绩。 建立平均成绩表。
(3)将学生选课成绩从百分制改为等级制(即A、B、C、D、E)。

（1）统计离散数学的成绩分布情况，即按照各分数段统计人数。

①先建个表
②建个有统计的存储过程

CREATE TABLE Grade(
	Score char(10),
	Num int
);
insert into gradesum_8(rank,total) values(''[0,60)'',NULL);
insert into gradesum_8(rank,total) values('[60,70)',NULL);
insert into gradesum_8(rank,total) values('[70,80)',NULL);
insert into gradesum_8(rank,total) values('[80,90)',NULL);
insert into gradesum_8(rank,total) values('[90,100]',NULL);

create procedure Proc_rank
as
begin  
   	declare	/*定义变量*/
	@Cno char(4),
	@less60 int, -- [0,60)
	@less70 int,-- [60,70)
	@less80 int, -- [70,80)
	@less90 int, -- [80,90)
	@less100 int -- [90,100]
	

	select @Cno = Cno from Course where Cname = '离散数学';
	select @less60 = count(*) from SC where Grade < 60 and Cno = @Cno;
	select @less70 = count(*) from SC where Grade >= 60 and Grade < 70 and Cno = @Cno;
	select @less80 = count(*) from SC where Grade >= 70 and Grade < 80 and Cno = @Cno;	
	select @less90 = count(*) from SC where Grade >= 80 and Grade < 90 and Cno = @Cno;
	select @less100 = count(*) from SC where Grade >= 90 and Grade < 100 and Cno = @Cno;
	

	update Grade set num = @less60 where score = '[0,60)';
	update Grade set num = @less70 where score = '[60,70)';
	update Grade set num = @less80 where score = '[70,80)';
    update Grade set num = @less90 where score = '[80,90)';
	update Grade set num = @less100 where score = '[90,100]';
end;

--分开执行

exec Proc_rank; 
select * from SC where Cno = 8;
select * from rank

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

①建表

CREATE TABLE Avg_Grade
(
 Cname char(10), --课程名称
 Cno int, --课程号
 A_Gra int --该课程的平均成绩
)

②存储过程

create procedure Cal_avg(
@Cno int)
as
declare
@Cpo int,
@grade int,
@Cname char (10);
select @Cpo = Cno   from Course
where Cno = @Cno; --对应这门课的
select  @Cname =Cname  from Course
where Cno = @Cno;
if @Cpo is null
begin
print '未录入'+@Cno+'这门课'
rollback;
return 
end;
select @grade = avg(Grade) from Sc
where Cno =@Cpo;
update Avg_Grade set A_Gra =@grade where Cno =@Cpo and Cname =@Cname;

（3）将学生选课成绩从百分制改为等级制 (即A、 B、C、D、E)

create procedure Value_Grade
(@grade int,@Cno int,@Sno char(10))
as
declare 
@A char(5),
@B char(5),
@C char(5),
@D char(5),
@E char(5),
@Cpo int,
@Spo char(10);
select @Cpo = Cno from SC where @Cno =Cno;
if @Cpo is null
begin
print '没有该课程'
rollback;
return
 end;
 select @Spo = Sno from SC where @Sno =Sno;
 if @Cpo is null
begin
print '没有该学生'
rollback;
return
 end;
 
 update SC set Val ='A' where @grade >=90;
 update SC set Val ='B' where @grade >=80 and @grade <=89;
 update SC set Val ='C' where @grade >=70 and @grade <=79;
 update SC set Val ='D' where @grade >=69 and @grade <=60;
 update SC set Val ='E' where @grade <60;