数据库：嵌套查询和视图操作

（一） 嵌套查询
1.  求选修了’MA’的学号和姓名。
--方法1：
select student.sno, sname from student, sc, course where (sc.sno=student.sno and sc.cno=course.cno and cname = '数学');
--方法2：
select student.sno, sname from student where sno in (select sc.sno from sc, course where sc.cno=course.cno and cname = '数学');
2.  查询与刘明在同一个系学习的学生。
select * from student a where sdept in (select sdept from student b where (b.sname='刘明'));
3.  求选修1号课程的成绩高于刘明的成绩（指刘明选修的所有的课程的成绩）的学生学号及成绩。
select sno, grade from sc where (grade > all (select grade from student, sc where (sc.sno=student.sno and sname='刘明') ) and cno='1');
4.  求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）。
--方法1：
select * from student a where (sage < any (select sage from student b where b.sdept= 'CS') and a.sdept != 'CS');
--方法2：
select * from student a where (sage < (select max(sage) from student where sdept='CS') and a.sdept != 'CS');
5.  求其他系中比计算机系学生年龄都小的学生姓名及年龄。
--方法1：
select sname,sage from student a where (sage < all (select sage from student b where b.sdept= 'CS') and a.sdept != 'CS');
--方法2：
select sname,sage from student a where (sage < (select min(sage) from student where sdept='CS') and a.sdept != 'CS');
6.  求没有选修3号课程的学生姓名和学号。
select student.sno, sname from student where not exists (select * from sc where student.sno=sc.sno and cno='3');


7.  查询选修了全部课程的学生姓名和学号。
select  student.sno, sname from student where not exists (select * from course where not exists
(select * from sc where (sc.sno=student.sno and sc.cno=course.cno)));
8.  求至少选修了学号为“20070002”的学生所选修全部课程的学生学号和姓名。
select sno, sname from student where sno in (select distinct sno from sc scx where not exists (select * from sc scy where scy.sno='20070002' and not exists 
(select * from sc scz where scz.cno=scy.cno and scz.sno=scx.sno)));
9.  求选修课程超过2门的学生的学号和姓名。
select student.sno, sname from student where sno in(select sno from sc group by sno having (count(distinct cno)>2));

（二）、数据更新

1.插入数据*
1）向Student表中插入2行数据，1行为你的信息，另一行自定。

insert into student (sno,sname,ssex,sage,sdept) values ('20144085','张江涛','男','20','CS');
insert into student (sno,sname,ssex,sage) values ('20140001','李明','男','18');
2）向Course表中插入数据，1行为本门课程的信息，另一行自定。
insert into course ( CNO, CNAME, CPNO, CCREDIT) values ('8','DataBase','5','2');
insert into course ( CNO, CNAME, CPNO, CCREDIT) values ('9','计算机网络','3','2');
3）向SC表中插入数据，插入你的这门课程的选课信息。
insert into sc (SNO, CNO, GRADE) values ('20144085','8','95');

2.修改数据

1）将姓刘的同学删除。
delete from student where sname like '刘%';
2）将’CS’系同学的选课信息中的成绩置0。
update sc set grade = 0 where 'CS' = (select sdept from student where student.sno=sc.sno); 
3.删除数据

1）删除和 ’刘明’ 在同一个系的学生的信息。
delete from student where sdept in (select sdept from student where sname = '刘明');
2）删除’CS’系同学的选课信息。
delete from sc where 'CS' = (select sdept from student where sc.sno = student.sno);