1.学科表(表名 c)

2.学生表（表名 s）

3.成绩表（表名 sc）

问题：查询两门以上（含两门）不及格课程的学生姓名及其平均成绩

select 
MAX(s.sname) as 姓名,AVG(s1.scgrade) as 平均成绩
FROM
s 
INNER JOIN sc on s.sno = sc.sno and sc.scgrade < 60

INNER JOIN c on c.cno = sc.cno

RIGHT JOIN sc as s1 on s1.sno = s.sno

GROUP BY s.sno
HAVING COUNT(c.cno) >=2;

查询结果：

注意：这里计算平均成绩是右链接 sc表，并起别名为s1 ，然后取 s1 表的 scgrade 字段计算平均数； 第一个sc表只有不及格学科的记录，并不是所有学科成绩记录。

select 
MAX(s.sname) as 姓名,AVG(s1.scgrade) as 平均成绩,AVG(sc.scgrade) as 不及格学科平均成绩
FROM
s 
INNER JOIN sc on s.sno = sc.sno and sc.scgrade < 60

INNER JOIN c on c.cno = sc.cno

RIGHT JOIN sc as s1 on s1.sno = s.sno

GROUP BY s.sno
HAVING COUNT(c.cno) >=2;

查询结果：