数据库原理-复杂查询-实验报告

计算机科学与信息学院软件工程系上机实验报告《数据库原理》实验报告题目实验4复杂查询（一）姓名***班级***日期***实验环境：SQLServer2000实验内容与完成情况：一、实验目的本实验的目的使学生熟练掌握SQLServer查询分析器的使用方法，本实验的目的是使学生进一步掌握SQLServer查询分析器的使用方法，加深SQL语言的连接查询的理解。二、实验内容本实验使用实验三中建立的基本表Student、Course、SC和Teacher中的元组。Student学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19CS200215123王敏女18MA200215125张立男19IS200215126欧阳丽女21FLCourse课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL648DB_Design12SC学号Sno课程号Cno成绩Grade2002151211922002151212852002151213882002151222902002151223802002151221200215123250200215123370Teacher教师编号Tno教师姓名Tname性别Tsex年龄Tage所在系Tdept职称Ttitles工资Twage系主任编号Tdno110001钟灵女27CS讲师2800110005110002杨毅男42CS副教授3500110005110003周倩女25CS讲师2800110005110005陈文茂男48CS教授4000110005120001江南男30IS副教授3500120003120002刘洋男28IS讲师2800120003120003汪明男44IS教授4000120003120004张蕾女35IS副教授3500120003130001邹佳羽女25MA讲师2800130003130002王力男30MA讲师2800130003130003王小峰男35MA副教授3500130003130004魏昭男40MA副教授3500130003140001王力男32FL副教授3500140005140002张小梅女27FL讲师2800140005140003吴娅女27FL讲师2800140005140004陈姝女35FL副教授3500140005140005周斌男44FL教授4000140005在SQLServer2000查询分析器中，使用连接查询完成如下查询要求：(1)求选课学生的基本情况以及他的选修情况；SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;(2)求学生的学号、姓名、选修的课程号及成绩；SELECTStudent.Sno,Student.Sname,SC.Cno,SC.GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;(3)求选修课程号为1且成绩在90以上的学生学号、姓名和成绩；SELECTStudent.Sno,Student.Sname,SC.GradeFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=1ANDGrade90;(4)求每一门课程的间接先行课（即先行课的先行课）；SELECTFIRST.Cno,SECOND.Cpnoas'该课程的先行课'FROMCourseFIRST,CourseSECONDWHEREFIRST.Cno=SECOND.Cno;(5)求选修了数学课的学生的学号和姓名；SELECTStudent.Sno,Student.SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='数学';(6)求学生李勇选修的总学分；SELECTSC.Sno,SUM(SC.Grade)as'总学分'FROMStudent,SCWHEREStudent.Sno=SC.SnoANDStudent.Sname='李勇'GroupbySC.Sno;(7)求各学生选修的总学分；SELECTStudent.Sname,SUM(SC.Grade)as'总学分'FROMStudent,SCWHEREStudent.Sno=SC.SnoGroupbyStudent.Sname;(8)求课程“数据库”的平均成绩；SELECTCourse.Cnameas'数据库',AVG(SC.Grade)as'平均成绩'FROMCourse,SCWHERECourse.Cno=SC.CnoANDCourse.Cname='数据库'GroupbyCourse.Cname;(9)求计算机系学生的选课情况；SELECTSC.*,Student.Sname,Student.SdeptFROMSC,StudentWHERESC.Sno=Student.SnoANDStudent.Sdept='CS';(10)求和钟灵在同一个系的老师姓名；SELECTTnameFROMTeacherWHERETdept=(SELECTTdeptFROMTeacherWHERETname='钟灵');(11)求吴娅所在系的教师人数；SELECTCOUNT(Tdept)FROMTeacherWHERETdept=(SELECTTdeptFROMTeacherWHERETname=‘吴娅’);(12)求和王小峰同一职称的教师姓名和所在系；SELECTTname,TdeptFROMTeacherWHERETtitles=(SELECTTtitlesFROMTeacherWHERETname=‘王小峰’)ANDTname!=‘王小峰’;(13)求和邹佳羽工资相同的教师人数；SELECTcount(Twage)FROMTeacherWHERETwage=(SELECTTwageFROMTeacherWHERETname='邹佳羽')ANDTname!='邹佳羽';(14)求汪明所在系教师的平均工资和最高工资；SELECTAVG(Twage)as'平均工资',MAX(Twage)as'最高工资'FROMTeacherWHERETdept=(SELECTTdeptFROMTeacherWHERETname='汪明');(15)求至少选修了8个学分的学生学号。SELECTSC.Sno,SUM(Course.Ccredit)FROMSC,CourseWHERECourse.Cno=SC.CnoGROUPBYSC.SnoHAVINGSUM(Course.Ccredit)=8;三、出现的问题及其解决方案(列出遇到的问题和解决办法，列出没有解决的问题)问题1：在求选修了数学课的学生的学号和姓名时，结果为零。分析原因：是否是表之间的属性联系没有对应，导致无法连接表，而没有显示数据；解决办法：检查表之间的公共属性是否连接正确，发现Course表的Cno没有与SC表的Cno连接。问题2：在求学生李勇选修的总学分,要显示总学分，则要标明是谁的总学分，用学生姓名来标明，但输入‘SELECTStudent.Sname,SUM(SC.Grade)····’查询时提示“选择列表中的列'Student.Sname'无效，因为该列没有包含在聚合函数或GROUPBY子句中。”分析原因：是否是聚集函数是按子句__进行分组的；解决办法：将分组子句改为‘GroupbyStudent.Sno;’改为‘GroupbyStudent.Sname;’，问题解决。问题3：在求和王小峰同一职称的教师姓名和所在系时，将王小峰也显示出来了。分析原因：因为是嵌套查询，所以王小峰所在系与内查寻的系相同，所以也显示；解决办法：将王小峰的名去掉，加上’ANDTname!=‘王小峰’’。问题解决。问题4：在求汪明所在系教师的平均工资和最高工资时，提示“操作数数据类型char对于avg运算符无效。”；分析原因：AVG()聚集函数是对int型元素进行运算的。解决办法：将Teacher表的Twage数据类型改为int型。