《实验六答案数据库综合查询【骄阳教学】》由会员分享，可在线阅读，更多相关《实验六答案数据库综合查询【骄阳教学】（4页珍藏版）》请在装配图网上搜索。
1、1、查询以DB_开头，且倒数第三个字符为s的课程的详细情况select *from coursewhere cname like DB_%s_2、查询名字中第二个字为“阳”的学生姓名和学号及选修的课程号、课程名select student.sno ,student.sname ,o,cnamefrom student,course,scwhere sname like _阳%and student.sno=sc.sno and sc.Cno=o3、列出选修了数学或大学英语的学生学号、姓名、select student.sno,sname,sdept,sc.Cno,cname,gradefrom
2、 student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sno in(select sc.sno from sc,course where (cname=大学英语or cname=数学)and sc.Cno=ogroup by sc.sno)select student.sno,sname,sdept,cno,gradefrom student,scwhere Cno in (select Cno from coursewhere cname=数学or cname=大学英语)and sc.sno=student.sno4、查
3、询缺少成绩的所有学生的详细情况；select *from student,scwhere Grade is null and student.sno=sc.sno5、查询与张力(假设姓名唯一)年龄不同的所有学生的信息；select *from studentwhere sage (select sagefrom studentwhere sname=张力)6、查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩select student.sno,sname,平均成绩=AVG(grade)from student ,scwhere student.sno=sc.snogroup
4、by student.sno,snamehaving AVG(Grade)(select AVG(Grade)from sc,student where sname=张力and student.sno=sc.sno)7、按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；select student.sno,sname,sdept,已修学分=SUM(ccredit)from student,sc,coursewhere Grade60and student.sno=sc.sno and sc.Cno=ogroup by student.
5、sno,sname,sdept8、列出只选修一门课程的学生的学号、姓名、院系及成绩select student.sno,sname,sdept,Cno,gradefrom student,scwhere student.sno=sc.sno and sc.sno in (select sno from sc group by sc.sno having COUNT(distinct Cno)=1)9、查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；select student.sno,sname,cnofrom student,scwhere student.sno=sc.sn
6、o and sc.sno in(select sc.sno from sc ,student where cno in(select cno from student,sc where sname=张力and student.sno=sc.sno) )10、只选修“数据库”和“数据结构”两门课程的学生的基本信息；select student.sno,snamefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sno in(select sc.sno from sc,course where (cname=数据库
7、or cname=数学)and sc.Cno=ogroup by sc.snohaving COUNT(*)=2)group by student.sno,snamehaving COUNT(*)=211、至少选修“数据库”或“数据结构”课程的学生的基本信息；只包括其中一门或两门：select student.sno,sname,sdept,sc.Cno,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sno in(select sc.sno from sc,course where (
8、cname=数据库or cname=数据结构)and sc.Cno=o )两门课全部包括：select student.sno,sname,sdept,sc.Cno,cname,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=o and sc.sno in(select sc.sno from sc,course where (cname=数据库or cname=数据结构)and sc.Cno=ogroup by sc.snohaving COUNT(sc.Cno)=2)12、列出所有课程被选修的详细情况，包括课程号
9、、课程名、学号、姓名及成绩select o,cname,student.sno,sname,gradefrom student,sc,coursewhere student.sno=sc.sno and sc.Cno=oorder by o13、查询只被一名学生选修的课程的课程号、课程名select o,cnamefrom sc,coursewhere sc.Cno=ogroup by sc.Cno,cnamehaving COUNT(sc.sno)=114、检索所学课程包含学生张向东所学课程的学生学号、姓名至少包含一门张向东所选都的课程：select student.sno,sname,cn
10、o from student,scwhere student.sno=sc.sno and student.sno in (select sno from sc,course where sc.Cno=o and sc.Cno in (select cno from sc,student where sname=张向东and student.sno=sc.sno)包括张向东所选的全部课程：select *from studentwhere sno in (select sno from sc,course where sc.Cno=o and sc.Cno in (select cno fro
11、m sc,student where sname=张向东and student.sno=sc.sno)group by sc.snohaving COUNT(*)=(select COUNT(cno)from sc,student where sname=张向东and student.sno=sc.sno)15、使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名select sno,snamefrom studentwhere sno in (select sno from sc,course where cname=数据结构and sc.Cno=o)16、使用嵌套查询查询其它系中年龄小于
12、CS系的某个学生的学生姓名、年龄和院系；select sname,sage,sdeptfrom studentwhere sageany(select sage from student where sdept=CS )and sdeptCS17、使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生select *from studentwhere sageall(select sage from student where sdept=CS)and sdeptCS;18、分别使用连接查询和嵌套查询，列出与张力在一个院系的学生的信息；select *from studentwh
13、ere sdept in(select sdept from student where sname=张力)19、使用集合查询列出CS系的学生以及性别为女的学生名单select *from studentwhere sdept=CSintersectselect *from studentwhere ssex=女20、使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集select *from studentwhere sdept=CSintersectselect *from studentwhere sage19select *from studentwhere sdept=CSexceptselect *from studentwhere sage1921、使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集；select snofrom scwhere Cno=1intersectselect snofrom scwhere Cno=222、思考题：按照课程名顺序显示各个学生选修的课程（如200515001 数据库 数据结构 数学）select o,sno,cnamefrom sc,coursewhere sc.Cno=oorder by sno,sc.Cno asc4习题与教育
实验六答案数据库综合查询【骄阳教学】
