ACCP S1 SQL第十一章上机练习

上机1

select GradeId,sum(ClassHour) as 总学时数 from Subject 
group by GradeId order by sum(ClassHour)

select StudentNo,AVG(StudentResult) as '平均分' from Result
group by StudentNo

select SubjectId, AVG(StudentResult) as '平均分' from Result
group by SubjectId order by AVG(StudentResult) desc

select StudentNo,sum(StudentResult) as '总分' from Result
group by StudentNo order by sum(StudentResult) desc

上机2

select GradeId,COUNT(*)as '课程数' from Subject
group by GradeId having sum(ClassHour)>50

select GradeId,AVG(DATEDIFF(dd,BornDate,GETDATE())/365)as '平均年龄' from Student
group by GradeId

select GradeId,COUNT(*)as '学生人数' from Student
where Address like '北京%' group by GradeId

select StudentNo,AVG(StudentResult)as '平均分' from Result
group by StudentNo having AVG(StudentResult)>=60 order by AVG(StudentResult) desc

select Subjec