数据库学习13 - 第五章作业

第2题 .

标准SQL：
(1)
create procedure divide_rank
as
	declare score_90_100 int,
			score_80_90 int,
			score_70_80 int,
			score_60_70 int,
			score_0_60 int,
			score int;
			
	select score_90_100=count(*) 
	from SC,Course 
	where SC.Cno=Course.Cno and Grade>=90 and Grade<=100 and Cname='离散数学' ;
	
	select score_80_90 =count(*) 
	from SC,Course 
	where SC.Cno=Course.Cno and Grade>=80 and Grade<90 and Cname='离散数学' ;
	
	select score_70_80 =count(*) 
	from SC,Course 
	where SC.Cno=Course.Cno and Grade>=70 and Grade<80 and Cname='离散数学' ;
	
	select score_60_70 =count(*) 
	from SC,Course 
	where SC.Cno=Course.Cno and Grade>=60 and Grade<70 and Cname='离散数学' ;
	
	select score_0_60  =count(*) 
	from SC,Course 
	where SC.Cno=Course.Cno and Grade>=0 and Grade<60 and Cname='离散数学' ;
	
	select score_90_100 as '[90,100]',
		   score_80_90  as '[80,90)',
		   score_70_80  as '[70,80)',
		   score_60_70  as '[60,70)',
		   score_0_60   as '[0,60)';

(2)
标准SQL：

create procedure AVG_Cscore(course_name char(10))
as
	select avg(Grade) 
	from SC,Course 
	where SC.Cno=Course.Cno and Course.Cname=course_name

T-SQL：

create procedure AVG_Cscore
	@course_name char(10)
as
	select avg(Grade) 
	from SC,Course 
	where SC.Cno=Course.Cno and Course.Cname=@course_name;
	
(3)
alter table SC add Grade_level char(1);
go
create procedure score_to_level()
as
	update SC set Grade_level='A' where Grade>=90 and Grade<=100;
	update SC set Grade_level='B' where Grade>=80 and Grade<90;
	update SC set Grade_level='C' where Grade>=70 and Grade<80;
	update SC set Grade_level='D' where Grade>=60 and Grade<70;
	update SC set Grade_level='E' where Grade>=0 and Grade<60;

第6题 .

部门表：

create table Dept
(Deptno char(9),
 Dename char(10),
 Demanager char(10),
 Detel char(11),
 constraint  DeptKey primary key (Deptno), //定义主码
);

职工表：

create table Staff
(Snum char(9),
 Sname char(10),
 Sage char(4) check (Sage<=60),			  //职工年龄不超过60岁
 Swork char(10),
 Sal int,
 Deptno char(9),
 constraint StaffKey primary key (Snum),  //定义主码
 constraint StaKey foreign key(Deptno) references Dept(Deptno)
 										 //定义参照完整性
);

说实话，T-SQL还真不怎么会用，以至于例题有的都做不出来，还要靠同学的表验证，之后要更加强这方面的练习。
…………………………………………………………………………….

以上就是文章全部内容，感谢阅读。