Question

1,统计各系系各门课程成绩的平均成绩 2，查询选秀计算机基础和离散数学的学生的学好和平均成绩

建好的三张表如图所示

Answer 1

--6.3.5 统计各系各门课程的平均成绩
select dept,course.cno,course.cname,grade=avg(grade)
from student,SC,course
where student.sno = SC.sno
and SC.cno = course.cno
group by course.cname,course.cno,dept

--6.3.6 查询选修计算机基础和离散数学的学生学号和平均成绩
select distinct sno,grade=avg(grade)
from SC,course
where SC.cno in(select cno from course where cname = '计算机基础' or cname = '离散数学')
group by sno

Answer 2

1.统计各系各门课程的平均成绩

select Sdept,Cno,avg(Grade) as averageG

from Student

left join SC on  SC.Sno=Student.Sno --左连接，以左边为主，出现空数据时会保留左边的行，即student表的行

group by Cno,Sdept

2.查询选修计算机基础和离散数学的学生学号和平均成绩

select Student.Sno,avgG=avg(Grade)

from SC,Student,Course

where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname in('计算机基础','离散数学')

group by Student.Sno