Question

SQL语句求答案

Student(Sno,Sname,Sage,Ssex) 学生表
Course(Cno,Cname,Tno) 课程表
SC(Sno,Cno,score) 成绩表
Teacher(Tno,Tname) 教师表
应用前面的学生选课数据库完成如下查询。
（1）查询所有同学的学号、姓名、选课数、总成绩
（3）查询选修“张华”老师所授课程的学生中，成绩最高的学生姓名及其成绩 。
（4）查询平均成绩大于85的所有学生的学号、姓名和平均成绩。 （5）查询课程名称为“数据库”，且分数低于60的学生姓名和分数。
（6）查询任何一门课程成绩在70分以上的姓名、课程名称和分数。

Answer 1

1.select s.sno,max(Sname),count(c.Cno) as courseNum,sum(sc.score) as sumScore from Student s,Course c,SC sc--先连接三张表where s.Sno=SC.Sno and SC.Cno=c.Cno--按学号分组group by s.Sno 3.select s.Sname ,sc.score from studet s ,sc
where s.sno=sc.sno
and sc.score in( --先查出张华的课程的最大成绩 selec max(sc.score) from sc,teacher t,course c
where sc.cno=c.cno and c.tno=t.tno
and t.tname = '张华'
)--再用in查询成绩是这个成绩的人。 4. select s.sno, s.sname,temp.avgScore from
student s,
--查出所有学生平静成绩做一个临时表
(
select avg(sc.score) as avgScore,sc.sno
from sc group by sc.sno
) tempWHEREtemp.avgScore>85
and s.sno=temp.sno 5. select s.sno,s.sname ,temp.score
from student s,
(
--小于60分数的人查出来做一个临时表
select c.cname ,sc.score,sc.sno
from course c,sc
where sc.score<60 and c.cname='数据库'
) tempwhere s.sno=temp.sno 6.select s.sname,c.cname,sc.core
from student s ,course c,sc
where s.sno=sc.sno
and c.cno=sc.cno
and sc.score>70 3.4.5题颇有难度，一般学生肯定是写不出来的~~

Answer 2

（1） select a.sno, a.sname, count(b.cno), sum(c.score)
from student a, course b, sc c
where a.sno = c.sno
and b.cno = c.cno
group by a.sno, a, sname;（3）select a.sname, c, score
from student a, sc c
where c.sno = a.sno
and c.score in (select max(c.score)
from sc c, teacher b, course d
where c.cno = d.cno
and d.tno = b.tno
and b.tname = '张华');
（4）
select a.sno,a.sname,b.avgs
from student a,
(
select avg(score) avgs,sno
from sc group by sno
) b
where a.sno=b.sno
（5）
select a.sname, b.score
from student a, sc b
where b.cno in (select cno from course where cname = '数据库')
and b.score < 60
and a.sno=b.sno
（6）
select a.sname, b.cname, c.score
from student a, course b, sc c
where a.sno = c.sno
and b.cno = c.cno
and a.sno not in (select distinct sno from sc where score < =70)