6、求各系学生的平均成绩,并把结果存入数据库Dept_grade 表,Dept_grade 创建如下; create table Dept_grade ( Sdept char(15), Avg_grade smallint )
insert into Dept_grade
select sdept,AVG(grade)
from sc,s
where sc.sno=s.sno
group by sdept
--13. 统计每位学生选修课程的门数、学分及其平均成绩;
select sno,count(sc.cno),count(ccredit),avg(grade)
from sc,course
where sc.cno=course.cno
group by sno
go
--14. 统计选修每门课程的学生人数及各门课程的平均成绩;
select cno,count(sno),avg(grade)
from sc
group by cno
go
--[例31] 求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno
go
--[例32] 查询选修了3门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3
go