【2020-2021春学期】数据库作业13：第五章课后题

（1）T-SQL

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

（1）标准SQL

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

（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

（3）T-SQL

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;

（3）标准SQL

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;

-- 注意先后顺序
create table dept
(
	Sno char(5) primary key,
	Dname char(10),
	Mname char(20),
	Tel char(20)
);
create table staff
(
	Sno  char(10) primary key,
	Sname char(10),
	Sage smallint,
	constraint C1 check(Sage<=60),
	Spost char(10),
	Spay int,
	Dno char(5)
	foreign key (Dno) references dept(Sno)
);