第十一章 上机

 --上机1  --1  SELECT GradeId ,SUM (ClassHour) FROM [MySchool].[dbo].[Subject]  GROUP BY  GradeId  ORDER BY SUM (ClassHour)  --2  SELECT StudentNo,AVG(StudentResult)AS 平均分  FROM dbo.Result  GROUP BY StudentNo --3   SELECT SubjectId,AVG(StudentResult)AS 平均分  FROM dbo.Result  GROUP BY SubjectId  ORDER BY AVG(StudentResult) DESC  --4 SELECT StudentNo,SUM(StudentResult)AS 总分  FROM dbo.Result  GROUP BY StudentNo  ORDER BY SUM(StudentResult)  dbo.Result  --上机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,COUNT(*) AS 人数  FROM  dbo.Student  WHERE Address LIKE '%北京%'    GROUP BY GradeId    --4   SELECT StudentNo AS 学员,AVG(StudentResult)AS 平均分  FROM [MySchool].[dbo].[Result]  GROUP BY StudentNo   HAVING AVG(StudentResult)>60   ORDER BY  AVG(StudentResult)desc   --5   SELECT ExamDate AS 日期,  avg(StudentResult)AS 平均分  FROM [MySchool].[dbo].[Result]  WHERE ExamDate='2013-3-22'and StudentResult>60  GROUP BY ExamDate  --6  SELECT StudentNo AS 学号,COUNT(StudentResult)AS 不及格次数  FROM [MySchool].[dbo].[Result]  WHERE StudentResult<60  GROUP BY StudentNo      --上机3--1SELECT StudentName,s.GradeId,phone FROM Student s  inner join Grade g ON s.GradeId=g.GradeId   --2SELECT GradeName,SubjectName,ClassHour FROM Subject j  inner join Grade g ON j.GradeId=g.GradeId WHERE g.GradeId=1   --3SELECT StudentName,StudentResult,ExamDate FROM Student s  inner join Result r ON s.StudentNo=r.StudentNo  WHERE SubjectId=1    --4SELECT SubjectName,StudentResult,ExamDate FROM Result r  inner join Subject s ON s.SubjectId=r.SubjectIdWHERE StudentNo='S1101007'   -- 5SELECT StudentNo,SubjectName,StudentResult,ExamDate FROM Result r  inner join Subject s ON s.SubjectId=r.SubjectIdWHERE StudentResult<>0  --上机4--查询学生学号、姓名、考试科目名称及成绩。SELECT S.StudentNo AS 学号,S.StudentName AS 姓名,J.SubjectName AS 科目名称,R.StudentResult AS 成绩FROM Result AS RINNER JOIN Student AS S ON(R.StudentNo=S.StudentNo)INNER JOIN Subject AS J ON(R.SubjectId=J.SubjectId)SELECT S.StudentNo AS 学号,S.StudentName AS 姓名,J.SubjectName AS 科目名称,R.StudentResult AS 成绩FROM Result AS R,Student AS S,Subject AS JWHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)--查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。SELECT S.StudentName AS 姓名,R.StudentResult AS 成绩,R.ExamDate AS 考试日期FROM Result AS RINNER JOIN Student AS S ON (R.StudentNo=S.StudentNo)INNER JOIN Subject AS J ON(R.SubjectId=J.SubjectId)WHERE J.SubjectName='走进Java编程世界' SELECT S.StudentName AS 姓名,R.StudentResult AS 成绩,R.ExamDate AS 考试日期FROM Result AS R, Student AS S ,Subject AS JWHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)AND J.SubjectName='走进Java编程世界'