--2、查询平均成绩大于全班平均成绩的学生学号、姓名及平均成绩。
我的代码
select student.sno,sname,sdept,count(sc.cno) 选课门数,
avg(grade) 平均成绩, max(grade) 最高成绩, min(grade) 最低成绩
from student,sc,course
where student.sno=sc.sno
group by student.sno,sname,sdept
order by 平均成绩 asc
答案
select student.sno,sname,avg(grade) avg_grade
from student,sc
where student.sno=sc.sno
group by student.sno,sname
having avg(grade)>(select avg(grade) from sc)
添加了course表但未连接,没有连接条件就是笛卡尔积
正确显示
--4、查询选修了“数据库”且“数据库”成绩大于自己选修课程平均分的学生学号、姓名、所在院系及所有选修的课程名和成绩。
select student.sno,sname,sdept,cname,grade
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno
and student.sno in (
select sc1.sno
from sc sc1,course
where sc1.cno=course.cno and cname='数据库'
and grade>(
select avg(grade)
from sc sc2
where sc1.sno=sc2.sno
)
)