资源描述:
《数据库实验 T-SQL编程》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、6实验六T-SQL编程课程名称:数据库原理实验实验类型:设计型实验名称T_SQL编程学时4实验目的和要求:1.掌握变量的定义及赋值、数据显示及IF、WHILE、CASE逻辑控制语句。2.掌握简单子查询、IN子查询及EXISTS子查询的用法,并能应用T-SQL进行综合查询。实验内容:创建学员成绩数据库stu。createdatabasestu;1、创建学员信息表:createtableStuInfo(stuNamechar(10),stuNochar(10)primarykey,stuSexchar(
2、2)check(stuSex='男'orstuSex='女'),stuAgeint,stuSeatint,stuAddresschar(20));insertintoStuInfovalues('张秋丽','s25301','男',18,1,'北京海淀');insertintoStuInfovalues('李文才','s25302','男',28,2,'地址不详');insertintoStuInfovalues('李斯文','s25303','女',22,3,'河南洛阳');insertintoS
3、tuInfovalues('欧阳俊雄','s25304','女',34,4,'地址不详');insertintoStuInfovalues('梅超风','s25318','女',23,5,'地址不详');查询学员信息表:select*fromStuInfo;6----试编写SQL语句查找李文才的左右同桌。查找李文才的左右同桌:declare@l_Deskmatechar(10),@stuSeat1char(10)declare@r_Deskmatechar(10),@stuSeat2char(10)
4、select@stuSeat1=stuSeatfromStuInfowherestuName='李文才'print'李文才的座位号是:'+cast(@stuSeat1asvarchar)print'------------------------------'set@stuSeat2=@stuSeat1-1select@l_Deskmate=stuNamefromStuInfowherestuSeat=@stuSeat2print'姓名座位号'print'李文才的左同桌是:'+@l_Deskmate
5、+cast(@stuSeat2asvarchar)set@stuSeat2=@stuSeat1+1select@r_Deskmate=stuNamefromStuInfowherestuSeat=@stuSeat2print'李文才的右同桌是:'+@r_Deskmate+cast(@stuSeat2asvarchar)结果如下:1、学员成绩表如下:创建学员成绩表:createtableStuGrade(examNochar(10)primarykey,stuNochar(10)foreignkeyr
6、eferencesStuInfo(stuNo),writtenExamfloat,labExamfloat);insertintoStuGrade6values('s271811','s25303',80,58);insertintoStuGradevalues('s271813','s25302',50,90);insertintoStuGradevalues('s271816','s25301',77,82);insertintoStuGradevalues('s271818','s25318'
7、,45,52);查询结果:select*fromStuGrade;统计并显示本班笔试平均分,如果平均分在70以上,显示“成绩优秀“,并显示前三名学员的考试信息;如果在70以下,显示“本班成绩较差“,并显示后三名学员的考试信息。----本班笔试平均分:selectavg(writtenExam)writtenExam_avgfromStuGrade----如果平均分在70以上,显示“成绩优秀“,并显示前三名学员的考试信息;如果在70以下,显示“本班成绩较差“,并显示后三名学员的考试信息。declare
8、@avgsvarchar(10),@aavarchar(20)declarestucursorforselectavg(writtenexam)fromStuGradeopenstufetchfromstuinto@avgsif@avgs>70selecttop3*fromStuGradeorderbywrittenexamdesc6elseselecttop3*fromStuGradeorderbywrittenexamwhile@@fetch_status=0be