导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

查询选修的课程的成绩大于该课程的平均成绩的所有学生姓名。

wxxloveu 2007-12-15 10:00:27

有一个“学生-课程”数据库,数据库中包括三个表:
(1) “学生”表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成,可记为: Student(Sno,Sname,Ssex,Sage,Sdept) Sno 为关键字。
(2) “课程”表Course由课程号(Cno)、课程名(Cname)、任课教师(Cteacher)、学分(Ccredit)四个属性组成,可记为: Course(Cno,Cname, Cteacher,Ccredit) Cno为关键字。
(3) “学生选课”表SC由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成,可记为: SC(Sno,Cno,Grade) (SNO, CNO) 为关键字。

查询选修的课程的成绩大于该课程的平均成绩的所有学生姓名。
...全文
794 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
乌啦啦 2011-12-06
SELECT STUDENT.SNAME,SC.CNO,SC.GRADE,X.AVG
FROM STUDENT,SC,(SELECT COURSE.CNO,AVG(GRADE) AS GRADE_AVG
FROM COURSE,SC GROUP BY COURSE.CNO) AS T,
(SELECT SC.CNO,AVG(GRADE) AS AVG
FROM SC
GROUP BY CNO) AS X
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=T.CNO AND SC.GRADE>T.GRADE_AVG AND X.CNO=SC.CNO
GROUP BY SC.CNO,STUDENT.SNAME,SC.GRADE,X.AVG
回复
wxxloveu 2007-12-18
select(select sname from student where student.sno=sc.sno)as sname,
(select cname from course where cno=sc.cno)as cname
from sc,(select cno as cno,avg(grade) as grade from sc group by cno)as c
Where sc.cno=c.cno and sc.grade> c.grade
回复
zjexe 2007-12-15
select tc.* from SC ta,(select Cno,avg(Grade) as Grade from SC group by Cno) tb where ta.Cno=tb.Cno,Student tc and ta.Grade>tb.Grade and tc.Sno=ta.Sno
回复
dawugui 2007-12-15
--课程表在这个查询中没有用.

select a.sname from student a , course b,
(select cno , avg(grade) grade_avg from course group by cno) t
where a.sno = bsno and b.cno = t.cno and b.grade > t.grade_avg
回复
dawugui 2007-12-15
--课程表在这个查询中没有用.
select a.student.sname from student a , course b,
(select cno , avg(grade) grade_avg from course group by cno) t
where a.sno = bsno and b.cno = t.cno and b.grade > t.grade_avg
回复
lizhongheng 2007-12-15
select cs.cno,student.sname,cs.garde from sc left join student on (student.sno=sc.sno) 
left join
(select cno,avg(grade) as avgGarde from sc) sc1 on (sc.cno=sc1.cno and sc.grade>sc1.avggarde)
回复
lucky749 2007-12-15
select (select SName from Student where Student.SNo=Sc.SNo) as SNo,(select CName from Course where Cno=Sc.CNo) as Cno1 from Sc,(select CNo as CNo1,avg(Grade1) as Grade from Sc group by CNo) as c
Where Sc.CNo=c.CNo1 and Sc.grade>c.Grade
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告