《数据库的嵌套查询实验报告》由会员分享，可在线阅读，更多相关《数据库的嵌套查询实验报告（19页珍藏版）》请在金锄头文库上搜索。
1、实验三：数据库的嵌套查询实验实验目的：加深对嵌套查询语句的理解。实验容：使用IN、比较符、ANY或ALL和EXIST，操作符进行嵌套查询操作。实验步骤：一.使用带IN谓词的子查询1.查询与晨在同一个系学习的学生的信息：比较 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 student where
2、 sdept =(select sdept from student where sname=晨)andsname 晨 与：select S1.* from student S1, student S2 where S1.sdept=S2.sdept andS2.sname=晨的异同2.查询选修了课程名为信息系统的学生的学号和:比较 select sno, sname from student where sno in(select sno from sc whereo in (selecto from course whereame=信息系统与：select sno,) sname from
3、 student where sno in(select sno from sc, course where o=oandame=信息系统)菱SflL查询册析尊回区文网ID WH-1-夏海(SP.T g Id F用中 临土 J _X|J - :11 -1. -.-Jfl 0 g口 偃ffih llfjn -| |曰,*31日圄瓯|旬回W 35嗣1 F&9-ck. FSSkAdAindtT祉 m 王标蹬掌匚|日;X)lACt 2NQ.fxo ftLjd.StiT *LtiAXw 2hu HI(aielecjt sun fiDt. sc wherfi eno iri sltct criD fi t
4、it. enuxst diie cil&亡一信，黑亨毗)IJ由 ihl卜U 5UJ-HkhvindIJjubi+U：4rdtUg止UTFJys*1_l 用材默* _j配隹函致 忡 _i was F _|日朗和时间函翦 H二J g由数& _|聚举函致 业_|F3演园取 F _1安圭函裁_1宇任3国敦 片_|京迁函曲 B 本院城曜糊 H-二J LeL 叫 LB4 函罚1 旧二|行Itk Isitu ritaiw口吨国清用*1，弱 I.OJ 歧 LMLSEtrl 旧 TJ e 口:伽:伽 Grid 扪 。行行 1 刿 L直授:LFH3. 查询选修了课程1和课程2的学生的学号：select sno f
5、rom student where sno in (selectsnofrom sc whereo=1)and sno in (select sno from sc whereo=2)比较：查询选修了课程1或课程2的学生的sno:select sno from sc whereo=1 oro=2比较连接查询:select A.sno from sc A, sc B where A.sno=B.sno and A.cno=1 and B.cno=2二.使用带比较运算的子查询4. 查询比晨年龄小的所有学生的信息: select * from student where sage(select sa
6、ge from student where sname=晨)三.使用带Any, All谓词的子查询5, 查询其他系中比信息系（IS）某一学生年龄小的学生和年龄;select sname, sage from student where sage Any(select sage from student where sdept=IS) and sdeptIS6,查询其他系中比信息系（IS）学生年龄都小的学生和年龄:select sname, sage from student where sage ALL(select sage from student where sdept=IS) and
7、sdeptIS7.查询与计算机系（CS）系所有学生的年龄均不同的学生学号，和年龄:select sno,sname,sage from student where sageall(select sage from student where sdept=CS)四.使用带Exists调词的子查询和相关子查询8,查询与其他所有学生年龄均不同的学生学号，和年龄: select sno,sname,sage from student A where not exists(select * from student B where A.sage=B.sage and A.snoB.sno)9,查询所有选
8、修了 1号课程的学生:select sname from student where exists(select * from sc where sno=student.sno ando=1)10.查询没有选修了 1号课程的学生:select sname from student where not exists(select * from sc where sno=student.sno ando=1)11,查询选修了全部课程的学生:SQL Server 中: select sname from student where not exists(select * from course wh
9、ere not exists(select * from sc where sno=student.sno ando=o)11,查询至少选修了学生95002选修的全部课程的学生的学号:SQL Server 中: select 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 ando=B.cno)12.求没有人选修的课程号cno和cnamecname: selecto,cname from cour
10、se C where not exists(select * from sc where o=C.cno )13*.查询满足条件的（sno,cno）对，其中该学号的学生没有选修该课程号cno 的课程SQL Server 中: select sno,cno from student,course where not exists(select * from sc whereo=o and sno=student.sno)14*.查询每个学生的课程成绩最高的成绩信息(sno,cno,grade): select * from sc A where grade=(select max(grade)
11、from sc where sno=A.sno )二 F. F .! 7 _n 7宜.，,很.；,曲-鼻口钥I 3W八 |日皿 |定际鱼| &a、 一祖J KM 1169让血皿山打.J 159t Ll bEtrk U B-adalH- |J kdb in |J wmaEk J jabrF U LVJb tU TT5ub H (J TTSt-yii /如册FF二J胃泅函数H _J器商可致(* _J日明*HJ1密的Ih】敏学函数FT _l律:含西勒：H _1元t!其函敦4 一|斐也函致ih j李奇勇13散FF _1 .S.iifiSt|卜二|.赫统计戮 _H-Esrt 和 l”4 函E!I卜_|行臭！FF ._| .呆冷涵芟32近授：L 叫思考:如何查询所有学生都选修了的课程的课程号cno?selecto from sc group byo having count(*)=(select count(*) from student)
