C# , SQL 数据库 -------------- 连接查询和分组查询 ---- (上机练习)

--/****** Script for SelectTopNRows command from SSMS  ******/--SELECT TOP 1000 [StudentNo] count (*)--      ,[LoginPwd]--      ,[StudentName]--      ,[Sex]--      ,[GradeId]--      ,[Phone]--      ,[Address]--      ,[BornDate]--      ,[Email]--  FROM [MySchool].[dbo].[Student][Result]      --SELECT Sex ,count (Phone) AS 电话  --FROM dbo.Student   --group by Sex    SELECT SubjectId ,AVG(StudentResult) AS 科目平均分   from dbo.Result  group by SubjectId  order by AVG(StudentResult) desc     SELECT SubjectId , AVG(StudentResult) AS 科目平均分   from dbo.Result  group by SubjectId  HAVING AVG(StudentResult)>70  order by AVG(StudentResult) desc      SELECT SubjectId ,StudentResult, AVG(StudentResult) AS 科目平均分 ,MAX(StudentResult) as 最高分  from dbo.Result  where (StudentResult)>90  group by SubjectId,StudentResult  HAVING AVG(StudentResult)>70  order by AVG(StudentResult) desc        SELECT dbo.Student.GradeId,dbo.Subject.GradeId  from Student,Subject  WHERE Student.GradeId=dbo.Subject.GradeId            ------------------------------------------------------------------------   11章 (连接查询和分组查询),    上机1, 1  SELECT GradeId,SUM(ClassHour) as 总学时数  from [Subject]  group by  GradeId   order by sum(ClassHour)     -----------------------------------------------------   SELECT StudentNo, AVG(StudentResult) as 平均分  from dbo.Result  group by  StudentNo    ---------------------------------------------------    SELECT SubjectId, AVG(StudentResult) as 平均分  from dbo.Result  group by  SubjectIdorder by  AVG(StudentResult) desc---------------------------------------      SELECT StudentNo as 每个学生, sum(StudentResult) as 考试总分  from dbo.Result  group by  StudentNo  order by  sum(StudentResult) desc     -------------------------------------------------------------------------   上机2 , 1    SELECT GradeId , sum(ClassHour) AS 学时,COUNT(SubjectId)as 课程数  from dbo.Subject  group by  GradeId  HAVING sum(ClassHour)>50----------------------------------  SELECT GradeId as 年级,avg( datediff(dd,BornDate,GETDATE())/365 )AS 平均年龄  from dbo.Student  group by  GradeId  ------------------------------------    SELECT GradeId as 学期,count(StudentNo)AS 人数  from dbo.Student  where [Address] like '%北京%'  group by  GradeId  -----------------------------------    SELECT StudentNo as 学生, StudentResult as 成绩 ,count (StudentResult) as 平均分及格  from dbo.Result   group by StudentNo,StudentResult   having AVG (StudentResult)>=60  order by  count (StudentResult) desc  ---------------------------------------        SELECT SubjectId as 课程,AVG (StudentResult)as 课程平均分  from dbo.Result   where ExamDate='2013/3/22'   group by SubjectId  having AVG (StudentResult)>=60  order by  count (StudentResult) desc  ----------------------------------------------select StudentNo as 学生学号,COUNT (StudentResult) as 不及格次数from dbo.Resultwhere StudentResult<60group by StudentNo---------------------------------------- -------------------------------------   上机 3 ,1 select  StudentName,GradeName,Phone from dbo.Student,dbo.Gradewhere Student.GradeId=Grade.GradeId------------------------------------------  select SubjectName,GradeName,ClassHourfrom Subject,Gradewhere Subject.GradeId=Grade.GradeId and Grade.GradeId=1----------------------------------------select StudentName,StudentResult,ExamDatefrom dbo.Student,dbo.Resultwhere Student.StudentNo=Result.StudentNo and SubjectId=1--------------------------------------------select SubjectName,StudentResult,ExamDatefrom dbo.Result,dbo.Subjectwhere Subject.SubjectId=dbo.Result.SubjectId and StudentNo='S1101007'---------------------------------------------select StudentNo,SubjectName,StudentResult,ExamDatefrom dbo.Result,Subjectwhere Result.SubjectId=Subject.SubjectId------------------------------------------------------------------------------  上机 4 ,1 select Student.StudentNo,StudentName,SubjectName,StudentResultfrom Student,dbo.Subject,dbo.Resultwhere Student.StudentNo=Result.StudentNo and Subject.SubjectId=Result.SubjectId------------------------------------------  select * from Resultselect StudentName,StudentResult,ExamDatefrom Student,Result,Subjectwhere Student.StudentNo=Result.StudentNo and Subject.SubjectId=Result.SubjectId and Subject.SubjectName='走进Java编程世界'--------------------------------------------------------------------------  上机 5 ,1 select * from Subjectselect SubjectName,StudentNo,StudentResultfrom Subjectleft join Result on Subject.SubjectId=Result.SubjectId