第五章作业

6.先建表

create table 部门(部门号 char(9) primary key ,
				  名称 char(9),
				  经理名 char(9),
				  电话 char(11));
create table 职工(
					职员号 char(9) primary key,
					姓名   char(9) ,
					年龄   smallint check (年龄<=60),
					职务   char(9),
					工资   smallint,
					部门号 char (9),
					foreign key(部门号) references 部门(部门号));

存储过程解题

create procedure Proc_GetMathStatus
@Cname char(9)
as
begin transaction trans
	declare 
	@less60 int,
	@more60less70 int ,
	@more70less80 int,
	@more80less90 int,
	@more90 int,
	@Cno char(4),
	@cnt int
	select @Cno=Cno from Course where Cname=@Cname
	select @cnt=count(*) from sc where Cno=@Cno;
	if @cnt=0
	begin
		print '这个课没有学生或者没有这个课'
		rollback transaction trans;
		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
	commit transaction trans;

根据题意建立表格

Create Table SC(
Sno nChar(12),
Cno nChar(12),
Cname nChar(12),
Sname nChar(12),
Ssex nchar(2) check(Ssex in('男','女')),
Primary Key(Sno, Cno),
Grade SmallInt Check(Grade >= 0 And Grade <= 100)
);
Insert Into SC Values('20191102001', '1', '离散数学', '张三', '男', 91);
Insert Into SC Values('20191102001', '2', '高等数学','张三', '男', 93);
Insert Into SC Values('20191102001', '3', '线性代数','张三', '男', 90);
Insert Into SC Values('20191102001', '4', '运筹学','张三', '男', 78);
Insert Into SC Values('20191102002', '1', '离散数学', '李四', '女', 51);
Insert Into SC Values('20191102002', '2', '高等数学','李四', '女', 76);
Insert Into SC Values('20191102002', '3', '线性代数','李四', '女', 66);
Insert Into SC Values('20191102002', '4', '运筹学','李四', '女', 99);
Insert Into SC Values('20191102003', '1', '离散数学', '王五', '男', 80);
Insert Into SC Values('20191102003', '2', '高等数学','王五', '男', 87);
Insert Into SC Values('20191102003', '3', '线性代数','王五', '男', 84);
Insert Into SC Values('20191102003', '4', '运筹学','王五', '男', 78);
Insert Into SC Values('20191102004', '1', '离散数学', '赵六', '女', 75);
Insert Into SC Values('20191102004', '2', '高等数学','赵六', '女', 74);
Insert Into SC Values('20191102004', '3', '线性代数','赵六', '女', 73);
Insert Into SC Values('20191102004', '4', '运筹学','赵六', '女', 77);

(1)统计离散数学的成绩分布情况,即按照各分数段统计人数。

Select
count(case when Grade between 90 and 100 And Cname = '离散数学' then 1 end) as 优秀,
count(case when Grade between 80 and 89 And Cname = '离散数学' then 1 end) as 良好,
count(case when Grade between 60 and 79 And Cname = '离散数学' then 1 end) as 及格,
count(case when Grade < 60 And Cname = '离散数学' then 1 end)  as 不及格
from SC

(2)统计任意一门课的平均成绩。

Select 
AVG(Case When Cname = '离散数学' Then Grade End) As 离散数学平均成绩,
AVG(Case When Cname = '高等数学' Then Grade End) As 高等数学平均成绩,
AVG(Case When Cname = '线性代数' Then Grade End) As 线性代数平均成绩,
AVG(Case When Cname = '运筹学' Then Grade End) As 运筹学平均成绩
From SC

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

Select 
Sno,
Cno,
Cname,
Sname,
Ssex,
Case When Grade Between 90 And 100 Then 'A'
When Grade Between 80 And 89 Then 'B'
When Grade Between 70 And 79 Then 'C'
When Grade Between 60 And 69 Then 'D'
When Grade Between 0 And 59 Then 'E'
End As 等级
From SC