3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
查询选修的课程的成绩大于该课程的平均成绩的所有学生姓名。
有一个“学生－课程”数据库，数据库中包括三个表：
(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) 为关键字。
查询选修的课程的成绩大于该课程的平均成绩的所有学生姓名。
...全文
请发表友善的回复…
发表回复
乌啦啦 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
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.gradezjexe 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_avgdawugui 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_avglizhongheng 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
Where Sc.CNo=c.CNo1 and Sc.grade>c.Grade