实验五：数据库综合查询

实验五：数据库综合查询一、实验目的1.掌握SELECT语句的基本语法和查询条件表示方法；2.掌握查询条件种类和表示方法；3.掌握连接查询的表示及使用；4.掌握嵌套查询的表示及使用；5.了解集合查询的表示及使用。二、实验环境已安装SQLServer企业版的计算机(120台)；具有局域网环境,有固定IP;三、实验学时2学时四、实验要求1.了解SELECT语句的基本语法格式和执行方法；2.了解连接查询的表示及使用；3.了解嵌套查询的表示及使用；4.了解集合查询的表示及使用；5.完成实验报告；五、实验内容及步骤1.利用Transact-SQL嵌套语句实现下列数据查询操作。1)查询选修了计算机体系结构的学生的基本信息。select*fromstudentwhere学号in(select学号fromscwhere课程号in(select课程号fromcoursewhere课程名称='计算机体系结构'))2)查询年龄比李勇小的学生的学号和成绩。selectsc.学号,成绩fromsc,studentwheresc.学号=student.学号andstudent.年龄(select年龄fromstudentwhere姓名='李勇')3)查询其他系中比系编号为‘D1’的学生中年龄最小者要大的学生的信息。select*fromstudentwhere年龄in(select年龄fromstudentwhere年龄any(select年龄fromstudentwhere系编号='d1'))and系编号!='d1'4)查询其他系中比系编号为‘D3’的学生年龄都大的学生的姓名。select姓名fromstudentwhere年龄in(select年龄fromstudentwhere年龄all(select年龄fromstudentwhere系编号='d3'))and系编号!='d3'5)查询‘C1’课程的成绩高于70的学生姓名。select姓名fromstudent,scWherestudent.学号=sc.学号andsc.成绩70andsc.课程号='C1'6)查询‘C1’课程的成绩不高于70的学生姓名。select姓名fromstudent,scwherestudent.学号=sc.学号andsc.成绩=70andsc.课程号='C1';7)查询没有选修的学生姓名。select姓名fromstudentwhere学号notin(select学号fromsc)8）查询学校开设的课程总数。selectcount(课程号)课程号_countfromcourse;9）查询选修两门及两门以上课程的学生姓名。select姓名fromstudentwhere学号in(select学号fromscgroupby学号havingcount(课程号)=2);10）查询开设的课程和选修该课程的学生的总成绩、平均成绩、最高成绩和最低成绩。select课程号,sum(成绩)sum_成绩,avg(成绩)avg_成绩,max(成绩)max_成绩,min(成绩)min_成绩fromscgroupby课程号;(二)、以数据库原理实验4数据为基础，请使用T-SQL语句实现进行以下操作：1.查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；select*fromcoursewhere课程名称like'DB\_%s__'escape'\';2.查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；selecta.姓名,a.sno,b.cno,b.课程名称fromstudenta,courseb,sccwherea.sno=c.snoandc.cno=b.cnoanda.姓名like'_阳%';3.列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；selecta.sno,a.姓名,a.sdept,b.cno,b.gradefromstudenta,scbwherea.sno=b.snoandb.cnoin(selectcnofromcoursewhere课程名称='数学'or课程名称='大学英语');4.查询缺少成绩的所有学生的详细情况；select*fromstudentwheresnoin(selectsnofromscwheregradeisnull);5.查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；selecta.*fromstudenta,studentbwhereb.姓名='张力'anda.年龄b.年龄;6.查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；★selecta.sno,a.姓名,avg(b.grade)avg_gradefromstudenta,scbwherea.sno=b.snogroupbya.sno,a.姓名havingavg(b.grade)(selectavg(grade)fromscwheresno=(selectsnofromstudentwhere姓名='张力')7.按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；★selectstudent.sno学号,student.姓名姓名,student.sdept所在院系,sum(course.ccredit)已修学分fromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cnoandsc.grade=60groupbystudent.sno,student.姓名,student.sdept;8.列出只选修一门课程的学生的学号、姓名、院系及成绩；selecta.sno,a.姓名,a.sdept,b.gradefromstudenta,scbwherea.sno=b.snoandb.snoin(selectsnofromscgroupbysnohavingcount(sno)=1);9.查询选修“数据库”或“数据结构”课程的学生的基本信息；selectstudent.*fromstudent,course,scwherestudent.sno=sc.snoandsc.cno=course.cnoand(course.课程名称='数据库'orcourse.课程名称='数据结构');10.列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩；selecta.sno,a.姓名,b.cno,b.课程名称,c.gradefromstudenta,courseb,sccwherea.sno=c.snoandb.cno=c.cno;11.查询只被一名学生选修的课程的课程号、课程名；selectcno,课程名称fromcoursewherecnoin(selectcnofromscgroupbycnohavingcount(sno)=1);12.检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；★selectsno,姓名fromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromstudent,coursewhere姓名='张向东'));13.检索所学课程包含学生‘张向东’所学全部课程的学生学号、姓名；★selectstudent.sno,姓名fromstudentwherenotexists(selecta.snofromscawherea.sno=(selectsnofromstudentwhere姓名='张向东'andnotexists(selectb.snofromscbwhereb.sno=student.snoanda.cno=b.cno)));14.使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；selectsno,姓名fromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewhere课程名称='数据结构'));15.使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；select姓名,年龄,sdeptfromstudentwheresdept'CS'and年龄(selectmax(年龄)fromstudentwheresdept='CS');16.使用ANY、ALL查询，列出其他院系中比CS系所有学生年龄小的学生；--------any查询--------select*fromstudentwheresdept'CS'and年龄any(selectmin(年龄)fromstudentwheresdept='CS');--------all查询---------select*fromstudentwheresdept'CS'and年龄all(select年龄fromstudentwheresdept='CS');17.分别使用连接查询和嵌套查询，列出与‘张力’在一个院系的学生的信息；--------------连接查询-------------selecta.*fromstudenta,studentbwhereb.姓名='张力'anda.sdept=b.sdeptanda.姓名'张力';--------------嵌套查询--------------select*fromstudentwhere姓名'张力'andsdeptin(selectsdeptfromstudentwhere姓名='张力');18.使用集合查询列出CS系的学生以及性别为女的学生名单；select*fromstudentwheresdept='CS'unionselect*fromstudentwheressex='女';19.使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；--------交集---------select*fromstudentwheresdept='CS'intersectselect*fromstudentwhere年龄=19;--------差集---------select*fromstudentwheresdept='CS'exceptselect*fromstudentwhere年龄=19;20.使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集；select*fromscwherecno='1'intersectselectcnofromscwherecno='2'六、出现问题及解决办法如：某些查询操作无法执行，如何解决？