sql 11章上机练习

  --上机1
  --1
  SELECT GradeId ,sum (ClassHour) FROM [MySchool].[dbo].[Subject]
  group by  GradeId
  order by sum (ClassHour)
  --2
  SELECT StudentNo,AVG(StudentResult)as 平均分
  FROM [MySchool].[dbo].[Result]
  group by StudentNo
 --3
   SELECT SubjectId,AVG(StudentResult)as 平均分
  FROM [MySchool].[dbo].[Result]
  group by SubjectId
  order by AVG(StudentResult) desc
  --4
 SELECT StudentNo,sum(StudentResult)as 总分
  FROM [MySchool].[dbo].[Result]
  group by StudentNo
  order by sum(StudentResult)
  
  --上机2
  
  --1
  SELECT GradeId,count(ClassHour)as 课程数
  FROM [MySchool].[dbo].[Subject]
  group by GradeId 
  --2
  SELECT GradeId,AVG(DATEDIFF(dd,BornDate,getdate())/365)as 平均年龄
  FROM [MySchool].[dbo].[Student]
  group by GradeId
  --3
   SELECT GradeId as 学期,COUNT(StudentName)as 学生人数
  FROM [MySchool].[dbo].[Student]
  GROUP BY GradeId
  --4
   SELECT StudentNo as 学员,AVG(StudentResult)as 平均分
  FROM [MySchool].[dbo].[Result]
  group by StudentNo
   HAVIN