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

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) 为关键字。

查询选修的课程的成绩大于该课程的平均成绩的所有学生姓名。
...全文
6122 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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

34,837

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧