數(shù)據(jù)庫(kù)的嵌套查詢實(shí)驗(yàn)報(bào)告

1、實(shí)驗(yàn)三：數(shù)據(jù)庫(kù)的嵌套查詢實(shí)驗(yàn)實(shí)驗(yàn)?zāi)康模杭由顚?duì)嵌套查詢語(yǔ)句的理解。實(shí)驗(yàn)內(nèi)容：使用in、比較符、any或all和exists操作符進(jìn)行嵌套查詢操作。實(shí)驗(yàn)步驟：一. 使用帶in謂詞的子查詢1. 查詢與劉晨在同一個(gè)系學(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é)號(hào)和姓名:比較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é)號(hào):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 二. 使用帶比較運(yùn)算的子查詢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. 查詢與計(jì)算機(jī)系(cs)系所有學(xué)生的年齡均不同的學(xué)生學(xué)號(hào), 姓名和年齡:select sno,sname,sage from student where sageall(select sage from student where sdept=cs)

6、四. 使用帶exists謂詞的子查詢和相關(guān)子查詢8. 查詢與其他所有學(xué)生年齡均不同的學(xué)生學(xué)號(hào), 姓名和年齡: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號(hào)課程的學(xué)生姓名:select sname from student where exists(select * from sc where sno=student.sno and cno=1)10. 查詢沒(méi)有選修了1號(hào)課程的學(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é)號(hào):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. 求沒(méi)有人選修的課程號(hào)cno和cnamecname:select cno,cname from course c where not exists(select * from sc where o=c.cno )13*. 查詢滿足條件的(sno,cno)對(duì), 其中該學(xué)號(hào)的學(xué)生沒(méi)有選修該課程號(hào)cno的課程sql server中:select sno,cno from student,course where not exists(select * from sc where cno=o and sno=student.sno)14*. 查詢每個(gè)學(xué)生的課程成績(jī)最高的成績(jī)信息(sno,cno,grade):select * from sc a where grade=(select max(g