数据库第五次作业.docx
《数据库第五次作业.docx》由会员分享，可在线阅读，更多相关《数据库第五次作业.docx（15页珍藏版）》请在冰点文库上搜索。
数据库第五次作业
实验七数据库的连接和嵌套查询实验
实验十T-SQL语句的综合应用
一实验目的和要求：
掌握变量的定义及赋值、数据显示及IF、WHILE、CASE逻辑控制语句。
实验类型：
设计验证实验内容：
创建学员成绩数据库stu。
实验目的和要求：
掌握简单子查询、IN子查询及EXISTS子查询的用法，并能应用T-SQL进行综合查询。
实验类型：
设计验证
CREATETABLESTUINFO
（
stuNamechar（9）,
stoNochar（9）primarykey,
stuSexchar
（2）check（stuSexin（‘男’,’女’））,
stuageint,
stuSeatchar
（2）,
stuAddressvarchar（20）,
）
CREATETABLESTUMARKS
（
ExamNochar（9）,
stuNOchar（9）,
writtenExamnumeric（5,2）,
LabExamnumeric（5,2）,
Primarykey（ExamNo,stuNo）,
Foreignkey（stuno）referencesSTUINFO（stoNo）,
）
输入数据：
insertintoSTUINFOvalues（'张秋丽','s25301','男',18,'1','北京海淀'）
insertintoSTUINFOvalues（'李文才','s25302','男',28,'2','地址不详'）
insertintoSTUINFOvalues（'李斯文','s25303','女',22,'3','河南洛阳'）
insertintoSTUINFOvalues（'欧阳俊雄','s25304','女',34,'4','地址不详'）
insertintoSTUINFOvalues（'梅超风','s25318','女',23,'5','地址不详'）
insertintoSTUMARKSvalues（'s','s25303',80,58）
insertintoSTUMARKSvalues（'s','s25302',50,90）
insertintoSTUMARKSvalues（'s','s25301',77,82）
insertintoSTUMARKSvalues（'s','s25328',45,65）
试编写SQL语句查找李文才的左右同桌。
set@name='李文才'
select@stuSeat=idfromSTUINFO
wherestuName=@name
select*fromSTUINFO
where（id=@stuSeat-1）or（id=@stuSeat+1）
go
二统计并显示本班笔试平均分，如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息
DECLARE@myavgfloat
SELECT@myavg=AVG（writtenExam）FROMSTUMarks
print‘本班平均分’+convert（varchar（5）,@myavg）
if（@myavg>70）
BEGIN
print‘本班笔试成绩优秀，前三名的成绩为：
’
SELECTTOP3*FROMstuMarksORDERBYwrittenExamDESC
END
ELSE
BEGIN
print‘本班笔试成绩较差，后三名的成绩为：
’
SELECTTOP3*FROMstuMarksORDERBYwrittenExam
END
三本次考试成绩较差，假定要提分，确保每人笔试都通过。
提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止。
DECLARE@nint
WHILE（1=1）
BEGIN
SELECT@n=COUNT（*）FROMstuMarks
WHEREwrittenExam<60
IF（@n>0）
UPDATESTUMarks
SETwrittenExam=writtenExam+2
ELSE
BREAK
END
print'加分后的成绩如下：
'
SELECT*FROMSTUMarks
四采用美国的ABCDE五级打分制来显示笔试成绩。
A级:
90分以上
B级:
80－89分
C级:
70－79分
D级:
60－69分
E级:
60分以下
print'ABCDE五级显示成绩如下:
'
SELECTstuNo,
成绩=CASE
WHENwrittenExam<60THEN'E'
WHENwrittenExamBETWEEN60AND69THEN'D'
WHENwrittenExamBETWEEN70AND79THEN'C'
WHENwrittenExamBETWEEN80AND89THEN'B'
ElSE'A'
END
FROMstuMarks
五有学员成绩数据库stu，其中学员信息表stuinfo和学生成绩表stumarks如下：
CREATETABLESTUINFO
（
stuNamechar（9）,
stoNochar（9）primarykey,
stuSexchar
（2）check（stuSexin（‘男’,’女’））,
stuageint,
stuSeatchar
（2）,
stuAddressvarchar（20）,
）
CREATETABLESTUMARKS
（
ExamNochar（9）,
stuNOchar（9）,
writtenExamnumeric（5,2）,
LabExamnumeric（5,2）,
Primarykey（ExamNo,stuNo）,
Foreignkey（stuno）referencesSTUINFO（stoNo）,
）
insertintoSTUINFOvalues（'张秋丽','s25301','男',18,'1','北京海淀'）
insertintoSTUINFOvalues（'李文才','s25302','男',31,'3','地址不详'）
insertintoSTUINFOvalues（'李斯文','s25303','女',22,'2','河南洛阳'）
insertintoSTUINFOvalues（'欧阳俊雄','s25304','男',28,'4','新疆威武哈'）
insertintoSTUINFOvalues（'梅超风','s25318','女',23,'5','地址不详'）
insertintoSTUMARKSvalues（'s','s25303',93,59）
insertintoSTUMARKSvalues（'s','s25302',63,91）
insertintoSTUMARKSvalues（'s','s25301',90,83）
insertintoSTUMARKSvalues（'s','s25328',63,53）
六、统计本次考试的缺考情况
七、比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。
加分后重新统计通过情况
八、统计通过率
--SELECT*FROMstuInfo
--SELECT*FROMstuMarks
/*--------------统计考试缺考情况----------------------*/
SELECT应到人数=（SELECTcount（*）FROMSTUINOFO）,
实到人数=（SELECTcount（*）FROMSTUMARKS）,
缺考人数=（（SELECTcount（*）FROMSTUINOFO）-（SELECTcount（*）FROMSTUMARKS））
/*----统计考试通过情况，并将结果存放在新表newTable中---*/
IFEXISTS（SELECT*FROMsysobjects
WHEREname='newTable'）
DROPTABLEnewTable
SELECTstuName,stuInfo.stuNo,writtenExam,labExam,
isPass=CASE
WHENwrittenExam>=60andlabExam>=60THEN1
ELSE0
END
INTOnewTableFROMSTUINFO
LEFTJOINSTUMARKS
ONSTUINFO.stuNo=STUMARKS.stuNo
--SELECT*FROMnewTable--查看统计结果，可用于调试
/*-酌情加分：
比较笔试和机试平均分，决定加哪门---*/
DECLARE@avgWrittennumeric（4,1）
DECLARE@avgLabnumeric（4,1）
SELECT@avgWritten=AVG（writtenExam）FROMnewTable
WHEREwrittenExamISNOTNULL
SELECT@avgLab=AVG（labExam）FROMnewTable
WHERElabExamISNOTNULL
IF@avgWritten<@avgLab
WHILE（1=1）--循环给笔试加分，最高分不能超过97分
BEGIN
UPDATEnewTableSETwrittenExam=writtenExam+1
IF（SELECTMAX（writtenExam）FROMnewTable）>=97
BREAK
END
ELSE
IF@avgWritten>@avgLab
WHILE（1=1）--循环给笔试加分，最高分不能超过97分
BEGIN
UPDATEnewTableSETLabExam=LabExam+1
IF（SELECTMAX（LabExam）FROMnewTable）>=97
BREAK
END
--因为提分，所以需要更新isPass（是否通过）列的数据
UPDATEnewTable
SETisPass=CASE
WHENwrittenExam>=60andlabExam>=60THEN1
ELSE0
END
--SELECT*FROMnewTable--可用于调试
/*--------------显示考试最终通过情况----------------*/
SELECT姓名=stuName,学号=stuNo
笔试成绩=CASE
WHENwrittenExamISNULLTHEN'缺考'
ELSEconvert（varchar（5）,writtenExam）
END
机试成绩=CASE
WHENlabExamISNULLTHEN'缺考'
ELSEconvert（varchar（5）,labExam）
END
是否通过=CASE
WHENisPass=1THEN'是'
ELSE'否'
END
FROMnewTable
/*--显示通过率及通过人数--*/
SELECT总人数=count（*）,通过人数=SUM（isPass）,
通过率=（convert（varchar（5）,AVG（isPass*100））+'%'）FROMnewTable
升级会员 