发布时间 : 星期一 文章SQL数据库原理实验指导书及答案更新完毕开始阅读ea1a496ea9956bec0975f46527d3240c8547a141
where student.sno=sc.sno and cno in
(select cno from course where cname='数学' );
② 求C1课程的成绩高于张三的学生学号和成绩。 select student.sno,grade from student,sc
where student.sno=sc.sno and cno='1' and grade> (select grade from sc where sno in
(select sno from student where sname='张立') );
③ 求其他系中比计算机某学生年龄小的学生（即求年龄小于计算机系年龄最大者的学生） select * from student
where sdept<>'cs' and sage< (select max(sage) from student where sdept='cs' );
13
④ 求其他系中比计算机系学生年龄都小的学生。 select * from student
where sdept<>'cs' and sage< (select min(sage) from student where sdept='cs' );
⑤ 求进修了C2课程的学生姓名。 select sname from student,sc
where student.sno=sc.sno and cno='2'; ⑥ 求职没有选修C2课程的学生姓名。 select sname from student,sc where not exists (select * from sc
where sno=student.sno and cno='2' );
⑦ 查询送修了全部课程的学生姓名。 select sname from student
14
where not exists (select * from course
where not exists( select * from sc
where sno=student.sno and cno=course.cno));
⑧ 求职至少选修了学生为“S2”的学生所选修的全部课程的学生学号的姓名 select distinct sno from sc scx where not exists ( select * From sc scy
Where scy.sno=’200215122’and not exists (select * From sc scz
Where scz.sno=scx.sno and scz.cno=scy.cno) );
用T-SQL语句表示，在学生选课库中实现其数据嵌套查询操作。
２提高操作实验
按以下要求用T-ＳQL语句表示并通过SQL Server Query Analyzer实现查询操作。 ① 找出上海厂商供应的所有零件号码。 select pno,jno,qty from s,spj
15
where s.sno=spj.sno and spj.sno in (select sno from s
where city='上海' ) ② 找出使用上海产的零件的工程名称。 select jno,qty,pno from s,spj
where s.sno=spj.sno and spj.sno in (select sno from s
where city='上海' )
③ 找出没有使用天津产零件的工程号码。
select pno (没调试出来的) from spj
where not exists (select * from s,spj
where s.sno=spj.sno and s.city='天津' )
３选择操作实验
16