第十一章 上机

 --上机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

--1
SELECT StudentName,s.GradeId,phone 
FROM Student s  
inner join Grade g ON s.GradeId=g.GradeId  
 
--2
SELECT GradeName,SubjectName,ClassHour
 FROM Subject j  
inner join Grade g ON j.GradeId=g.GradeId 
WHERE g.GradeId=1  
 
--3
SELECT StudentName,StudentResult,ExamDate 
FROM Student s  
inner join Result r ON s.StudentNo=r.StudentNo 
 WHERE SubjectId=1   
 
--4
SELECT SubjectName,StudentResult,ExamDate 
FROM Result r  
inner join Subject s ON s.SubjectId=r.SubjectId
WHERE StudentNo='S1101007'  
 
-- 5
SELECT StudentNo,SubjectName,StudentResult,ExamDate 
FROM Result r  
inner join Subject s ON s.SubjectId=r.SubjectId
WHERE StudentResult<>0  

--上机4


--查询学生学号、姓名、考试科目名称及成绩。
SELECT S.StudentNo AS 学号,S.StudentName AS 姓名,J.SubjectName AS 科目名称,R.StudentResult AS 成绩
FROM Result AS R
INNER 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 J
WHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)

--查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。
SELECT S.StudentName AS 姓名,R.StudentResult AS 成绩,R.ExamDate AS 考试日期
FROM Result AS R
INNER 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 J
WHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)AND J.SubjectName='走进Java编程世界'