ACCESS的sql多表綜合查詢實驗答案

1、1、 查詢以DB_開頭,且倒數(shù)第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數(shù)據(jù)結構3、列出選修了數(shù)學或者大學英語的學生學號、姓名、所在院系、選修課程號及成績；查詢命令：SELECT student.sno,sname,sdept,cno,grade FROM student,sc WHERE cno in (SELECT cno FROM course WHERE cname='數(shù)學' 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、查詢?nèi)鄙俪煽兊乃袑W生的詳細情況；查詢命令：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、按照“學號，姓名，所在院系，已修學分”的順序列出學生學分的獲得情況。其中已修學分為考試已經(jīng)及格的課程學分之和；查詢命令：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.只選修“數(shù)據(jù)庫”和“數(shù)據(jù)結構”兩門課程的學生的基本信息；查

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='數(shù)據(jù)庫') and sc.sno in (SELECT sno FROM sc WHERE cno in (SELECT cno FROM course WHERE cname='數(shù)據(jù)結構' )查詢結果:查詢5查詢5查詢5查詢5snosnamessexsdept200515001趙菁菁女CS

12、11. 至少選修“數(shù)據(jù)庫”或“數(shù)據(jù)結構”課程的學生的基本信息；查詢命令: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='數(shù)據(jù)庫' or cname='數(shù)據(jù)結構')查詢結果:查詢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數(shù)據(jù)庫200515001趙菁菁751數(shù)據(jù)庫2005

14、15002李勇851數(shù)據(jù)庫200515003張力861數(shù)據(jù)庫200515004張衡741數(shù)據(jù)庫200515005張向東581數(shù)據(jù)庫200515006張向麗842數(shù)學200515004張衡462數(shù)學200515005張向東892數(shù)學200515006張向麗652數(shù)學200515008王民生722數(shù)學200515009王小民762數(shù)學200515010李晨963信息系統(tǒng)200515002李勇534操作系統(tǒng)200515001趙菁菁624操作系統(tǒng)200515002李勇855數(shù)據(jù)結構200515001趙菁菁585數(shù)據(jù)結構200515026尤陽陽906數(shù)據(jù)處理200515021劉峰587PASCAL語言2

15、00515001趙菁菁708大學英語200515010李晨868大學英語200515011張毅628大學英語200515015李蕾08大學英語200515016劉社Null8大學英語200515017劉星耀Null8大學英語200515018李貴589計算機網(wǎng)絡200515021劉峰5410人工智能200515005張向東6513. 查詢只被一名學生選修的課程的課程號、課程名；查詢命令:SELECT o,cname FROM sc,course WHERE o=o GROUP BY o,cname HAVING COUNT(sc.sno)=1查詢結果:查詢5查詢5cnocname3信息系統(tǒng)6數(shù)

16、據(jù)處理7PASCAL語言9計算機網(wǎng)絡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. 使用嵌套查詢列出選修了“數(shù)據(jù)結構”課程的學生學號和姓名；查詢命令:SELECT student.sno,sname FROM student WHERE sno in (SELECT sno FROM sc WHERE cno in (SELECT cno FROM course WHERE cname = '數(shù)據(jù)結構')查詢結果:查詢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趙菁菁