数据库SQL练习1-16.doc

数据库习题之基本操作（插入、修改、删除、更新）主要用到插入insert、修改alter、删除delete、更新update、相关子查询和连接（左、右、外） -1、检索所有学生中年龄最大的学生的姓名及年龄。 select sname,sage from student where sage=all(select sage from student );-2、求每一个学生的最高分和最低分。 select sno,max(grade),min(grade) from sc group by sno; -3、查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓名，成绩，并按成绩降序排列。 -select * from student select * from sc -原来没有 -修改出一条记录 update student set ssex=m where sno=004 select * from studentselect student.sno,student.sname,sc.grade from student,sc where student.sno=sc.sno and student.sno =( select sno from sc where cno=c05 intersect select sno from student where sdept=CS and ssex=m )and o=c05 order by grade DESC; -4、检索选修了“C语言”课程的学生的姓名（可用子查询IN或Exists） select sname from student where sno in (select sno from sc where cno in (select cno from course where cname=c语言) select sname from student where exists( select * from sc where exists( select * from course where cname=C语言 and cno=o and sc.sno=student.sno) -5、检索选修了课程号为C01或C02课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。 select student.sname, ame, sc.grade from student,sc,course where sc.grade70 and o=o and sc.sno=student.sno and o in (c01,c02) -6、检索所有学生的姓名、所选课程的课程名和成绩以及课程号，并且按成绩的降序和课程号的升序进行排列（使用外连接将没有选课的同学列出来,还用到相关子查询）。 -. select student.sname,ame,sc.grade,o from student left outer join sc on(student.sno=sc.sno) left join course on o=o order by sc.grade DESC,o ASC; -7. 列出没有选课的学生姓名 select sname from student where not exists( select * from sc where sno=student.sno)-8. 列出平均分最高的学生所在系的所有学生的姓名 select sname from student where sdept=( select sdept from student where sno=( select sno from sc group by sno having Avg(grade)=all (select Avg(grade)from sc group by sno)-9.查询CS系C01课程的成绩比C01课程的平均分高的学生学号 select sno from student where sno in( select sno from sc where grade=( select Avg(grade) from sc group by cno having cno=c01) and cno=c01) and sdept=cs -10.查询既选修了C01又选修了C02的学生 select sno from sc where cno=c01 intersect select sno from sc where cno=c02 -11.统计及格的课程数在四门以上的学生所选课程的平均成绩。最后按降序列出平均成绩名次名单来。 select sno,Avg(grade) from sc where grade=60 group by sno having count(*)=4 order by Avg(grade) DESC -12.检索所有CS系学生都选修了的课程（列出课程号） select cno from sc group by cno having count(*)=( select count(*) from student where sdept=cs) -13.查询年龄高于其所在系的平均年龄的学生姓名 select sname,sdept from student where sage( select Avg(sage) from student where sdept=cs) and sdept=cs union select sname,sdept from student where sage( select Avg(sage) from student where sdept=e) and sdept=e union select sname,sdept from student where sage( select Avg(sage) from student where sdept=c) and sdept=c -用相关子查询 select sname,sdept from student x where sage(select Avg(sage) from student y where y.sdept=x.sdept) -14.查询每位同学的选课中成绩最高的课程对应的学号，姓名，课程名，成绩 -不用视图 select student.sno,student.sname,ame,sc.grade from student,sc,course where o=o and sc.sno=student.sno and sc.grade=( select Max(grade) from sc where o= o) -相关子查询 -每位同学的选课中最高成绩 create view Stu_Mg as select sno,Max(grade) 最高成绩 from sc group by sno -最高成绩的课程号* -select sc.sno,o,Stu_Mg.最高成绩 from sc ,Stu_Mg where sc.sno=Stu_Mg.sno and sc.grade=Stu_Mg.最高成绩-结论 select y.sno 学号,x.sname 姓名,ame 课程名,y.grade 成绩 from student x ,sc y,course where x.sno=y.sno and o=o and exists( select * from sc ,Stu_Mg z where sc.sno=z.sno and sc.grade=z.最高成绩 and z.最高成绩=y.grade and z.sno=x.sno )-15.为MA系学生选修必修课C05 insert into student (sno,sname,sage,ssex,sdept) values(012,李默默,19,m,ma) insert into sc select sno,c05,null from student where sdept=maselect * from sc -16.将CS系,C01课程学生的成绩加10分 update sc set grade=grade+10 where cno=c01 and sno in( select sno from student where sdept=CS)-17.将每位同学的最低分加10分