ACCESS的sql多表综合查询实验答案

1、1、 查询以DB_开头,且倒数第3个字符为s的课程的详细情况；查询命令：SELECT * FROM course WHERE cname like 'DB*S?'查询结果：查询4查询4查询4查询4CnoCnameCpnoCcredit11DBmaSee12、查询名字中第2个字为阳的学生姓名和学号及选修的课程号、课程名；查询命令：SELECT sno,sname,cno,cname FROM student,course WHERE cno=(SELECT cno FROM sc WHERE sno=( SELECT sno FROM student WHERE sname li

2、ke '?阳阳' ) and sname like '?阳?'查询结果：查询4查询4查询4查询4snosnamecnocname200515026尤阳阳5数据结构3、列出选修了数学或者大学英语的学生学号、姓名、所在院系、选修课程号及成绩；查询命令：SELECT student.sno,sname,sdept,cno,grade FROM student,sc WHERE cno in (SELECT cno FROM course WHERE cname='数学' or cname='大学英语') and student.sno=

3、sc.sno查询结果：查询4查询4查询4查询4查询4snosnamesdeptcnograde200515004张衡IS246200515005张向东IS289200515006张向丽IS265200515008王民生MA272200515009王小民MA276200515010李晨MA296200515010李晨MA886200515011张毅WM862200515015李蕾EN80200515018李贵EN858200515016刘社CM8Null200515017刘星耀CM8Null4、查询缺少成绩的所有学生的详细情况；查询命令：SELECT * FROM student WHERE s

4、tudent.sno in (SELECT sno FROM sc WHERE grade = 'null')查询结果：查询4查询4查询4查询4查询4SnoSnameSsexSageSdept200515016刘社男21CM200515017刘星耀男18CM5、查询与张力(假设姓名唯一)年龄不同的所有学生的信息；查询命令：SELECT * FROM student WHERE student.sno NOT IN (SELECT sno FROM student WHERE sname = '张力')查询结果：查询4查询4查询4查询4查询4SnoSnameSse

5、xSageSdept200515001赵菁菁女23CS200515002李勇男20CS200515004张衡男18IS200515005张向东男20IS200515006张向丽女20IS200515007王芳女20CS200515008王民生男25MA200515009王小民女18MA200515010李晨女22MA200515011张毅男20WM200515012杨磊女20EN200515013李晨女19MA200515014张丰毅男22CS200515015李蕾女21EN200515016刘社男21CM200515017刘星耀男18CM200515018李贵男19EN200515019林自

6、许男20WM200515020马翔男21200515021刘峰男25CS200515022牛站强男22200515023李婷婷丽女20200515025朱小鸥女30WM200515026尤阳阳女21MA6、查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；查询命令：SELECT sc.sno ,sname,AVG(IIF(grade='null',0,grade) FROM student,sc WHERE student.sno=sc.sno GROUP BY sc.sno,sname HAVING (AVG(IIF(grade=&

7、#39;null',0,grade) > (SELECT AVG(grade) FROM sc WHERE sc.sno=(SELECT student.sno FROM student WHERE sname='张力') GROUP BY sno ) )查询结果：查询6查询6查询6snosnameExpr1002200515010李晨91200515026尤阳阳907、按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；查询命令：SELECT sno as 学号,sname as 姓名,sdept as

8、所在院系, SUM(iif(grade>'60'and grade<>'null',1,0)as 已修学分 FROM student,course WHERE查询结果：8、列出只选修一门课程的学生的学号、姓名、院系及成绩；查询命令：SELECT student.sno,sname FROM student student, sc WHERE student.sno=sc.sno GROUP BY student.sno,sname HAVING COUNT(o)=1;查询结果：查询4查询4snosname200515003张力200515008

9、王民生200515009王小民200515011张毅200515015李蕾200515016刘社200515017刘星耀200515018李贵200515026尤阳阳9.查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；查询命令:SELECT student.sno,sname,cno FROM student,sc WHERE student.sno in ( SELECT sno FROM sc WHERE cno IN (SELECT cno FROM student,sc WHERE student.sno=sc.sno and sname = '张力')

10、and student.sno=sc.sno GROUP BY student.sno,sname,cno查询结果:查询5查询5查询5snosnamecno200515001赵菁菁1200515001赵菁菁4200515001赵菁菁5200515001赵菁菁7200515002李勇1200515002李勇3200515002李勇4200515003张力1200515004张衡1200515004张衡2200515005张向东1200515005张向东2200515005张向东10200515006张向丽1200515006张向丽210.只选修“数据库”和“数据结构”两门课程的学生的基本信息；查

11、询命令:SELECT DISTINCT student.sno,sname,ssex,sdept FROM student,course,sc WHERE student.sno=sc.sno and o=(SELECT cno FROM course WHERE ame='数据库') and sc.sno in (SELECT sno FROM sc WHERE cno in (SELECT cno FROM course WHERE cname='数据结构' )查询结果:查询5查询5查询5查询5snosnamessexsdept200515001赵菁菁女CS

12、11. 至少选修“数据库”或“数据结构”课程的学生的基本信息；查询命令:SELECT DISTINCT student.sno,sname,ssex,sdept FROM student,course,sc WHERE student.sno=sc.sno and o in(SELECT cno FROM course WHERE cname='数据库' or cname='数据结构')查询结果:查询5查询5查询5查询5snosnamessexsdept200515001赵菁菁女CS200515002李勇男CS200515003张力男CS200515004张衡男

13、IS200515005张向东男IS200515006张向丽女IS200515026尤阳阳女MA12. 列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩；查询命令:SELECT o,cname,student.sno,sname,grade FROM course,student,sc WHERE o=o and sc.sno=student.sno GROUP BY o,cname,student.sno,student.sname,grade查询结果:查询5查询5查询5查询5查询5cnocnamesnosnamegrade1数据库200515001赵菁菁751数据库2005

14、15002李勇851数据库200515003张力861数据库200515004张衡741数据库200515005张向东581数据库200515006张向丽842数学200515004张衡462数学200515005张向东892数学200515006张向丽652数学200515008王民生722数学200515009王小民762数学200515010李晨963信息系统200515002李勇534操作系统200515001赵菁菁624操作系统200515002李勇855数据结构200515001赵菁菁585数据结构200515026尤阳阳906数据处理200515021刘峰587PASCAL语言2

15、00515001赵菁菁708大学英语200515010李晨868大学英语200515011张毅628大学英语200515015李蕾08大学英语200515016刘社Null8大学英语200515017刘星耀Null8大学英语200515018李贵589计算机网络200515021刘峰5410人工智能200515005张向东6513. 查询只被一名学生选修的课程的课程号、课程名；查询命令:SELECT o,cname FROM sc,course WHERE o=o GROUP BY o,cname HAVING COUNT(sc.sno)=1查询结果:查询5查询5cnocname3信息系统6数

16、据处理7PASCAL语言9计算机网络10人工智能14. 检索所学课程包含学生张向东所学课程的学生学号、姓名；SELECT student.sno,sname FROM student WHERE student.sno in ( SELECT sno FROM sc WHERE cno IN (SELECT cno FROM student,sc WHERE student.sno=sc.sno and sname = '张向东') GROUP BY student.sno,sname查询命令:查询结果:查询5查询5snosname200515001赵菁菁200515002李勇

17、200515003张力200515004张衡200515005张向东200515006张向丽200515008王民生200515009王小民200515010李晨15. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；查询命令:SELECT student.sno,sname FROM student WHERE sno in (SELECT sno FROM sc WHERE cno in (SELECT cno FROM course WHERE cname = '数据结构')查询结果:查询5查询5snosname200515001赵菁菁200515026尤阳阳16

18、. 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；查询命令:select sname,sage,sdept from student where sno NOT IN (SELECT sno FROM student WHERE sdept='CS') and sage <(select max(sage) from student where sdept='cs')查询结果:查询7查询7查询7snamesagesdept张衡18IS张向东20IS张向丽20IS王小民18MA李晨22MA张毅20WM杨磊20EN李晨19MA李蕾21

19、EN刘社21CM刘星耀18CM李贵19EN林自许20WM马翔21牛站强22李婷婷18严丽20尤阳阳21MA17. 使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生查询命令:SELECT sname,sage,sdept FROM student GROUP BY sdept,sname,sage,sno HAVING sage < ALL(SELECT sage FROM student WHERE sdept='CS' )and sno NOT IN (SELECT sno FROM student WHERE sdept='CS')

20、查询结果:查询5查询5查询5snamesagesdept李婷婷18刘星耀18CM张衡18IS王小民18MA17. 使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生查询命令:查询命令:SELECT sname,sage,sdept FROM student GROUP BY sdept,sname,sage,sno HAVING sno not in(SELECT sno FROM student WHERE sage >=ANY(SELECT sage FROM student WHERE sdept='CS' )查询结果:查询5查询5查询5sname

21、sagesdept李婷婷18刘星耀18CM张衡18IS王小民18MA18. 分别使用连接查询和嵌套查询，列出与张力在一个院系的学生的信息；查询命令:SELECT * FROM student WHERE sdept in (SELECT sdept FROM student WHERE sname='张力')查询结果:查询5查询5查询5查询5查询5SnoSnameSsexSageSdept200515001赵菁菁女23CS200515002李勇男20CS200515003张力男19CS200515007王芳女20CS200515014张丰毅男22CS200515021刘峰男25

22、CS18. 分别使用连接查询和嵌套查询，列出与张力在一个院系的学生的信息；查询命令:SELECT stu1.sno as 学号,stu1.sname as 姓名,stu1.ssex as 性别,stu1.sage as 年龄,stu2.sdept as 院系 FROM student stu1,student stu2 WHERE stu1.sdept=stu2.sdept and stu2.sname='张力'查询结果:查询6查询6查询6查询6查询6学号姓名性别年龄院系200515014张丰毅男22CS200515002李勇男20CS200515021刘峰男25CS20051

23、5003张力男19CS200515007王芳女20CS200515001赵菁菁女23CS19、使用集合查询列出CS系的学生以及性别为女的学生名单；查询命令：SELECT stu1.sno as 学号,stu1.sname as 姓名,stu1.ssex as 性别,stu1.sage as 年龄,stu2.sdept as 院系 FROM student stu1,student stu2 WHERE stu1.sno=stu2.sno and stu2.sdept='CS'查询结果：查询6查询6查询6查询6查询6学号姓名性别年龄院系200515001赵菁菁女23CS20051

24、5002李勇男20CS200515003张力男19CS200515007王芳女20CS200515014张丰毅男22CS200515021刘峰男25CS19、使用集合查询列出CS系的学生以及性别为女的学生名单；查询命令：SELECT stu1.sno as 学号,stu1.sname as 姓名,stu1.ssex as 性别,stu1.sage as 年龄,stu2.sdept as 院系 FROM student stu1,student stu2 WHERE stu1.sno=stu2.sno and stu2.sdept='CS' and stu2.ssex='女'查询结果：查询6查询6查询6查询6查询6学号姓名性别年龄院系200515001赵菁菁