数据库作业3

根据教材上的teach数据库（student、course、sc），上机验证书上第三章例3.49到例3.68的所有例程，分别给出代码，对应将运行结果截图形成一个附件提交。注意压缩一下截图的大小。

例3.49 查询每个学生及其选修课程的情况

select student.*,sc.* from student,sc where student.sno=sc.sno;

例3.50 对例3.49用自然连接完成

select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;

例3.51 查询0202号课程且成绩在90分以上的所有学生的学号和姓名

select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno='0202' and sc.grade>90;

例3.52 查询每一门课的间接先修课（即先修课的先修课）

select first.cno,second.cpno from course as first,course as second where first.cpno=second.cno;

例3.53 外连接改写例3.49

select student.sno,sname,ssex,sage,sdept,cno,grade from student left outer join sc on (student.sno=sc.sno);

例3.54 查询每个学生的学号、姓名、选修的课程名及成绩

select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno;

例3.55 查询与“刘贤”在同一个系学习的学生

select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘贤');

例3.56 查询选修了课程名为“数据结构”的学生学号和姓名

select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname='数据结构'));

例3.57 找出每个学生超过他自己选修课程平均成绩的课程号

select sno,cno from sc x where grade>=(select avg(grade) from sc y where y.sno=x.sno);

例3.58 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

select sname,sage from student where sage<any(select sage from student where sdept='CS') and sdept<>'CS';

例3.59 查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄

select sname,sage from student where sage<all(select sage from student where sdept='CS') and sdept<>'CS';

例3.60 查询所有选修了0202号课程的学生姓名

select sname from student where exists(select * from sc where sno=student.sno and cno='0202');

例3.61 查询没有选修1号课程的学生姓名

select sname from student where not exists (select * from sc where sno=student.sno and cno='1');

例3.62 查询选修了全部课程的学生的姓名

select sname from student where not exists (select * from course where not exists (select * from sc where sno=student.sno and cno=course.cno));

例3.63 查询至少选修了学生201215122的全部课程的学生号码

select distinct sno from sc scx where not exists (select * from sc scy where scy.sno='201215122' and not exists (select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno));

例3.64 查询计算机科学系的学生及年龄不大于19岁的学生

select * from student where sdept='CS' union select * from student where sage<=19;

例3.65 查询选修了课程0202或者选修了课程0204的学生

select sno from sc where cno='0202' union select sno from sc where cno='0204';

例3.66 查询计算机科学系的学生与年龄不大于19岁的学生的交集

select * from student where sdept='CS' intersect select * from student where sage<=19;

例3.67 查询既选修了课程0204又选修了课程0210的学生

select sno from sc where cno='0204' intersect select sno from sc where cno='0210';

例3.68 查询计算机科学系的学生与年龄不大于19岁的学生的差集

select * from student where sdept='CS' except select * from student where sage<=19;