第五章课后作业 数据库课程

2、

2.1

以下为标准SQL：

create procedure divide_grade()
as
	declare score_90_100 int;
	declare	score_80_90 int;
	declare score_70_80 int;
	declare	score_60_70 int;
	declare	score_0_60 int;
	declare	score int;
	set score_90_100:=
		(select count(*) from SC where Grade>=90 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_80_90:=
		(select count(*) from SC where Grade>=80 and Grade<90 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_70_80:=
		(select count(*) from SC where Grade>=70 and Grade<80 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_60_70:=
		(select count(*) from SC where Grade>=60 and Grade<70 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set score_0_60:=
		(select count(*) from SC where Grade>=0 and Grade<60 and Cno=
			(select Cno from Course where 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)'; --显示	

以下为T-SQL：

create procedure divide_grade
as
begin
	declare @score_90_100 int,
			@score_80_90 int,
			@score_70_80 int,
			@score_60_70 int,
			@score_0_60 int,
			@score int;
	set @score_90_100=
		(select count(*) from SC where Grade>=90 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set @score_80_90=
		(select count(*) from SC where Grade>=80 and Grade<90 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set @score_70_80=
		(select count(*) from SC where Grade>=70 and Grade<80 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set @score_60_70=
		(select count(*) from SC where Grade>=60 and Grade<70 and Cno=
			(select Cno from Course where Cname='离散数学'));
	set @score_0_60=
		(select count(*) from SC where Grade>=0 and Grade<60 and Cno=
			(select Cno from Course where 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)'; --显示
end

2.2

以下为标准SQL：

create procedure aver_score(course_name varchar(10))
as
begin
	select avg(Grade) from SC where Cno=
		(select Cno from Course where Cname=course_name);
end

以下为T-SQL：

create procedure aver_score
	@course_name varchar(10)
as
	select avg(Grade) from SC where Cno=
		(select Cno from Course where Cname=@course_name);

2.3

以下为标准SQL：

create procedure score_to_level()
as
begin
	update SC set Grade_level='A' where Grade>=90;
	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<80;
	update SC set Grade_level='E' where Grade<90;
end

以下为T-SQL：

create procedure score_to_level
as
begin
	update SC set Grade_level='A' where Grade>=90;
	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<80;
	update SC set Grade_level='E' where Grade<90;
end

5、

该题T-SQL与标准SQL相同

create table 部门
(
	部门号 varchar(10),
	名称 varchar(10),
	经理名 varchar(10),
	电话 varchar(15)
	constraint dept_key primary key(部门号)
);

create table 职工
(
	职工号 varchar(10),
	姓名 varchar(10),
	年龄 int,
	职务 varchar(10),
	工资 int,
	部门号 varchar(10),
	constraint worker_key primary key(职工号),
	constraint worker_age_border check(年龄<=60),
	constraint worker_foreign_dept foreign key(部门号) references 部门(部门号)
);