Question

查询各课程的平均成绩并按成绩降序排列

Select cno,avg(grade) from sc group by cno and order by avg(grade) desc为什么会出错

Answer 1

一、语句为：
SELECT S.学号，姓名，AVG(成绩)平均成绩 FROM S，SC
WHERE S.学号=SC.学号
GROUP BY S.学号 HAVING COUNT(*)＞5 ORDER BY 3 DESC

二、注意要点：
1）题目要求查询平均成绩，表中不存在“平均成绩”字段，需要使用VAG函数。
2）学生表student和成绩表score都含有“学号”字段，在查询时需指明学号属于哪个表。
3）GROUP BY短语对于查询结果进行分组，后跟随HAVING短语来限定分组必须满足查询选修课在5门以上的学生，必须在分组后再统计，所以CONM(大)>S应在HAVING后。

Answer 2

SQL指令：

SELECT stu.name,a4.java,a4.mysql,a4.html,a4.`总成绩` from
(select a.stu_id,a.score 'java',a1.score 'mysql',a2.score 'html',a3.sum1 '总成绩' from
(select sc.score,sc.stu_id from score sc where sc.subject_id=10001) a
LEFT JOIN
(select sc.score,sc.stu_id from score sc where sc.subject_id=10002) a1
on a.stu_id = a1.stu_id LEFT JOIN
(select sc.score,sc.stu_id from score sc where sc.subject_id=10003) a2
on a1.stu_id=a2.stu_id
LEFT JOIN (SELECT sum(sc.score) sum1 ,sc.stu_id from score sc GROUP BY sc.stu_id) a3
on a2.stu_id = a3.stu_id
ORDER BY a3.sum1 desc) a4
LEFT JOIN student stu on a4.stu_id = stu.id

Answer 3

Select cno,avg(grade)
from sc
group by cno
order by avg(grade) desc

Answer 4

Select cno,avg(grade) avg_grade from sc group by cno and order by avg_grade desc

Answer 5

你把and去掉就没问题了。