数据库原理与应用实验作业参考答案

1数据库原理与应用实验报告参考答案实验1数据库的建立修改与删除实验2表结构的建立修改删除及完整性约束条件定义实验3数据查询与更新完成以下实验报告：《数据库原理与应用》实验报告院名专业年级班级学号姓名教师成绩一、实验题目实验3数据查询与更新二、实验环境操作系统：WindowsXP。数据库管理系统：MSSQLServer2000或MSSQLServer2008。三、实验目的1．掌握SELECT语句的语法、语义及语用。2．掌握基于单表的查询方法。3．掌握基于多表的查询方法。4．掌握相关与不相关的嵌套查询。5．掌握集合查询。6．掌握插入(INSERT)、修改(UPDATE)和删除(DELETE)语句的使用。四、实验内容(一)规定内容1.单表查询USEST--[3_1]查询全体学生的详细记录。select*fromstudent--[3_2]查询选修了课程的学生学号。SELECTDISTINCTsnoFROMsc--[3_3]将“学生”表中的sno、sname这2列合并为1列snosname输出(不改变表中存储的内容)，其余列不变。selectsno+snamesnosname,ssex,sage,sdeptfromstudent--[3_4]查询年龄不在20~23岁之间的学生姓名、系别和年龄。方法1：SELECTsname,sdept,sageFROMstudent2WHEREsageNOTBETWEEN20AND23;方法2：SELECTsname,sdept,sageFROMstudentWHEREsage20orsage23;--[3_5]查询计算机科学系(cs)、数学系(ma)和信息系(is)学生的姓名和性别。SELECTsname,ssexFROMstudentWHEREsdeptIN('cs','ma','is');--[3_6]查询所有姓“刘”学生的姓名、学号和性别。SELECTsname,sno,ssexFROMstudentWHEREsnameLIKE'刘%';--[3_7]查询名字中第2个字为阳字的学生的姓名和学号。SELECTsname,snoFROMstudentWHEREsnameLIKE'_阳%';--[3_8]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。--查询缺少成绩的学生的学号和相应的课程号。SELECTsno,cnoFROMscWHEREgradeISNULL;--[3_9]查询计算机系年龄在20岁以下的学生姓名。SELECTsnameFROMstudentWHEREsdept='cs'ANDsage20;--[3_10]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTsno,gradeFROMscWHEREcno='3'ORDERBYgradeDESC;--[3_11]查询全体学生情况，查询结果按所在系升序排列，同一系中的学生按性别降序排列。SELECT*FROMstudentORDERBYsdept,sageDESC;--使用集函数--[3_12]查询学生总人数。SELECTCOUNT(*)学生总人数FROMstudent;--[3_13]查询选修了课程的学生人数。SELECTCOUNT(DISTINCTsno)选课人数--注:用DISTINCT以避免重复计算学生人数FROMsc;--[3_14]计算1号课程的最高、最低及平均成绩。SELECTMAX(grade)'1号课程最高分',MAX(grade)'1号课程最低分',AVG(grade)'1号课程平均成绩'3FROMscWHEREcno='1'--[3_15]查询学生200215121选修课程的平均成绩。SELECTAVG(grade)学生200215121平均成绩FROMscWHEREsno='200215121'--[3_16]查询学生200215122选修课程的总学分数。SELECTSUM(ccredit)学生200215122学分FROMsc,courseWHEREsc.cno=course.cnoANDsno='200215122';--使用GROUPBY子句分组--[3_17]求各个课程号及相应的选课人数。SELECTcno课程号,COUNT(sno)选课人数FROMscGROUPBYcno;--使用HAVING短语筛选最终分组结果--[3_18]查询选修了3门(含3)以上课程的学生学号。SELECTsnoFROMscGROUPBYsnoHAVINGCOUNT(*)=3--也可为:COUNT(cno)=3--[3_19]查询有2门以上课程是80分以上的学生的学号及(80分以上的)课程数SELECTsno学号,COUNT(*)'80分以上的课程数'FROMscWHEREgrade=80GROUPBYsnoHAVINGCOUNT(*)=22.连接查询--自然连接--[3_20]查询每个学生的学号、姓名、课号及成绩。SELECTstudent.sno,sname,cno,gradeFROMstudent,scWHEREstudent.sno=sc.sno--左外连接--[3_21]查询每个学生的学号、姓名、课号及成绩(包括没有选修课程的学生)。方法1：SELECTstudent.sno,sname,cno,gradeFROMstudent,scwherestudent.sno*=sc.sno方法2：SELECTstudent.sno,sname,cno,gradeFROMstudentLEFTJOINscONstudent.sno=sc.sno运行结果：snosnamecnograde----------------------------4200215121李勇197.0200215121李勇240.0200215121李勇393.0200215122刘晨295.0200215122刘晨355.0200215123王敏NULLNULL200215124张立NULLNULL--[3_22]查询所有学生选修课程的成绩。包括没有选课的学生。列出学号、姓名、课号、课名、成绩。SELECTstudent.sno,sname,sc.cno,cname,gradeFROMscJOINcourseONsc.cno=course.cnoRIGHTJOINstudentONstudent.sno=sc.sno运行结果：snosnamecnocnamegrade--------------------------------------------200215121李勇1数据库97.0200215121李勇2数学40.0200215121李勇3信息系统93.0200215122刘晨2数学95.0200215122刘晨3信息系统55.0200215123王敏NULLNULLNULL200215124张立NULLNULLNULL--自身连接:一个表与其自己进行连接，称为表的自身连接--[3_23]查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.cno课号,SECOND.cpno间接先修课FROMcourseFIRST,courseSECONDWHEREFIRST.cpno=SECOND.cno--复合条件连接--[3_24]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。SELECTstudent.sno,snameFROMstudent,scWHEREstudent.sno=sc.sno/*连接谓词*/ANDsc.cno='2'ANDsc.grade90/*其他限定条件*/--多表连接--[3_25]查询每个学生的学号、姓名、课名及成绩。SELECTstudent.sno,sname,cname,gradeFROMstudent,sc,courseWHEREstudent.sno=sc.snoandsc.cno=course.cno运行结果：snosnamecnamegrade--------------------------------------200215121李勇数据库97.0200215121李勇数学40.0200215121李勇信息系统93.0200215122刘晨数学95.05200215122刘晨信息系统55.03．嵌套查询--[3_26]查询与“刘晨”在一个系学习的学生。--方法1:不相关子查询(子查询的查询条件不依赖于父查询)selectsno,sname,sdeptfromstudentwheresdeptin--当内查询结果最多只有一个值时可用=代替in(selectsdeptfromstudentwheresname='刘晨')--[3_27]查询所有姓名相同的学生。--方法1:不相关子查询(子查询的查询条件不依赖于父查询)select*fromstudentwheresnamein(selectsnamefromstudentgroupbysnamehavingcount(*)1)orderbysname,sno--[3_28]查询选修了课程名为“信息系统”的学生学号和姓名。--方法1：用嵌套查询(不相关子查询)SELECTsno,sname--③最后在sudent关系中取出Sno和SnameFROMstudentWHEREsnoIN(SELECTsno--②然后在sc中找出选修了“信息系统”所在课号的学生学号FROMscWHEREcnoIN(SELECTcno--①首先在curse关系中找出“信息系统”的课程号FROMcourseWHEREcname='信息系统'))--[3_29]找出每个学生超过他选修课程平均成绩的学号、课号及成绩。SELECTx.*FROMscxWHEREgrade=(SELECTAVG(grade)FROMscyWHEREy.sno=x.sno)运行结果：snocnograde--------------------200215121197.0200215121393.0200215122295.0--[3_30]查询其他系中比计算机科学系某一学生年龄小的学生学号、姓名和年龄。--方法1：用any谓词6selectsname,sagefromstudentwheresageany(selectsagefromstudentwheresdept='cs')andsdept'cs'/*注意这是父查询块中的条件*/--[3_31]查询其他系中比计算机科学系所有学生年龄都小的学生学号、姓名和年龄。selectsname,sagefromstudentwheresageall(selectsagefromstudentwheresdept='cs')andsdept'cs'--[3_32]查询所有选修了1号课程的学生学号、姓名。SELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno='1');--相关子查询--[3_33]查询没有选修1号课程的学生学号、姓名。--方法1：相关子查询SELECTsno,snameFROMstudentWHERENOTEXISTS(SELECT*--此处*可换为snoFROMscWHEREsno=student.snoANDcno='1');--[3_34]查询选修了全部课程的学生姓名。SELECTsname--查询这样的学生yFROMstudentWHERENOTEXISTS--不存在课程x(SELECT*FROMcourseWHERENOTEXISTS--学生y不选修x(SELECT*FROMscWHEREsno=student.sno--表SC与Student自然连接ANDcno=course.cno))--表SC与course自然连接--[3_35]查询至少选修了学生200215122选修的全部课程的学生号码。SELECTDISTINCTsno--查