sqlserver综合编程讲稿.doc

习题课（2）综合编程一、在选修成绩中查询出选修了课程号为kc01且成绩比平均分高的学生名单。分别用以下两种方法实现：(1)使用局部变量 (2)使用子查询参考程序（1）使用局部变量USE 学生选课GODECLARE avgcj floatSELECT avgcj=AVG(成绩) FROM 选修成绩 WHERE 课程号=kc01SELECT s.*FROM 学生 s INNER JOIN 选修成绩 sc ON s.学号=sc.学号WHERE 成绩avgcj AND 课程号=kc01（2）使用子查询USE 学生选课GOSELECT s.*FROM 学生 s INNER JOIN 选修成绩 sc ON s.学号=sc.学号 WHERE 课程号=kc01 AND 成绩all (SELECT AVG(成绩) FROM 选修成绩 WHERE 课程号=kc01)或USE 学生选课GOSELECT s.*FROM 学生 s WHERE 学号 IN(SELECT 学号 FROM 选修成绩 sc WHERE 课程号=kc01AND 成绩all (SELECT AVG(成绩) FROM 选修成绩 WHERE 课程号=kc01) )二、计算S=20+21+22+231 declare s float,i int,t float -由于数据较大，乘方和求和不能用intset s=1 -阶乘的和，初值为20set t=1 -2的阶乘，初值为20set i=1 -循环变量，初值为1，即循环求21开始 while i=31beginset t=t*2set s=s+tset i=i+1EndPrint 20+21+22+231之和：+convert(varchar(20),s) 三、根据如下规则对机试成绩进行反复加分，直到平均分超过85分为止，如下图所示。请编写T-SQL语句实现。90分以上： 不加分8089分： 加1分7079分： 加2分6069分： 加3分60分以下： 加5分【参考程序】SELECT * FROM stuMarks -原始成绩DECLARE labAvg floatSELECT labAvg=AVG(labExam) FROM stuMarksWHILE(labAvg85) BEGIN UPDATE stuMarks SET labExam= CASE WHEN labExam60 THEN labExam+5 WHEN labExam between 60 AND 69 THEN labExam+3 WHEN labExam between 70 AND 79 THEN labExam+2 WHEN labExam between 80 AND 89 THEN labExam+1 ELSE labExam END SELECT labAvg=AVG(labExam) FROM stuMarks ENDSELECT * FROM stuMarks -加分后的成绩(选讲)4、在成绩表中，统计并显示笔试成绩，鉴于试题偏难，假定要提分，确保每人笔试都通过。提分规则是，先给每个人都加2分，查看是否都通过，如果没有全通过，每人再加2分，再看是否都通过，如此反复提分。如果加分后超过100分，就按100分计算。最后按美国的ABCDE五级打分制来显示成绩。参考程序：DECLARE n intUPDATE stuMarks SET writtenExam=writtenExam+2 -每人先加2分WHILE(1=1) -条件永远成立BEGIN SELECT n=COUNT(*) FROM stuMarks WHERE writtenExam0) BEGIN UPDATE stuMarks SET writtenExam=writtenExam+2 -每人加2分 UPDATE stuMarks SET writtenExam=100 WHERE writtenExam100 END ELSE BREAK -退出循环ENDprint 加分后的成绩如下：SELECT * FROM stuMarksprint ABCDE五级显示成绩如下：SELECT stuNo,成绩=CASE WHEN writtenExam0 BEGINPRINT 该读者不能删除，还有+concert(char(2), data_yj+本书没还。ROLLBACKENDELSEPRINT 该读者已被删除！GO11、事务应用举例：模拟银行转帐功能。要求：从张三的帐户转帐1000元到李四的帐户BEGIN TRANSACTION /*-定义变量，用于累计事务执行过程中的错误-*/DECLARE errorSum INT SET errorSum=0 -初始化为0，即无错误/*-转帐：张三的帐户少1000元，李四的帐户多1000元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三SET errorSum=errorSum+error -累计是否有错误UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四SET errorSum=errorSum+error -累计是否有错误print 查看转帐事务过程的余额SELECT * FROM bank IF errorSum0 -如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存 COMMIT TRANSACTION ENDGOprint 查看转帐事务后的余额-SELECT * FROM bank GO12、练习和管理触发器：（1） 建立数据库testdb，并在数据库中建立两个表：Txl(ID int, Name char(10), Age int)Person_ counts(Person_ count int)（2） 使用T-SQL编写一个触发器tr_person_ins，每当txl表中插入一行数据时，表Person_ counts中对应的数量也相应地发生变化。（3） 使用企业管理器创建一个触发器tr_person_del，每当txl表中删除记录时，表Person_ counts中对应的数量也相应地发生变化。参考代码如下：（1）CREATE DATABASE testdb GO USE testdb GO CREATE TABLE txl (ID int, Name char(10), Age int) GOCREATE TABLE Person_counts(Person_count int) GO（2）create trigger tr_person_inson txlfor insertAS -必须将字段Person_count的初始值输入0 UPDATE Person_counts SET Person_count=Person_count+1 Select * from Person_counts GO测试触发器：insert txl values(1,AAAAAA,22)（3）create trigger tr_person_delon txlfor deleteAS DECLARE n int /*-统计txl表