数据库实验 T-SQL编程.doc

6实验六 T-SQL编程课程名称：数据库原理实验实验类型：设计型实验名称T_SQL编程学时4实验目的和要求：1.掌握变量的定义及赋值、数据显示及IF、WHILE、CASE逻辑控制语句。2.掌握简单子查询、IN子查询及EXISTS子查询的用法，并能应用T-SQL进行综合查询。实验内容：创建学员成绩数据库stu。create database stu;1、 创建学员信息表：create table StuInfo(stuName char(10), stuNo char(10) primary key, stuSex char(2) check(stuSex=男or stuSex=女), stuAge int, stuSeat int, stuAddress char(20);insert into StuInfovalues(张秋丽,s25301,男,18,1,北京海淀);insert into StuInfovalues(李文才,s25302,男,28,2,地址不详);insert into StuInfovalues(李斯文,s25303,女,22,3,河南洛阳);insert into StuInfovalues(欧阳俊雄,s25304,女,34,4,地址不详);insert into StuInfovalues(梅超风,s25318,女,23,5,地址不详);查询学员信息表：select *from StuInfo;-试编写SQL语句查找李文才的左右同桌。查找李文才的左右同桌：declare l_Deskmate char(10),stuSeat1 char(10)declare r_Deskmate char(10),stuSeat2 char(10)select stuSeat1=stuSeat from StuInfo where stuName=李文才print 李文才的座位号是：+cast(stuSeat1 as varchar)print -set stuSeat2=stuSeat1-1select l_Deskmate=stuName from StuInfo where stuSeat=stuSeat2print 姓名 座位号print 李文才的左同桌是: +l_Deskmate+cast(stuSeat2 as varchar)set stuSeat2=stuSeat1+1select r_Deskmate=stuName from StuInfo where stuSeat=stuSeat2print 李文才的右同桌是: +r_Deskmate+cast(stuSeat2 as varchar) 结果如下：2、 学员成绩表如下：创建学员成绩表：create table StuGrade(examNo char(10) primary key, stuNo char(10) foreign key references StuInfo(stuNo), writtenExam float, labExam float);insert into StuGradevalues(s271811,s25303,80,58);insert into StuGradevalues(s271813,s25302,50,90);insert into StuGradevalues(s271816,s25301,77,82);insert into StuGradevalues(s271818,s25318,45,52);查询结果：select *from StuGrade;统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。-本班笔试平均分：select avg(writtenExam) writtenExam_avgfrom StuGrade-如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。declare avgs varchar(10),aa varchar(20)declare stu cursor forselect avg(writtenexam)from StuGradeopen stufetch from stuinto avgsif avgs70 select top 3 * from StuGrade order by writtenexam descelse select top 3 * from StuGrade order by writtenexamwhile fetch_status = 0begin if avgs70 set aa=成绩优秀 else set aa=本班成绩较差 select 平均成绩 +avgs+ 成绩评估+aa fetch from stu into avgs endclose studeallocate stu3、本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。if info=本班成绩较差begindeclare v_count int set v_count=1endwhile v_count0beginupdate StuGrade set writtenExam=writtenExam+2 select v_count=count(*) from StuGrade where writtenExam=90 then A级 when writtenExam=80 then B级 when writtenExam=70 then C级 when writtenExam=60 then D级 when writtenExamavg_labExambegin while (select max(labExam) from StuGrade)97 beginupdate StuGrade set labExam=labExam+1 endendelse if avg_writtenExamavg_labExambeginwhile (select max(writtenExam) from StuGrade)=60 and labExam=60 then 是 else 否 endfrom StuGrade,StuInfowhere StuGrade.stuNo=StuInfo.stuNo7、统计通过率 declare total_num int,through_num int,through_rate floatset total_num=(select count(*) from StuInfo)set through_num=(select count(*) from StuGrade where writtenExam=60 and labExam=60)set through