數(shù)據(jù)庫的嵌套查詢實驗報告

1、實驗三：數(shù)據(jù)庫的嵌套查詢實驗實驗?zāi)康模杭由顚η短撞樵冋Z句的理解。實驗內(nèi)容：使用in、比較符、any或all和exists操作符進行嵌套查詢操作。實驗步驟：一. 使用帶in謂詞的子查詢1. 查詢與劉晨在同一個系學(xué)習(xí)的學(xué)生的信息:比較select * from student where sdept in (select sdept from student where sname=劉晨)與: select * from student where sdept = (select sdept from student where sname=劉晨) 的異同比較: select * from stu

2、dent where sdept = (select sdept from student where sname=劉晨) and sname 劉晨與: select s1.* from student s1, student s2 where s1.sdept=s2.sdept and s2.sname=劉晨的異同2. 查詢選修了課程名為信息系統(tǒng) 的學(xué)生的學(xué)號和姓名:比較select sno, sname from student where sno in(select sno from sc where cno in (select cno from course where cname=

3、信息系統(tǒng))與: select sno, sname from student where sno in(select sno from sc, course where o=o and cname=信息系統(tǒng)) 3. 查詢選修了課程1和課程2的學(xué)生的學(xué)號:select sno from student where sno in (select sno from sc where cno=1)and sno in (select sno from sc where cno=2)比較: 查詢選修了課程1或課程2的學(xué)生的sno:select sno from sc where cno=1 or cno

4、=2比較連接查詢: select a.sno from sc a, sc b where a.sno=b.sno and a.cno=1 and b.cno=2 二. 使用帶比較運算的子查詢4. 查詢比劉晨年齡小的所有學(xué)生的信息:select * from student where sage (select sage from student where sname=劉晨)三. 使用帶any, all謂詞的子查詢5. 查詢其他系中比信息系(is)某一學(xué)生年齡小的學(xué)生姓名和年齡;select sname, sage from student where sage any (select sag

5、e from student where sdept=is) and sdeptis6. 查詢其他系中比信息系(is)學(xué)生年齡都小的學(xué)生姓名和年齡:select sname, sage from student where sage all(select sage from student where sdept=is) and sdeptis7. 查詢與計算機系(cs)系所有學(xué)生的年齡均不同的學(xué)生學(xué)號, 姓名和年齡:select sno,sname,sage from student where sageall(select sage from student where sdept=cs)

6、四. 使用帶exists謂詞的子查詢和相關(guān)子查詢8. 查詢與其他所有學(xué)生年齡均不同的學(xué)生學(xué)號, 姓名和年齡:select sno,sname,sage from student a where not exists(select * from student b where a.sage=b.sage and a.snob.sno) 9. 查詢所有選修了1號課程的學(xué)生姓名:select sname from student where exists(select * from sc where sno=student.sno and cno=1)10. 查詢沒有選修了1號課程的學(xué)生姓名:sel

7、ect sname from student where not exists(select * from sc where sno=student.sno and cno=1)11. 查詢選修了全部課程的學(xué)生姓名:sql server中: select sname from student where not exists(select * from course where not exists( select * from sc where sno=student.sno and cno=o)11. 查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生的學(xué)號:sql server中:sele

8、ct distinct sno from sc a where not exists (select * from sc b where sno=95002and not exists(select * from sc c where sno=a.sno and cno=b.cno)12. 求沒有人選修的課程號cno和cnamecname:select cno,cname from course c where not exists(select * from sc where o=c.cno )13*. 查詢滿足條件的(sno,cno)對, 其中該學(xué)號的學(xué)生沒有選修該課程號cno的課程sql server中:select sno,cno from student,course where not exists(select * from sc where cno=o and sno=student.sno)14*. 查詢每個學(xué)生的課程成績最高的成績信息(sno,cno,grade):select * from sc a where grade=(select max(g