实验答案(四-五-六)参考答案

-1-实验四简单查询和连接查询1.简单查询实验用Transact-SQL语句表示下列操作，在“学生选课“数据库中实现其数据查询操作：(1)查询数学系学生的学号和姓名。selectsno,snamefromstudentwheredept='数学系';(2)查询选修了课程的学生学号。selectdistinct(sno)fromsc;(3)查询选修课程号为0101的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。selectdistinct(sno),gradefromscwherecno='0101'orderbygradedesc,snoasc;(4)查询选修课程号为0101的成绩在80-90分之间的学生学号和成绩，并将成绩乘以系数0.8输出。selectdistinct(sno),grade*0.8as'sore'fromscwherecno='0101'andgradebetween80and90;(5)查询数学系或计算机系姓张的学生的信息。select*fromstudentwheredeptin('数学系','计算机系')andsnamelike'张%';(6)查询缺少了成绩的学生的学号和课程号。selectsno,cnofromscwheregradeisnull;2.连接查询实验用Transact-SQL语句表示，并在“学生选课”数据库中实现下列数据连接查询操作：(1)查询每个学生的情况以及他（她）所选修的课程。selectstudent.*,course.cnamefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cno;(2)查询学生的学号、姓名、选修的课程名及成绩。selectstudent.sno,sname,cname,gradefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cno;(3)查询选修离散数学课程且成绩为90分以上的学生学号、姓名及成绩。-2-selectstudent.sno,sname,gradefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cnoandcname='离散数学'andgrade=90;(4)查询每一门课的间接先行课（即先行课的先行课）。selectfirst.cno,second.pcnofromcourseasfirst,courseassecondwherefirst.pcno=second.cno;实验五嵌套查询用TransacTransact-SQL语句表示，在学生选课库中实现其数据嵌套查询操作：(l)查询选修了离散数学的学生学号和姓名。selectsno,snamefromstudentwheresnoin(selectsnofromscwherecno=(selectcnofromcoursewherecname='离散数学'));(2)查询0101课程的成绩高于张林的学生学号和成绩。selectsno,gradefromscwherecno='0101'andgrade(selectgradefromscwherecno='0101'andsno=(selectsnoFromstudentWheresname='张林'));(3)查询其他系中年龄小于计算机系年龄最大者的学生。select*fromstudentwheredept'计算机系'andage(selectmax(age)fromstudentwheredept='计算机系');(4)查询其他系中比计算机系学生年龄都小的学生。(3)中的max换成min即可。(5)查询同牟万里数据库原理课程分数相同的学生的学号。-3-selectsnofromscwheregrade=(selectgradefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cnoandcourse.cname='数据库原理'andsname='牟万里');(6)查询选修了0206课程的学生姓名。selectsnamefromstudentwheresnoin(selectsnofromscwherecno='0206');(7)查询没有选修0206课程的学生姓名。在(5)的in前加not即可。(8)查询选修了全部课程的学生的姓名。SELECTSNAMEFROMSTUDENTWHERESNOIN(SELECTSNOFROMSCGROUPBYSNOHAVINGCOUNT(*)=(SELECTCOUNT(*)FROMCOURSE));selectsnamefromstudentwherenotexists(select*fromcoursewherenotexists(select*fromscwheresno=student.snoandcno=course.cno));(9)查询与学号为“09001103”的学生所选修的全部课程相同的学生学号和姓名。selectsno,snameFromstudentWheresno'09001103'andnotexists(select*Fromscasx-4-Wheresno='09001103'andnotexists(select*FromscasyWherey.sno=student.snoandx.cno=y.cno));(10)查询至少选修了学号为“09001103”的学生所选修的全部课程的学生学号和姓名。selectsno,snamefromstudentwheresnoin(selectscx.snofromscscxwherenotexists(select*fromscscywherescy.sno='09001103'andnotexists(select*fromscsczwherescz.sno=scx.snoandscz.cno=scy.cno)));实验六组合查询和统计查询在学生选课数据库中实现其查询操作：(1)查找选修“计算机基础”课程的学生成绩比此课程的平均成绩大的学生学号，成绩。selectx.sno,x.gradeFromscasxWherex.grade(selectavg(y.grade)Fromscasy,courseascWherec.cname='计算机基础')andx.cno=(selectcnoFromcourseWherecname='计算机基础');(2)查询选修计算机基础课程的学生的平均成绩。selectavg(grade)FromscWheresnoin(selectsnoFromscWherecno=(selectcnoFromcourseWherecname='计算机基础'));(3)查询年龄大于女同学平均年龄的男同学姓名和年龄。-5-selectsname,ageFromstudentWheresex='男'andage(selectavg(age)FromstudentWheresex='女');(4)列出各系学生的总人数，并按人数进行降序排列。selectdept,count(*)astotalFromstudentGroupbydeptorderbytotaldesc;(5)统计各系各门课程的平均成绩。selectdept,cno,avg(grade)Fromstudent,scGroupbydept,cno(6)查询选修计算机基础和离散数学的学生学号和平均成绩。selects1.sno,avg(grade)as平均分Fromscass1Where'计算机基础'in(selectcnameFromcourseWherecnoin(selects2.cnoFromscass2Wheres2.sno=s1.sno))and'离散数学'in(selectcnameFromcourseWherecnoin(selectcnoFromscass3Wheres3.sno=s1.sno))Groupbys1.sno;