c#第十一章

SELECT TOP 1000 * FROM [MySchool].[dbo].[Student] --第十一章 P241页  -- 1.男女人数  select Sex as 性别,COUNT(sex)as 人数  group by Sex  --2.各年级总人数    select GradeId as 年级,COUNT(StudentNo)as 总人数  from dbo.Student  group by GradeId  --3.每个科目平均分并且排序  select SubjectId as 科目,AVG(StudentResult) as 平均分  from dbo.Result  group by  SubjectId order by AVG(StudentResult)   --group by 多列分组用法 SELECT COUNT(StudentNo) as 人数,GradeId as 年级,Sex as 性别 from dbo.Student group by GradeId,Sex order by GradeId --having 分组筛选用法 SELECT COUNT(StudentNo) as 人数,GradeId as 年级 from dbo.Student  group by GradeId having COUNT(StudentNo)>15 SELECT SubjectId as 年级 ,AVG(StudentResult)AS 平均分,MAX(StudentResult)AS 最大值,MIN(StudentResult)AS 最小值 from dbo.Result where SubjectId=2 group by SubjectId having AVG(StudentResult)>60  --内连接 方法1 select dbo.Result.StudentNo as 编号,StudentName as 姓名, Sex as 性别,StudentResult as 分数 from dbo.Result,dbo.Student where dbo.Student.StudentNo=dbo.Result.StudentNo --内连接 方法2 select  dbo.Result.StudentNo as 编号,StudentName as 姓名, Sex as 性别,StudentResult as 分数 from dbo.Result inner join dbo.Student  on dbo.Student.StudentNo=dbo.Result.StudentNo  --上机练习1 1select GradeId AS 年级,AVG(ClassHour)as 总学时from dbo.Subjectgroup by GradeIdorder by AVG(ClassHour)-- 2select StudentNo as 学生,AVG(StudentResult) AS 平均分from dbo.Resultgroup by StudentNo --3select SubjectId as 课程,AVG(StudentResult) AS 平均分from dbo.Resultgroup by SubjectIdorder by AVG(StudentResult)desc--4select StudentNo as 学生,SUM(StudentResult) as 总分from dbo.Resultgroup by  StudentNoorder by SUM(StudentResult)desc--上机练习2 1select GradeId as 学期, SUM(ClassHour) as 学时 from dbo.Subjectgroup by  GradeIdhaving SUM(ClassHour)>50--2select GradeId as 学期,AVG(DATEDIFF(dd,BornDate,GETDATE())/365 )AS 平均年龄from dbo.Studentgroup by  GradeId--3SELECT count(StudentNo) AS 学生人数,GradeId as 学期from dbo.Studentwhere Address like '%北京%'group by GradeId--4select StudentNo as 编号,AVG(StudentResult)as 平均分 from dbo.Resultgroup by StudentNohaving AVG(StudentResult)>60--5select AVG(StudentResult)as 平均分,SubjectId as 课程from dbo.Resultwhere ExamDate = '2013-3-22'group by SubjectIdhaving AVG(StudentResult)>60order by AVG(StudentResult)desc--6select StudentNo as 学号,COUNT(StudentResult)as 次数from dbo.Resultwhere StudentResult<60group by StudentNo--上机练习3 1select x.StudentName as 学生姓名,c.GradeName as 所属年级名称,Phone as 联系电话from dbo.Student as x join dbo.Grade as con (x.GradeId=c.GradeId)--2select GradeName as 年级名称,SubjectName as 科目名称,ClassHour as 学时from dbo.Grade as n join dbo.Subject as k on (n.GradeId=k.GradeId)where SubjectId=1--3select StudentName AS 学生姓名,StudentResult as 分数,ExamDate as 考试日期from dbo.Result as f join dbo.Student as xon (f.StudentNo=x.StudentNo)where SubjectId=1--4select SubjectName as 科目名称,StudentResult as 分数,ExamDate as 考试日期from dbo.Result as r join dbo.Subject as xon (r.SubjectId=x.SubjectId)where r.StudentNo='s1101007'--5select StudentNo as 学号,SubjectName as 科目名称,StudentResult as 分数,ExamDate as 考试日期from dbo.Result as r join dbo.Subject as xon (r.SubjectId=x.SubjectId)--上机练习4 1select h.StudentNo as 学生学号,StudentName as 姓名,SubjectName as 科目名称,StudentResult as 成绩from dbo.Result as h join dbo.Student as mcon (h.StudentNo=mc.StudentNo)join dbo.Subject as k on mc.GradeId=k.GradeId --2select StudentName as 姓名,StudentResult as 成绩 ,ExamDate as 考试日期from dbo.Student as m join dbo.Result as c on m.StudentNo=c.StudentNojoin dbo.Subject j on m.GradeId=j.GradeIdwhere SubjectName='面向对象程序设计' --上机练习5 1select m.SubjectName as 科目名称,xf.StudentNo as 学号,xf.StudentResult as 分数from  dbo.Subject as m left join dbo.Result as xfon m.SubjectId=xf.SubjectId--2select m.SubjectName as 科目名称,xf.StudentNo as 学号,xf.StudentResult as 分数from  dbo.Subject as m left join dbo.Result as xfon m.SubjectId=xf.SubjectIdwhere StudentResult is null--3select GradeName,StudentNo,StudentName from dbo.Grade g left join dbo.Student s on s.GradeId=g.GradeId