实验六答案数据库综合查询4371.pdf

《实验六答案数据库综合查询4371.pdf》由会员分享，可在线阅读，更多相关《实验六答案数据库综合查询4371.pdf（4页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1、查询以DB_开头，且倒数第三个字符为s的课程的详细情况 select*from course where cname like DB_%s_ 2、查询名字中第二个字为“阳”的学生姓名和学号及选修的课程号、课程名 select ,cname from student,course,sc where sname like _阳%and=and=3、列出选修了数学或大学英语的学生学号、姓名、select ,sname,sdept,cname,grade from student,sc,course where =and=and in(select from sc,course where(cna

2、me=大学英语or cname=数学)and=group by select,sname,sdept,cno,grade from student,sc where Cno in(select Cno from course where cname=数学or cname=大学英语)and=4、查询缺少成绩的所有学生的详细情况；select*from student,sc where Grade is null and=5、查询与张力(假设姓名唯一)年龄不同的所有学生的信息；select*from student where sage(select sage from student where

3、 sname=张力)6、查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩 select,sname,平均成绩=AVG(grade)from student,sc where=group by,sname having AVG(Grade)(select AVG(Grade)from sc,student where sname=张力and=7、按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；select,sname,sdept,已修学分=SUM(ccredit)from student,sc,course wher

4、e Grade60and=and=group by,sname,sdept 8、列出只选修一门课程的学生的学号、姓名、院系及成绩 select,sname,sdept,Cno,grade from student,sc where=and in (select sno from sc group by having COUNT(distinct Cno)=1)9、查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；select,sname,cno from student,sc where=and in(select from sc,student where cno in(sel

5、ect cno from student,sc where sname=张力and=)10、只选修“数据库”和“数据结构”两门课程的学生的基本信息；select ,sname from student,sc,course where =and=and in(select from sc,course where(cname=数据库or cname=数学)and=group by having COUNT(*)=2)group by,sname having COUNT(*)=2 11、至少选修“数据库”或“数据结构”课程的学生的基本信息；只包括其中一门或两门：select ,sname,sde

6、pt,cname,grade from student,sc,course where =and=and in(select from sc,course where(cname=数据库or cname=数据结构)and=)两门课全部包括：select ,sname,sdept,cname,grade from student,sc,course where =and=and in(select from sc,course where(cname=数据库or cname=数据结构)and=group by having COUNT=2)12、列出所有课程被选修的详细情况，包括课程号、课程名、

7、学号、姓名及成绩 select,cname,sname,grade from student,sc,course where=and=order by 13、查询只被一名学生选修的课程的课程号、课程名 select,cname from sc,course where=group by,cname having COUNT=1 14、检索所学课程包含学生张向东所学课程的学生学号、姓名 至少包含一门张向东所选都的课程：select,sname,cno from student,sc where=and in(select sno from sc,course where=and in(selec

8、t cno from sc,student where sname=张向东and=)包括张向东所选的全部课程：select*from student where sno in(select sno from sc,course where=and in(select cno from sc,student where sname=张向东and=group by having COUNT(*)=(select COUNT(cno)from sc,student where sname=张向东and=)15、使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名 select sno,sname

9、from student where sno in(select sno from sc,course where cname=数据结构and=16、使用嵌套查询查询其它系中年龄小于 CS 系的某个学生的学生姓名、年龄和院系；select sname,sage,sdept from student where sageany(select sage from student where sdept=CS)and sdeptCS 17、使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生 select*from student where sageall(select sage

10、from student where sdept=CS)and sdeptCS;18、分别使用连接查询和嵌套查询，列出与张力在一个院系的学生的信息；select*from student where sdept in(select sdept from student where sname=张力)19、使用集合查询列出CS系的学生以及性别为女的学生名单 select*from student where sdept=CS intersect select*from student where ssex=女 20、使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集 select*

11、from student where sdept=CS intersect select*from student where sage19 select*from student where sdept=CS except select*from student where sage19 21、使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集；select sno from sc where Cno=1 intersect select sno from sc where Cno=2 22、思考题：按照课程名顺序显示各个学生选修的课程（如1 数据库 数据结构 数学）select,sno,cname from sc,course where=order by sno,asc