return 0
go
pro7 z1
go
/*8查询某系教师的基本情况。
要求显示如下信息：系部ID、系部名称、专业名称、教师名字、教师性别、教师职称。*/
create procedure pro8
@m_js varchar(20)
as
select a.xi_id,a.xi_name,b.zy_name,c.js_name,c.xb,c.zhiye
from xi a,zhuanye b,jiaoshi c
where b.zy_id=c.zy_id and a.xi_id=c.xi_id and @m_js=c.js_name
return 0
go
pro8 m1
go
/*具有统计和计算功能*/
/*1统计某系部教师总人数，男教师和女教师各多少名。*/
create procedure pro10
@m_xi varchar(20)
as
declare @nv int,@nan int
select @nv=0,@nan=0
select @nv=count(jiaoshi.xb)
from jiaoshi, xi where xb='g'and jiaoshi.xi_id=xi.xi_id and @m_xi=xi.xi_name
select @nan=count(jiaoshi.xb)
from jiaoshi, xi where xb='b'and jiaoshi.xi_id=xi.xi_id and @m_xi=xi.xi_name
select "总人数"=count(a.js_id) ,'女'=@nv,'男'=@nan
from jiaoshi a,xi b where a.xi_id=b.xi_id and @m_xi=b.xi_name
return 0
go
pro10 q1
go
/*2计算某专业教师的教学工作量（教师表，课程表，教师-课程表）*/
create procedure pro11
@m_zy char(8)
as
select '计算某专业教师的教学工作量'=sum(xs)
from jiaoshi a,kecheng b,jskc c
where @m_zy=a.zy_id and a.js_id=c.js_id and c.kc_id=b.kc_id
return 0
go
pro11 '0501'
go
/*3统计某学期授课的门数。(教师-课程表)*/
create procedure pro12
as
select '统计某学期授课的门数'=sum(ks) from jskc
return 0
go
pro12
go
/*4统计某学期课程的总学分。*/
create procedure pro13
as
select '统计某学期课程的总学分'=sum(cxf) from jskc
return 0
go
pro13
go
/*5统计某班级学生总人数。*/
create procedure pro14
@m_bj varchar(20)
as
select '统计某班级学生总人数'=count(b.bj_id) from banji a,xueshen b
where @m_bj=a.bj_name and a.bj_id=b.bj_id
return 0
go
pro14 '应用1'
go
/*6统计某专业学生总人数*/
create procedure pro15
@m_zy varchar(20)
as
select '统计某专业学生总人数'=count(b.zy_id) from zhuanye a,xueshen b
where @m_zy=a.zy_name and a.zy_id=b.zy_id
return 0
go
pro15 'z1'
go
/*7统计某个班级、某门课程的成绩分布情况。60分以下多少人、60-70分多少人,70-80分多少人、80-90分多少人、90以上多少人，各占班级总人数的百分比，并请给出直方图。*/
create procedure pro16
@m_bj varchar(20),@m_km char(8)
as
declare @ml float ,@m60 float ,@m70 float ,@m80 float ,@m90 float,@m100 int
select @ml=count(c.xuehao) from banji a,xueshen b ,xskc c
where @m_bj=a.bj_name and @m_km=c.kc_id and a.bj_id=b.bj_id and c.xuehao=b.xuehao and chengji<60
select @m60=count(c.xuehao) from banji a,xueshen b ,xskc c
where @m_bj=a.bj_name and @m_km=c.kc_id and a.bj_id=b.bj_id and c.xuehao=b.xuehao and chengji>=60 and chengji<70
select @m70=count(c.xuehao) from banji a,xueshen b ,xskc c
where @m_bj=a.bj_name and @m_km=c.kc_id and a.bj_id=b.bj_id and c.xuehao=b.xuehao and chengji>=70 and chengji<80
select @m80=count(c.xuehao) from banji a,xueshen b ,xskc c
where @m_bj=a.bj_name and @m_km=c.kc_id and a.bj_id=b.bj_id and c.xuehao=b.xuehao and chengji between 80 and 90
select @m90=count(c.xuehao) from banji a,xueshen b ,xskc c
where @m_bj=a.bj_name and @m_km=c.kc_id and a.bj_id=b.bj_id and c.xuehao=b.xuehao and chengji>90
select @m100=count(c.xuehao) from banji a,xueshen b ,xskc c
where @m_bj=a.bj_name and @m_km=c.kc_id and a.bj_id=b.bj_id and c.xuehao=b.xuehao
select 'm60以下'=@ml/@m100*100,'m60-70分'= @m60/@m100*100, 'm70-80分'=@m70/@m100*100, 'm80-90分'=@m80/@m100*100 , 'm90以'=@m90/@m100*100
return 0
go
pro16 '应用1','0201'
go
