数据库实验二记录

二（一）1.select sno,sname from Studentwhere sdept='Math';2.select sno from Student where snoin(select sno from SC);3.select sno,grade from Sc where cno='2' order by grade desc,sno asc;4.select sno,grade*0.8 from Sc where cno='2' and grade between 80 and 90;5.select * from Student where sname like '张%' and sdept in('MA','CS');6.select sno,cno from sc where grade is not null;7.select cno,count(Sno) numfrom sc group by cno;（二）1.select student.*,cno from student,sc where student.sno=sc.sno;2.select student.*,cno from student left join sc on student.sno=sc.sno;3.select x.sno,x.sname,y.grade from student x , sc ywhere(y.cno=1 and y.grade>90 and y.sno=x.sno);4.select y.cno,x.cpno from course x , course  ywhere(y.cpno=x.cno);5.select * from student where sdept=(select sdept from student  where sname='刘晨');6.select student.sno,sname from sc,studentwhere sc.cno =(select cno from course  where cname='信息系统') and sc.sno=student.sno ;7.select sno,avg(grade) from sc group by sno having avg(grade)>80;8.select sno from sc group by sno having count(cno)>1;9.查询每门课程的名称和选课人数。（没人选的课人数为0）select A.cname,count(B.cno) from course A left join sc B on  A.cno=B.cnogroup by B.cno,A.cname;(三)1.select sno,sname from student where sno in(select sno from sc where cno= (select cno from course where cname='信息系统'));2.select * from student where sdept=(select sdept from student where sname='刘晨');3.select sno, grade from sc where (grade > all (select grade from student, sc where (sc.sno=student.sno and sname='刘晨') )and cno='1');4.select * from student where(sage<(select max(sage) from student where sdept='CS') and sdept!='CS');5.select sname,sage from student where(sage < all(select sage from student where sdept='CS') and sdept!='CS');6.select sname from student where not exists(select * from sc where sc.cno=3 and sc.sno=student.sno);7.select   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.select sno, sname from student where sno in (select distinct sno from sc scx where not exists (select * from sc scy where scy.sno='200215121' and not exists (select * from sc scz where scz.cno=scy.cno and scz.sno=scx.sno)));9.select student.sno, sname from student where sno in(select sno from sc group by sno having (count(distinct cno)>2));(四)1.create view cs_view (sno,sname,sage) asselect sno,sname,sage from student where sdept='CS';select * from cs_view;2.create view one_student (sno,sname,sage) asselect cs_view.sno,sname,sage from cs_view,sc where(cs_view.sno=sc.sno and sc.cno='1');select * from one_student;3.create view name_avergrade (sno,averg) asselect sno , avg(grade) from sc group by sno;select * from name_avergrade;4.create view F_stu (sno,sname,sdept,sex) asselect sno,sname,sdept,ssex  from student where ssex='女' with check option ;select * from F_STU;5.insert into F_STU values('200215129','smith','MA');提示没有足够的值6.insert into F_STU values('200215129','smith','MA','女');1行已插入。5.6区别在于数据完整与否。