计科09数据库技术实践第三部分Word文档格式.docx

1、 exec StudentInformation sname=李勇 （3） 查询指定系的男生人数，其中系为输入参数，人数为输出参数。create proc Man_Num dept varchar（50）,rs int output select rs=COUNT（*） from Student where Dept=dept and Sex=男 declare rs int exec Man_Num ,rs output select rs as 人数（4） 查询考试平均成绩超过指定分值的学生学号和平均成绩。create proc AvgGrade grade int select sno,

2、AVG（grade） as 平均成绩 from SC group by Sno having AVG（grade）grade exec AvgGrade 60（5） 查询查询指定系的学生中，选课门数最多的学生的选课门数和平均成绩，要求系为输入参数，选课门数和平均成绩用输出参数返回。create proc Choose_Course dept varchar（50）,rs int output,avg int output select top 1 rs=COUNT（*） ,avg=avg（grade） from Student s join SC on = join Course c on =

3、 where dept=dept group by declare rs int,avg int exec Choose_Course ,rs output,avg output select rs as 选课门数,avg as 平均成绩（6） 删除指定学生的修课记录，其中学号为输入参数。 create proc Del_Course sno char（50） as delete from SC where Sno=sno go exec Del_Course 0831102（7） 修改指定课程的开课学期。输入参数为：课程号和修改后的开课学期,开课学期的默认值为2。如果指定的开课学期不在18范

4、围内，则不进行修改。2 利用SSMS工具查看在students数据库中创建的全部存储过程。Students=可编程性= 存储过程3 修改第1题（1）的存储过程，使之能够查询指定系中，每个学生选课总门数、总学分和考试平均成绩。 alter proc SumCredit dept nvarchar（20） select COUNT 总门数,sum（credit） 总学分,AVG（Grade） 平均成绩 from student s left join sc on = left join course c on = where dept = dept exec SumCredit 4 创建满足下述要

5、求的触发器（前触发器、后触发器均可），并验证触发器执行情况。（1） 限制每个学期开设的课程总学分在2030范围内。 alter trigger TR_SumGrade on course after insert declare s int,x int,y int set s=（select sum（Credit） from course where semester in（select semester from inserted） if（20s and s5） select * from sc join course c on = select *from inserted print x

6、选课门数超过门5 在Students数据库建立如下所示的工作表和职工表CREATE TABLE 工作表（ 工作号 CHAR（8） PRIMARY KEY,最低工资 SMALLINT,最高工资 SMALLINT ）CREATE TABLE 职工表（ 职工号 CHAR（7） PRIMARY KEY, 职工名 CHAR（10） NOT NULL,工作号 CHAR（8） REFERENCES 工作表（工作号）,基本工资 SMALLINT,浮动工资 SMALLINT ）利用这两张表建立满足如下要求的触发器。（1） 限制职工的基本工资和浮动工资之和必须大于等于2000。create trigger TR_

7、Salaryon 职工表 after insert,updateasdeclare x SMALLINT,y SMALLINT,z SMALLINTset x=（select 基本工资 from 职工表 where 职工号 in（select 职工号 from inserted）set y=（select 浮动工资 from 职工表 where 职工号 in（select 职工号 from inserted）set z=x+yif（z=2000）beginprint操作符合要求endelseprint xprint yprint z请注意，职工的基本工资和浮动工资之和小于！select * f

8、rom 职工表select * from insertedrollbackinsert into 工作表 values（G001,10000,1000）insert into 职工表values（Z001张三,1000,100）（2） 限制工作表中最高工资不能低于最低工资的倍。create trigger TR_Salary1 on 工作表 after insert,update declare x SMALLINT,y SMALLINT,z float set x=（select 最低工资 from 工作表 where 工作号 in（select 工作号 from inserted） set

9、 y=（select 最高工资 from 工作表 where 工作号 in（select 工作号 from inserted） set z=y/x if（z= print y print z select * from 工作表 select * from inserted请注意，最高工资低于最低工资的.5倍 insert into 工作表 values（G002,1000,1000）（3） 限制不能删除基本工资低于1500的职工。alter trigger TR_Salary2 on 职工表 after delete as if exists（select * from 职工表 where 基

10、本工资=60） return sum select sname 姓名, 课程名,credit 课程学分,grade 考试成绩, as 总学分 from sc join course c on = join student s on = where =0811101（2） 查询指定系在指定课程（课程号）的考试平均成绩。 create function （dept nvarchar（20）, cno char（6） declare Avg int select Avg=avg（grade） from sc join student s on = where dept=dept and =cno r

11、eturn Avg select distinct （dept,cno） as 平均成绩 from sc join student s on = where dept= and =C001（3） 查询指定系的男生中选课门数超过指定门数的学生人数。 create function （dept nvarchar（20）,menshu int） declare Num int select Num=count（*） from（select ,count as b from student s left join sc on = where dept=dept and sex= group by ha

12、ving countmenshu） as t return Num select distinct （dept,0） 学生人数 from student s left join sc on = 7 创建满足下述要求的用户自定义内联表值函数。（1） 查询选课门数在指定范围内的学生的姓名、所在系和所选的课程。 create function （menshu int） returns table return（ select sname,dept,cname from Student s join SC on = join Course c on = where in （ select sno fr

13、om sc group by sno having count（*）=menshu）select * from （3）（2） 查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩。 create function （dept char（20） return（select sname,dept,cname,grade from Student s join SC on = where dept=dept and grade=90） select sname,cname,grade fro

14、m （）8 创建满足下述要求的用户自定义多语句表值函数。（1） 查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况。alter function （dept char（20）returns ret_F_8_1 table（sname char（10）,age int）insert into ret_F_8_1select top 2 WITH TIES sname,year（GETDATE（）-year（Birthday） age from studentwhere dept=deptorder by age DESCreturnselect sname,age from （2） 查询

15、指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：如果成绩大于等于90，则为“优”；如果成绩在8089，则为“良好”；如果成绩在7079，则为“一般”；如果成绩在6069，则为“不太好”；如果成绩小于60，则为“很糟糕”。并写出利用此函数查询李勇的考试情况的SQL语句。alter function （sname char（10）returns ret_F_8_2 table（dept char（20）,cname char（20）,GStye char（6）insert into ret_F_8_2select sname,dept,cname,cas

16、ewhen grade =90 then 优when grade between 80 and 89 then良好when grade between 70 and 79 then一般when grade between 60 and 69 then不太好when grade 60 then很糟糕from Student s join SC on = join Course c on = where sname=snameselect sname,dept,cname,gstye from （刘晨9 创建满足下述要求的游标。（1）查询VB课程的考试情况，并按如下形式显示结果数据:选了VB课程的

17、学生情况：姓名 所在系 成绩李勇 计算机系 86 刘晨 计算机系 78 吴宾 信息系 75 张海 信息系 68 姓名 所在系 成绩 declare s char（10）,d char（20）,g int declare C_9_1 cursor for select sname,dept,grade from Student s join SC on = join Course c on = where cname=VB open C_9_1 fetch next from C_9_1 into s,d,g while fetch_status=0 print s+d+cast（g as ch

18、ar（4） close C_9_1 deallocate C_9_1（2）统计每个系的男生人数和女生人数，并按如下形式显示结果数据。系名 性别 人数=计算机系 男 2 计算机系 女 1 数学系 男 1 数学系 女 1 信息系 男 2 信息系 女 1系名 性别 人数= declare d char（10）,s char（2）,c int declare C_9_2 cursor for select dept,sex,count（*） 人数 from Student group by dept,sex order by dept open C_9_2 fetch next from C_9_2

19、into d,s,c print d+s+cast（c as char（4） close C_9_2 deallocate C_9_2（3）列出每个系的学生信息，要求首先列出一个系的系名，然后在该系名下列出本系学生的姓名和性别；再列出下一个系名，然后在此系名下再列出该系的学生姓名和性别；以此类推，直至列出全部系。要求按如下形式显示结果数据:计算机系学生：李勇 计算机系 刘晨 计算机系 王敏 计算机系 =数学系学生：钱小平 数学系 王大力 数学系 信息系学生：张立 信息系 吴宾 信息系 张海 信息系 = declare dept varchar（20）,sname char（10） declar

20、e C_9_3 cursor for select distinct dept from student open C_9_3 fetch next from C_9_3 into dept print dept+: declare C_3 cursor for select sname,dept from student where dept=dept open C_3 fetch next from C_3 into sname,dept print sname+dept close C_3 deallocate C_3= close C_9_3 deallocate C_9_3（4）设有工作表，结构如下：Job（ Jobid char（4） primary key, -工作编号 desc varchar（40）, -工作描述 lvl tinyint） -工作级别设此表包含的数据如表7-2所示。表7-2 Job表的数据JobiddesclvlJ01软件开发10J02硬件开发12J03软件测试J04硬件维护8J05硬件测试用游标实现对此表数据的如下修改：将工作级别相同的工作只保留工作编号较小的一项工作，同时，将这些工作的工作描述拼接为一个