(1)查询C01课程成绩不为Null的学生的姓名和成绩。
分析:因为涉及到课程成绩和学生姓名,需要对student表和sc表进行连接。
代码:
select sname,grade from student,sc where student.sno=sc.sno and cno='C01' and grade is not null;
结果:
SNAME GRADE
-------------------- ---------------------------------------
葛灵 68
岳林月 60
姬胜俊 54
马源 40
翁印 98
(2)查询平均分高于70分的女同学的学号,姓名,平均成绩。
select student.sno ,sname,avg(grade) from sc ,studentwhere student.sno=sc.sno and ssex='f'
group by student.sno,sname having avg(grade)>70;
SNO SNAME AVG(GRADE)---------- -------------------- ----------001 葛灵 73.2
(3)查询ma系学生“英语”课程的最高分,列出姓名和最高分。
select * from(select sname,grade from sc,course,student wheresc.sno=student.sno and course.cno=sc.cno and sdept='MA' and cname='英语'and grade is not nullorder by grade desc)where rownum=1;SNAME GRADE-------------------- ---------------------------------------葛灵 86
(4)查询总学分在8分以上的学生的平均成绩,列出学号,平均成绩
select sc.sno,sum(credit),avg(grade) from course,scwhere course.cno=sc.cno group by sc.sno having sum(credit)>8;SNO SUM(CREDIT) AVG(GRADE)---------- ----------- ----------001 18 73.2002 15 68003 18 70004 10 65.3333333005 9 67
(5)查询所有18岁以上学生的选课门数,列出学号,姓名,年龄,选课门数。
select sc.sno,sname,sage,count(cno) from sc,studentwhere student.sno=sc.sno and sage>18group by sc.sno,sname,sage;SNO SNAME SAGE COUNT(CNO)---------- -------------------- --------------------------------------- ----------001 葛灵 19 5002 岳林月 25 4004 马源 20 3005 翁印 23 3
(6)删除所有MA系不及格的选课信息。
delete sc where sc.sno in
(select sc.sno from student,sc where student.sno=sc.sno and sdept='MA' and grade<60)and sc.cno in
(select sc.cno from student,sc where student.sno=sc.sno and sdept='MA' and grade<60);9 rows deleted
(7)将平均分不及格的学生成绩修改为空。(以70分为例)
update sc set grade=null where sno in(select sno from (select sno ,avg(grade)g from sc group by sno) where g<70);10 rows updated