c# 11章

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [StudentNo]
      ,[LoginPwd]
      ,[StudentName]
      ,[Sex]
      ,[GradeId]
      ,[Phone]
      ,[Address]
      ,[BornDate]
      ,[Email]
  FROM [MySchool].[dbo].[Student]
  上机一:
  select * from dbo.Subject
  1:
  select  count(GradeId)as 按年积分,SUM(ClassHour)as 总学时, from dbo.Subject
  group by GradeId
  order by GradeId
  2:
  select count(StudentNo)as an参加考试, AVG(StudentResult)as 平均分 from dbo.Result
  group by StudentNo
  3:select COUNT(SubjectName) as 按每门课分 ,SubjectName,
  AVG(StudentResult)平均分  from dbo.Result r, dbo.Subject s  where r.SubjectId=s.SubjectId
  group by SubjectName
  order by AVG(StudentResult)desc
  4:
   select count(StudentNo)as 按参加考试, sum(StudentResult)as总分 from dbo.Result
    group by StudentNo
    order by  sum(StudentResult)desc
    
 上机二:
 1:
 select GradeId ,ClassHour as 超过50的课程数 from dbo.Subject
 group by GradeId ,ClassHour
 having ClassHour>50
 2: 
 select GradeId, avg(datediff(dd,BornDate,GETDATE())/365)as 平均年龄 from dbo.Student
  group by GradeId
 3:
 select GradeId, count( StudentNo) as 学期人数 from dbo.Student
 where Address like '北京%'
 group by GradeId
 4:
 select * from dbo.Result
 select COUNT(StudentNo) ,AVG(StudentResult)as 考试平均成绩 from dbo.Result
 group by StudentNo
 having AVG(StudentResult)>60
 order by AVG(StudentResult) desc
 5:
 select AVG(StudentResult)as 平均分 from dbo.Result 
 where ExamDate='2013-12-22 '
 group by SubjectId
 having AVG(StudentResult)>60
 
 6:
 select StudentNo,COUNT(StudentResult)as 考试不及格次数 from dbo.Result
 group by StudentNo
 having  COUNT(StudentResult)<60
 上机三:
 1:
 select s.StudentName as 姓名,s.Phone as 姓名 ,SubjectId 班级
 from dbo.Student s, dbo.Subject r
 where s.GradeId=r.SubjectId
 
 2:
  select s.GradeId as 年级名 ,SubjectName as 科目名,r.ClassHour as 学时
  from dbo.Student s ,dbo.Subject r
  where  s.GradeId=r.SubjectId and SubjectId='1'
  
  3:
  select j.SubjectId as 编号,StudentName as 姓名,StudentResult 成绩as,ExamDate as 考试日期
  from dbo.Student s,dbo.Result r,dbo.Subject j
  where s.StudentNo=s.StudentNo and s.GradeId=j.GradeId and j.SubjectId=1
  4:
  
  select StudentNo as 学号 ,SubjectName as 姓名,StudentResult as 成绩,ExamDate as 考试日期
  from dbo.Result r,  dbo.Subject s 
  where  r.SubjectId=s. SubjectId and StudentNo='S2102007'
  
 5:
  select StudentNo as 学号 ,r.SubjectId as 科目, StudentResult as 分数, ExamDate as 考试日期
 from dbo.Result r,dbo.Subject s
 where r. SubjectId=s.GradeId
 上机四:
 1:
 select StudentNo as 学号 ,SubjectName as 姓名,r.SubjectId as 科目,StudentResult as 成绩
 from dbo.Result r,  dbo.Subject s 
  where  r.SubjectId=s. SubjectId 
  2:
 select s.StudentNo as 学号 ,s.StudentName as 姓名 ,ExamDate as 考试日期,r.SubjectName
 from dbo.Student s,dbo.Subject r,dbo.Result j
 where  j.SubjectId=r.SubjectId and s.GradeId=r.GradeId  and r.SubjectName LIKE '%SCH%'
 
 3:`
 
 上机 五:
 1:
 select * from dbo.Subject s left join  dbo.Result r
 on  s.SubjectId=r.SubjectId
 
 --2
 
 select *from dbo.Grade  g right join dbo.Subject s
 on g.GradeId=s.GradeId